4

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?

  • Can you give an indication of what "large" is for polygons that aren't satisfactorily accurate? – fortboise Sep 2 '16 at 16:12
4

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.

1

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.

  • Thats true for a point-in-polygon search where the above would return false positives (+1 for that) but it has nothing to do with curvature as its looking at the min/max lat long and the same true for any planar co-ordinate system. For displaying polygons on a map the results arent wrong, may include in corner cases polygons not visible but whose min max are within the search but doesn't display any false data and is far faster on our data than the spatial search and answers the question. – Matt Mar 10 '16 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.