Oracle 18c:
Using an SQL query, I want to generate square grid graph features:
The underlying vertices would look like this:
STARTPOINT_X STARTPOINT_Y ENDPOINT_X ENDPOINT_Y SHAPE ------------ ------------ ---------- ---------- ---------- 0 0 1 0 [SHAPE] --horizontal lines 1 0 2 0 [SHAPE] 2 0 3 0 [SHAPE] 3 0 4 0 [SHAPE] 4 0 5 0 [SHAPE] 5 0 6 0 [SHAPE] ... 0 0 0 1 [SHAPE] --vertical lines 0 1 0 2 [SHAPE] 0 2 0 3 [SHAPE] 0 3 0 4 [SHAPE] 0 4 0 5 [SHAPE] 0 5 0 6 [SHAPE] ... [220 rows selected]
Details:
The reason I want to generate this data is:
I want sample line data to work with when testing Oracle Spatial queries. Sometimes I need a few hundred lines. Other times, I need thousands of lines. And I need to share the data online via https://dbfiddle.uk, so I can't use real/proprietary GIS data.
Also, if the lines are in a grid pattern, then it will be obvious if any lines are missing in my analysis results (by looking for gaps in the data in a map).
I'm aware that there are ways to do this with ArcGIS tools. In this case, I want to do it using SQL.
How can I generate those grid lines using Oracle SQL?
Here's one way of doing it:
--create or replace view sdo_geom_grid_vw as ( --select * from (
with dimension as ( select 0 as point from dual union all select level from dual connect by level <= 10 ), points as ( select a.point as startpoint, b.point as endpoint, c.point as fixed from dimension a cross join dimension b cross join dimension c where b.point - a.point = 1 ) select cast(rownum as number(38,0)) as objectid, startpoint_x, startpoint_y, endpoint_x, endpoint_y, sdo_geometry('linestring('||startpoint_x||' '||startpoint_y||', '||endpoint_x||' '||endpoint_y||')',26917) as shape from ( select startpoint as startpoint_x, fixed as startpoint_y, endpoint as endpoint_x, fixed as endpoint_y from points union all select fixed as startpoint_x, startpoint as startpoint_y, fixed as endpoint_x, endpoint as endpoint_y from points ) order by startpoint_x, startpoint_y, endpoint_x, endpoint_y
--))
OBJECTID STARTPOINT_X STARTPOINT_Y ENDPOINT_X ENDPOINT_Y SHAPE ---------- ------------ ------------ ---------- ---------- -------------- 219 0 0 0 1 [SDO_GEOMETRY] 109 0 0 1 0 [SDO_GEOMETRY] 208 0 1 0 2 [SDO_GEOMETRY] 108 0 1 1 1 [SDO_GEOMETRY] 120 0 2 0 3 [SDO_GEOMETRY] 99 0 2 1 2 [SDO_GEOMETRY]
Notes: