locked
Locating a Point within a Geography RRS feed

  • Question

  • I used the utility at http://mikeo.co.uk/demo/sqlspatial/default.aspx to create an outline of the region Wales.  The result was

    INSERT into RegionGeographies (RegionID, RegionGeography) VALUES (236, geography::STGeomFromText('POLYGON((-3.3398437500000093 53.39643212709597, -4.735107421875012 53.47823926148309, -4.910888671875009 52.739617554270886, -5.284423828125015 52.02545860348813, -5.531616210937488 51.64188525876832, -4.7900390625000035 51.40605940499275, -3.3398437500000093 51.31344707827586, -2.6641845703125066 51.56341232867588, -2.636718749999995 51.82219818336938, -2.8674316406250137 51.920555645156945, -2.9498291015625155 51.89344403698397, -3.136596679687502 52.10650519075632, -2.966308593750002 52.335339071889386, -3.2244873046875 52.41582261237878, -2.98278808593749 52.519563529257425, -3.0047607421875057 52.562995039558004, -3.1091308593749902 52.536273041459474, -3.1420898437499982 52.57634993749886, -2.9937744140625146 52.739617554270886, -3.158569335937485 52.77950854257623, -3.125610351562511 52.89896238858775, -2.9718017578124986 52.94863788488319, -2.8509521484374933 52.938707345098635, -2.785034179687512 52.88902044899523, -2.7246093749999924 52.928774525801366, -2.7301025390624884 52.9850303652323, -2.99926757812501 53.14347558459451, -2.911376953125013 53.173119202640635, -3.2904052734375146 53.37349790058685, -3.3398437500000093 53.39643212709597))', 4326))

    My table consists of three columns, an identity column, RegionID and RegionGeography. The RegionID for Wales happens to be 236. 

    The Lat/Long for the Cardiff-Wales airport is 51 24N 003 21W.  If I convert this to Long/Lat I get -003.21 51.24. When I query my table like this

    DECLARE @WeatherStation geography = geography::STPointFromText('POINT(-003.21 51.24)', 4326);
    SELECT RG.RegionID FROM RegionGeographies RG
    WHERE RG.RegionGeography.STIntersects(@WeatherStation) = 1;   Result: NULL

    Shouldn't this point fall within my region?  Is there any convenient tool / technique to verify this?

    Other test points appear to return the correct result (236).

    Friday, August 14, 2009 4:09 PM

Answers

  • The "most convenient tool / technique" to verify what's going on is simply to select both geometries in a query in SQL Server Management Studio and then click on the 'Spatial Results' tab. Sometimes point geometries are a little but hard to see, so you can add an STBuffer() to them to make them a bit bigger. Doing so in this case produces the following:
    DECLARE @Wales geography = geography::STGeomFromText('POLYGON((-3.3398437500000093 53.39643212709597, -4.735107421875012 53.47823926148309, -4.910888671875009 52.739617554270886, -5.284423828125015 52.02545860348813, -5.531616210937488 51.64188525876832, -4.7900390625000035 51.40605940499275, -3.3398437500000093 51.31344707827586, -2.6641845703125066 51.56341232867588, -2.636718749999995 51.82219818336938, -2.8674316406250137 51.920555645156945, -2.9498291015625155 51.89344403698397, -3.136596679687502 52.10650519075632, -2.966308593750002 52.335339071889386, -3.2244873046875 52.41582261237878, -2.98278808593749 52.519563529257425, -3.0047607421875057 52.562995039558004, -3.1091308593749902 52.536273041459474, -3.1420898437499982 52.57634993749886, -2.9937744140625146 52.739617554270886, -3.158569335937485 52.77950854257623, -3.125610351562511 52.89896238858775, -2.9718017578124986 52.94863788488319, -2.8509521484374933 52.938707345098635, -2.785034179687512 52.88902044899523, -2.7246093749999924 52.928774525801366, -2.7301025390624884 52.9850303652323, -2.99926757812501 53.14347558459451, -2.911376953125013 53.173119202640635, -3.2904052734375146 53.37349790058685, -3.3398437500000093 53.39643212709597))', 4326);
    DECLARE @Cardiff geography = geography::STPointFromText('POINT(-3.21 51.24)', 4326);
    
    SELECT @Wales, 'Wales' UNION ALL SELECT @Cardiff.STBuffer(2000), 'Cardiff'

     alt=

    The results confirm why you're getting a NULL result - Cardiff does not lie within Wales, at least, not using the geometries you've defined!
    I would suggest that your coordinates for Cardiff are incorrect. The site at http://www.convertunits.com/distance/airport/CWL suggests a latitude of 51.397 and longitude of -3.346, which does lie within the polygon defined.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, August 14, 2009 4:27 PM
    Answerer
  • Ah - I didn't notice you had stated the coordinate in DMS format. Oh well, at least you know now!

    If you want free spatial data:
    For UN data: http://geodata.grid.unep.ch/
    For US states: http://www.census.gov/

    Note that these sites, as well as most common other sources of spatial data, use the ESRI shapefile format. You'll therefore need some way of importing shapefiles into SQL Server. The most popular free tool is Morten Nielsen's Shape2SQL tool, available here: http://www.sharpgis.net/page/Shape2SQL.aspx

    Also, as with any other sort of data, it is true that you get what you pay for. You may well find that the spatial data from the above sources may need cleaning up in places, but it will almost certainly provide greater accuracy than tracing a shape in Virtual Earth... :)

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, August 19, 2009 6:45 AM
    Answerer

