Gridding a sdo_geometry line/polygon object (Oracle)
Keywords grid gridding raster vector chopping intersection boundary clip polygon lineSummary A vector-to-raster algorithm generating tiles covering (multi-)line/polygon geometries. Boundary tiles are clipped forming non-square tiles.
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 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.
SpatialDB Advisor
Home
Comment