Gridding a sdo_geometry line/polygon object (Oracle)

A common question that comes up in the various database forums (PostGIS, SQL Server, Oracle) is how to “grid” a linear or polygonal object.

By “grid” one means work out the square pixels (rectangular polygons) that cover or define a vector geometry.

Here is some SQL for doing this for Oracle Locator.

Single Geometry

Here is a method applied to a single geometry. The function Morton and REGULARGRIDXY are provided in other blog articles or at the end of this article. Note that REGULARGRIDXY is used in order to ensure that the gridding takes place according to a commonly defined MBR.

``` WITH geomQuery AS (
SELECT sdo_geom.sdo_min_mbr_ordinate(g.geom,1) AS minx,
round(sdo_geom.sdo_max_mbr_ordinate(g.geom,1),2) AS maxx,
sdo_geom.sdo_min_mbr_ordinate(g.geom,2) AS miny,
round(sdo_geom.sdo_max_mbr_ordinate(g.geom,2),2) AS maxy,
g.geom, 0.050 AS gridX, 0.050 AS gridY, 0 AS loCol, 0 AS loRow
FROM (SELECT sdo_geom.sdo_xor(sdo_geom.sdo_buffer(a.geom,1.000,0.0005),
sdo_geom.sdo_buffer(a.geom,0.500,0.0005),0.0005) AS geom
FROM (SELECT sdo_geometry('MULTIPOINT((09.25 10.00),(10.75 10.00),(10.00 10.75),(10.00 9.25))',
NULL) AS geom
FROM DUAL) a
) g
)
SELECT f.mkey,f.gcol,f.grow,f.geom
FROM (SELECT TESSELATE.Morton(b.gcol - a.loCol, b.grow - a.loRow) AS mKey,
b.gcol,b.grow,
CASE WHEN a.geom.get_gtype() IN (3,7)
THEN sdo_geom.sdo_Intersection(a.geom,b.geometry,0.005)
ELSE a.geom
END AS geom
FROM geomQuery a,
TABLE(TESSELATE.RegularGridXY(a.minx,a.miny,a.maxx,a.maxy,a.gridX,a.gridY,a.geom.sdo_srid)) b
WHERE sdo_geom.relate(a.geom,'ANYINTERACT',b.geom,0.0005) = 'TRUE'
) f
WHERE f.geom.get_gtype() IN (3,7) /* Don't want point or line tiles */
ORDER BY f.mKey;
```

The result of this looks like.

Multiple Geometries

The following grids multiple geometries into a single logical grid.

``` -- Multiple features
--
WITH geomQuery AS (
SELECT g.rid,
MIN(sdo_geom.sdo_min_mbr_ordinate(g.geom,1))          OVER (partition BY g.pid) AS minx,
MAX(round(sdo_geom.sdo_max_mbr_ordinate(g.geom,1),2)) OVER (partition BY g.pid) AS maxx,
MIN(sdo_geom.sdo_min_mbr_ordinate(g.geom,2))          OVER (partition BY g.pid) AS miny,
MAX(round(sdo_geom.sdo_max_mbr_ordinate(g.geom,2),2)) OVER (partition BY g.pid) AS maxy,
CODESYS.GEOM.ROUNDORDINATES(g.geom,3) AS geom,
0.050 AS gridX, 0.050 AS gridY, 0 AS loCol, 0 AS loRow, g.tol
FROM (SELECT 1 AS pid, a.rid, t.tol,
sdo_geom.sdo_xor(sdo_geom.sdo_buffer(a.geom,1.000,t.tol),
sdo_geom.sdo_buffer(a.geom,0.750,t.tol),t.tol) AS geom
FROM (SELECT 0.00005 AS tol FROM DUAL) t,
(SELECT 1 AS rid, sdo_geometry('POINT(09.50 10.00)',NULL) AS geom FROM DUAL
UNION ALL SELECT 2 AS rid, sdo_geometry('POINT(10.50 10.00)',NULL) AS geom FROM DUAL
UNION ALL SELECT 3 AS rid, sdo_geometry('POINT(10.00 10.50)',NULL) AS geom FROM DUAL
UNION ALL SELECT 4 AS rid, sdo_geometry('POINT(10.00 09.50)',NULL) AS geom FROM DUAL) a ) g
)
SELECT ROW_NUMBER() OVER (ORDER BY f.gcol,f.grow) AS tid,
TESSELATE.Morton(f.gcol - f.loCol, f.grow - f.loRow) AS mKey,
f.gcol, f.grow,
COUNT(*) AS UnionedTileCount,
sdo_aggr_union(sdoaggrtype(f.geom,f.tol)) AS geom
FROM (SELECT b.gcol, b.grow, a.loCol, a.loRow, a.tol,
CASE WHEN a.geom.get_gtype() IN (3,7)
THEN sdo_geom.sdo_Intersection(a.geom,b.geometry,0.005)
ELSE a.geom
END AS geom
FROM geomQuery a,
TABLE(TESSELATE.RegularGridXY(a.minx,a.miny,a.maxx,a.maxy,a.gridX,a.gridY,a.geom.sdo_srid)) b
WHERE sdo_geom.relate(a.geom,'ANYINTERACT',b.geom,a.tol) = 'TRUE' ) f
WHERE f.geom IS NOT NULL
AND f.geom.get_gtype() IN (3,7) /* Don't want point or line tiles */
GROUP BY f.gcol,f.grow,f.loCol,f.loRow,f.tol
ORDER BY 2;
```

That looks like this.

I hope this is of use to Oracle users.