All replies

  • The "most convenient tool / technique" to verify what's going on is simply to select both geometries in a query in SQL Server Management Studio and then click on the 'Spatial Results' tab. Sometimes point geometries are a little but hard to see, so you can add an STBuffer() to them to make them a bit bigger. Doing so in this case produces the following:
    DECLARE @Wales geography = geography::STGeomFromText('POLYGON((-3.3398437500000093 53.39643212709597, -4.735107421875012 53.47823926148309, -4.910888671875009 52.739617554270886, -5.284423828125015 52.02545860348813, -5.531616210937488 51.64188525876832, -4.7900390625000035 51.40605940499275, -3.3398437500000093 51.31344707827586, -2.6641845703125066 51.56341232867588, -2.636718749999995 51.82219818336938, -2.8674316406250137 51.920555645156945, -2.9498291015625155 51.89344403698397, -3.136596679687502 52.10650519075632, -2.966308593750002 52.335339071889386, -3.2244873046875 52.41582261237878, -2.98278808593749 52.519563529257425, -3.0047607421875057 52.562995039558004, -3.1091308593749902 52.536273041459474, -3.1420898437499982 52.57634993749886, -2.9937744140625146 52.739617554270886, -3.158569335937485 52.77950854257623, -3.125610351562511 52.89896238858775, -2.9718017578124986 52.94863788488319, -2.8509521484374933 52.938707345098635, -2.785034179687512 52.88902044899523, -2.7246093749999924 52.928774525801366, -2.7301025390624884 52.9850303652323, -2.99926757812501 53.14347558459451, -2.911376953125013 53.173119202640635, -3.2904052734375146 53.37349790058685, -3.3398437500000093 53.39643212709597))', 4326);
    DECLARE @Cardiff geography = geography::STPointFromText('POINT(-3.21 51.24)', 4326);
    
    SELECT @Wales, 'Wales' UNION ALL SELECT @Cardiff.STBuffer(2000), 'Cardiff'

     alt=

    The results confirm why you're getting a NULL result - Cardiff does not lie within Wales, at least, not using the geometries you've defined!
    I would suggest that your coordinates for Cardiff are incorrect. The site at http://www.convertunits.com/distance/airport/CWL suggests a latitude of 51.397 and longitude of -3.346, which does lie within the polygon defined.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, August 14, 2009 4:27 PM
    Answerer
  • Many thanks Tanoshimi for your explanation.  I will review this further to see whether my region (Wales) or my point (Cardiff airport) is in error. 

    Cal
    Monday, August 17, 2009 9:23 PM
  • Quite a rookie mistake actually for misunderstanding the syntax for spatial data.  51 24N is not equal to 51.24 but instead 51.397 as you point out.  Apparently it's 24/60, not 24/100. 

    While the utility at http://mikeo.co.uk/demo/sqlspatial/default.aspx is quite handy, the underlying map is not particularly easy to use for drawing country outlines.  With the labels off, there are no country outlines at all.  With the labels on, depending on the level of zoom, there are many different city names which obscure the borders and make an accurate rendition completely impossible.  After a considerable degree of testing, about the most accurate I can get is around 95% (estimated). 

    While I don't suppose that there are spatial data representations of minor political districts such as the provinces of Somalia, for example, are there any publicly available representations which match United Nations member countries or US states?
    Wednesday, August 19, 2009 5:43 AM
  • Ah - I didn't notice you had stated the coordinate in DMS format. Oh well, at least you know now!

    If you want free spatial data:
    For UN data: http://geodata.grid.unep.ch/
    For US states: http://www.census.gov/

    Note that these sites, as well as most common other sources of spatial data, use the ESRI shapefile format. You'll therefore need some way of importing shapefiles into SQL Server. The most popular free tool is Morten Nielsen's Shape2SQL tool, available here: http://www.sharpgis.net/page/Shape2SQL.aspx

    Also, as with any other sort of data, it is true that you get what you pay for. You may well find that the spatial data from the above sources may need cleaning up in places, but it will almost certainly provide greater accuracy than tracing a shape in Virtual Earth... :)

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, August 19, 2009 6:45 AM
    Answerer