Gridding a geometry object (PostGIS)

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 PostGIS. Single Geometry The followingRead More

Gridding a geometry or geography object (SQL Server Denali)

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 SQL Server 2008 Denali. DenaliRead More

Finding centre and radius of a circular geometry

Here is a method for finding the centre of a circle and its radius from a polygon (x003) sdo_geometry. Some examples: The SDO_UTIL.CIRCLE_POLYGON function is trickier because it generates 8307 longitude/latitude data. So the above function, based as it is on projected data, can only give us a basic approximation. The returned radius is goingRead More

Constraining geometry type for sdo_geometry column in a table.

Other blog postings of mine have shown how the layer_gtype keyword in the PARAMETERS clause of an RTree spatial index can be used to constrain the sdo_geometry type being stored in a column. Remember, SDO_GEOMETRY is singly inherited: there is no POINT, CURVE, POLYGON etc sub-type. For this one must use the SQL/MM Type shippedRead More

The Power of Constraints and Indexes for Spatial Constraints: stopping duplicate points

Over on Technet , the following question was asked: My question is, can you think of a way to recognize situations like this and merge and store them in a table before, we attempt mapping and spatial queries are performed? The following database level approach is one approach for ensuring any loader doesn’t load duplicateRead More

CENTROID package use with ESRI’s sde.st_geometry

I have been emailed by someone who is using ESRI’s sde.st_geometry in preference to Oracle’s SDO_GEOMETRY or ST_GEOMETRY for their spatial data storage in Oracle, wanting a version of my CENTROID pl/sql package that would allow for its use with ESRI’s spatial data type. This is what I was asked: First off love your blog,Read More

CENTROID Package now returns centroid of multi-linestring

My original CENTROID package’s sdo_centroid() functions, when supplied with a multilinestring sdo_geometry, would “explode” the multilinestring into its component linestrings and calculate the centroid (mid-point) of the largest linestring. Some users have asked for the centroid package to return the centroid (midpoint) of the whole multilinestring. While the LINEAR package can do this, I haveRead More