Wednesday 30 January 2008

Geoquery 2008 beta - polygons on sphere NOT!

The releast notes for the latest Geoquery beta release should state in BIG BOLD LETTERS that the POLYGON GEOGRAPHY is not correctly rendered. I knew this but rushed the release out regardless - but this MSDN Forums post reminded me how confusing this can be so I wanted to be sure people use LINESTRING for now.

To illustrate, the poster wants to know why these polygons don't BOTH STIntersect 'Vancouver'...
DECLARE @myPoint geography, @polySmall geography, @polyBIG geography
SET @myPoint = geography::Parse('POINT(49.274138 -123.098562)')
SET @polySmall = geography::Parse('POLYGON((47.0 -124.0, 47.0 -122.0, 50.0 -122.0, 50.0 -124.0, 47.0 -124.0))')
SET @polyBIG = geography::Parse('POLYGON((47.0 -155.0, 47.0 -85.0, 50.0 -85.0, 50.0 -155.0, 47.0 -155.0))')
SELECT @polySmall.STIntersects(@myPoint) AS Intersect_polySmall, @polyBIG.STIntersects(@myPoint) AS Intersect_polyBIG
SELECT @polyBIG , 'Red' as Color, '#44ff0000' as fill
SELECT @polySmall, 'Green' as Color, '#4400ff00' as fill
SELECT @myPoint, 'Blue' as Color, 2 as Thickness
because he's visualizing the shapes looking like this:

This is WRONG WRONG WRONG because Geoquery is not correctly rendering POLYGONs on the curvature of the Earth's surface... so you THINK they should intersect, but STIntersects() correctly returns false.

If we use LINESTRINGs to do the drawing (which Geoquery 2008 v0.7 DOES support) then it's rendered correctly and you can see that they don't overlap:
/* However converting to a LINESTRING which is rendered correctly, it's clear they don't intersect*/
DECLARE @myPoint geography, @polySmall geography, @polyBIG geography
SET @myPoint = geography::Parse('POINT(49.274138 -123.098562)')
SET @polySmall = geography::Parse('LINESTRING(47.0 -124.0, 47.0 -122.0, 50.0 -122.0, 50.0 -124.0, 47.0 -124.0)')
SET @polyBIG = geography::Parse('LINESTRING(47.0 -155.0, 47.0 -85.0, 50.0 -85.0, 50.0 -155.0, 47.0 -155.0)')
SELECT @polySmall, 'Green' as Color, '#4400ff00' as fill
SELECT @polyBIG, 'Red' as Color, '#44ff0000' as fill
SELECT @myPoint, 'Blue' as Color, 2 as Thickness


Even more interesting - the lines that should be parallel ARE, and the lines that shouldn't be (longitude) AREN'T, when drawn on a globe (as they should be).


p.s. if you're wondering why LINESTRING works and POLYGON doesn't; it was (fairly) trivial to handle lines that 'break' over the +/-180 longitude (~international date line), but less so to 'split' POLYGONs into parts to draw independently ... so I left it out (for now). Sorry 'bout that.

UPDATE 31-Jan: this has been fixed and Geoquery 2008 v0.71 is available for download

2 comments:

  1. Updated SQL for Sql Server 2008 RTM (with Lat/Long reversed (use this in place of the example query above).

    DECLARE @myPoint geography, @polySmall geography, @polyBIG geography

    SET @myPoint = geography::Parse('POINT( -123.098562 49.274138)')

    SET @polySmall = geography::Parse('POLYGON((-124.0 47.0,-122.0 47.0, -122.0 50.0, -124.0 50.0, -124.0 47.0))')

    SET @polyBIG = geography::Parse('POLYGON((-155.0 47.0 , -85.0 47.0 , -85.0 50.0, -155.0 50.0,-155.0 47.0 ))')

    SELECT @polySmall.STIntersects(@myPoint) AS Intersect_polySmall
    , @polyBIG.STIntersects(@myPoint) AS Intersect_polyBIG

    SELECT @polyBIG , 'Red' as Color, '#44ff0000' as fill

    SELECT @polySmall, 'Green' as Color, '#4400ff00' as fill

    SELECT @myPoint, 'Blue' as Color, 2 as Thickness

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete

Note: only a member of this blog may post a comment.