Oracle 18c:

Using an SQL query, I want to generate a list of coordinates that make up the line segments of a

STARTPOINT_X STARTPOINT_Y ENDPOINT_X ENDPOINT_Y
------------ ------------ ---------- ----------
           0            0          1          0 --horizontal lines
           1            0          2          0
           2            0          3          0
           3            0          4          0
           4            0          5          0
           5            0          6          0

         ...

           0            0          0          1 --vertical lines
           0            1          0          2
           0            2          0          3
           0            3          0          4
           0            4          0          5
           0            5          0          6

         ...

[220 rows selected]

Details:

1. The lines would be split at each intersection. So, in the image above, there are 220 lines. Each line is composed of two vertices.
2. 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.).
3. To keep things simple, we can assume the grid's overall shape will always be a square (length = width). And we can make the cell size 1 unit.
4. I've supplied sample data in this db<>fiddle. 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.

Also, if the lines are in a grid, then it will be obvious if any lines are missing in my results (by looking at the data in mapping software and spotting gaps).

How can I generate those grid line coordinates using SQL?

CodePudding user response：

There's a few ways to generate rows in Oracle. Note: This particular (recursive) way might not be optimal for very large grids, for that you might want to cross join 2 rows a bunch of times, however, this way is more amenable to injecting a variable for your dimension.

Selecting from the magic dual table usually returns 1 row but you can use the recursive connect by with the magic level value to determine how many rows you want. It doesn't return a 0-level so I hard-coded that in.

Looking at your square, its a mirror image made up of single unit vectors; all the horizontal vectors are repeated vertically, so only half have to be generated. Note the union all in the final query just returns the same data but swaps the x and y points.

It cross joins dimension CTE 3 times. The first 2 are to get the start & end and only a 3rd because for all the e.g. horizontal vectors we just want the vertical coordinates to be the same for both start and end. It filters out where start & end are equal as those are zero-length vectors which are not needed as well as those longer than length 1 using where b.point - a.point = 1 .

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
  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_y, endpoint_y, startpoint_x, endpoint_x

The place where you would inject the variable is on line 6, replacing that 10 with whatever grid size you want connect by level <= 10.

In a SQL*Plus script you could do that like

define dimension = 10;
with ...[ rest of the query blah blah ] 
connect by level <= &dimension

CodePudding user response：

You can use:

WITH range (v) AS (
  SELECT LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 11
)
SELECT x.v AS startpoint_x,
       y.v AS startpoint_y,
       x.v   1 AS endpoint_x,
       y.v AS endpoint_y
FROM   range x CROSS JOIN range y
WHERE  x.v <= 9
UNION ALL
SELECT x.v AS startpoint_x,
       y.v AS startpoint_y,
       x.v AS endpoint_x,
       y.v   1 AS endpoint_y
FROM   range x CROSS JOIN range y
WHERE  y.v <= 9

or, more generally:

WITH range (v) AS (
  SELECT LEVEL - 1 FROM DUAL CONNECT BY LEVEL - 1 <= GREATEST(:max_x, :max_y)
)
SELECT x.v AS startpoint_x,
       y.v AS startpoint_y,
       x.v   1 AS endpoint_x,
       y.v AS endpoint_y
FROM   range x CROSS JOIN range y
WHERE  x.v <  :max_x
AND    y.v <= :max_y
UNION ALL
SELECT x.v AS startpoint_x,
       y.v AS startpoint_y,
       x.v AS endpoint_x,
       y.v   1 AS endpoint_y
FROM   range x CROSS JOIN range y
WHERE  x.v <= :max_x
AND    y.v <  :max_y

db<>fiddle here