Accurately find the bounding box of geography polygons

Question

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?

Answer 1

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.

Answer 2

That WHERE clause doesn't actually work correctly for the general geography case. Since the earth is curved, you can have a point that has a lat/long inside those limits and yet is outside the polygon. For example POLYGON((0 30, 100 30, 100 40, 0 40, 0 30)) and POINT(50 35). Just by the numbers it looks like it should be dead center, but on an ellipsoid the point falls outside the polygon by a wide margin. Try it and see.

There are some valid uses for fast-but-wrong, but be aware of what you're getting.