2

I'm using Microsoft SQL Server to store simple data for administrative boundaries of the world (lots of polygons). I store this in a geography data type column. Afterwards I query these polygons by a bounding box and draw these polygons on the computer screen, using WebMercator projection.

But now I've noticed a problem. When I make a rectangle polygon in SQL server, it has curved edges. For example, the following query produces the following results (screenshot from SQL Server Management Studio):

select geography::Parse('POLYGON((10 50, 50 50, 50 60, 10 60, 10 50))')
union all
select geography::Parse('POLYGON((30 51, 24 48, 36 48, 30 51))')

enter image description here

Even more importantly - these two shapes are NOT considered to be overlapping by the STIntersects() function, even though they would intersect if the borders were straight.

In a sense this is actually correct. These are the shortest lines on a globe which connect the given points.

But this presents me with a problem - my viewport is a rectangle in WebMercator projection. I need to select all shapes which intersect with my viewport, and for that I need to get a truly rectangular rectangle to pass to the STIntersects() function.

And of course, I now realize that I'm probably drawing these polygons wrongly as well... or am I? I'm using the data from Natural Earth, although I'd like to switch to OpenStreetMap, since it has more useful data. But should the polygon data I acquire from these sources be drawn with the "curves enabled" or not?

I don't even know what this phenomenon is called, so I don't know what to Google for.

What is the correct manner of doing this?

  • Possibly use GEOMETRY type rather than GEOGRAPHY type or use a lot more vertices to have the geodesic lines follow the latitude/x lines more closely. – mkennedy Mar 14 '16 at 18:20
  • @mkennedy - I've considered using geometry, but that then gets problematic when my viewport is over the 180/-180 line. – Vilx- Mar 14 '16 at 22:29

Your Answer

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

Browse other questions tagged or ask your own question.