Generate grid line features using SQL

Bud — Fri, 17 Jun 2022 02:08:16 GMT

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 lines would be split at each intersection. So, in the image above, there are 220 lines. Each line is composed of two vertices.
Ideally, I would have the option of specifying in the query what the overall grid dimensions would be. For example, specify this somewhere in the SQL: DIMENSIONS = 10 x 10 (or DIMENSIONS = 100 x 100, etc.).
To keep things simple, we can:
- Assume the grid's overall shape will always be a square (length = width).
- For the purpose of this question, we can make the cell size 1 unit.
- And the grid can start at 0,0 and be built "up and right" (to the northeast).
I've supplied sample data in this db<>fiddle (non-spatial XY coordinates). I created that data using Excel.
- Hint: The vertical grid lines start at row 111.

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?

Re: Generate grid line features using SQL

Bud — Fri, 17 Jun 2022 02:05:25 GMT

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:

Source: https://stackoverflow.com/questions/72620062/generate-grid-line-coordinates-using-sql
Related: Oracle Spatial Community - Generate grid line coordinates
The query currently produces SDO_GEOMETRY shapes. But it can very easily be adapted to generate SDE.ST_GEOMETRY shapes — since it converts the coordinates to shapes via WKT (supported by both geometry types). Just change sdo_geometry( —to— sde.st_geometry( in the outer query.
When I added the query as a view to ArcMap, selecting the features was very buggy. For example, if I selected a few rows of cells at the top of the grid, ArcMap would also select a few random rows/cells at the bottom of the grid too, which was incorrect. That seems to be a problem in general with spatial views in ArcGIS.
I was able to solve that problem by registering the view with the GDB. I had the same problem with both SDO_GEOMETRY and SDE.ST_GEOMETRY views. Registering the view solved it for both datatypes.

topic Generate grid line features using SQL in ArcGIS Enterprise Questions

Generate grid line features using SQL

Re: Generate grid line features using SQL