[GIS] Accurately find the bounding box of geography polygons

extentspolygonsql serversql-server-spatial

I'm displaying polygons on a map and have had issues with the spatial index where the index performs well only for certain data sets (large areas with low density / high density small map areas).

I've added bounding box columns (min/max, x/y) to the table with spatial data and using an index on these fields improves the speed of queries for the map by 100%.

However for large polygons the bounding box I've calculated is quite inaccurate – I'm using:

alter table contactgeography 
add
CGMinLong AS (CONVERT(float, ContactPolygon.EnvelopeCenter().Long - ContactPolygon.EnvelopeAngle())) PERSISTED,
CGMinLat AS (CONVERT(float,ContactPolygon.EnvelopeCenter().Lat - ContactPolygon.EnvelopeAngle())) PERSISTED,  
CGMaxLong AS (CONVERT(float,ContactPolygon.EnvelopeCenter().Long + ContactPolygon.EnvelopeAngle())) PERSISTED, 
CGMaxLat AS (CONVERT(float,ContactPolygon.EnvelopeCenter().Lat + ContactPolygon.EnvelopeAngle())) PERSISTED

to add the columns. This will often return an area smaller than the original polygon (even the MS documentation says its not perfect).

I'd like to retrieve the points of the polygon in SQL and loop through them to find the min/max lat/lon but can't see a way to do this in a computed column.

Can the 

ContactPolygon.EnvelopeCenter().Lat - ContactPolygon.EnvelopeAngle()

be improved to return more accurate results?

Is there an easy way to return the min/max points from the polygon itself?

Best Answer

There's no quick and easy way but for the benefit of someone else with the same problem the solution is to convert the geography type to a geometry type, take the bounding box (envelope) of the geometry and pull out the 1st (min) and 3rd (max) points. 

CGMinLong AS (CONVERT(float, geography::STGeomFromWKB( geometry::STGeomFromWKB(contactpolygon.STAsBinary(), contactpolygon.STSrid).STEnvelope().STAsBinary(), contactPolygon.STSrid).MakeValid().STPointN(1).Long)) PERSISTED,
CGMinLat AS (CONVERT(float,geography::STGeomFromWKB( geometry::STGeomFromWKB(contactpolygon.STAsBinary(), contactpolygon.STSrid).STEnvelope().STAsBinary(), contactPolygon.STSrid).MakeValid().STPointN(1).Lat)) PERSISTED,  
CGMaxLong AS (CONVERT(float,geography::STGeomFromWKB( geometry::STGeomFromWKB(contactpolygon.STAsBinary(), contactpolygon.STSrid).STEnvelope().STAsBinary(), contactPolygon.STSrid).MakeValid().STPointN(3).Long)) PERSISTED, 
CGMaxLat AS (CONVERT(float,geography::STGeomFromWKB( geometry::STGeomFromWKB(contactpolygon.STAsBinary(), contactpolygon.STSrid).STEnvelope().STAsBinary(), contactPolygon.STSrid).MakeValid().STPointN(3).Lat)) PERSISTED

Even with the conversions between types and WKB it still performs pretty well and if you use these min and max values instead of the spatial index (for retrieving shapes within a rectangle for display on the screen) you might find, like i have, that it performs more consistently and normally better than a spatial index search.

The search ends up looking like:

WHERE not(CGMaxLong < @minLon OR CGMinLong > @maxLon OR CGMaxLat < @minLat OR CGMinLat > @maxLat

where the @ parameters are your search area.

Related Question