locked
How to find out which polygon a lat/long is in? RRS feed

  • Question

  • Hi,

    Can anyone help me please?

    I have managed to upload a shapefile into my SQL Server 2008 and all looks fine.

    I would now like to be able to find out which polygon a certain lat/long is in.

    Basically, the table holds polygons for UK Local Authority Boundaries. Now I would like to be able work work out which Local Authority a particular lat/long is in.

    Is this possible, and if so can anyone give me some pointers.

    Thanks

    Trev
    Friday, February 12, 2010 4:11 PM

Answers

  • Your shapefile uses the British National Grid System, so you want to use the geometry datatype, with SRID 27700. Coordinates measured in this system are projected coordinates (Easting / Northing) measured in metres, and look something like this:
    (644400, 327000)

    However, your original question was to ask how to determine which county a latitude/longitude coordinate was in - these are geographic coordinates (probably using SRID 4326), and look something like (52, 0.16)

    SQL Server can't convert between coordinate types or reproject different spatial reference systems, so you need to convert your shapefile into SRID 4326 before importing it into SQL Server. You can do this using a tool such as Safe FME.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Trevor Daniel Sunday, February 14, 2010 12:00 PM
    Saturday, February 13, 2010 5:14 PM
    Answerer

All replies

  • You want the STIntersects() method, like this:

    DECLARE @point geography = geography::Point(52, 0, 4326);
    
    SELECT *
    FROM Table
    WHERE LocalAuthority.STIntersects(@point) = 1;

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, February 12, 2010 7:14 PM
    Answerer
  • Many thanks...

    That looks just what I need.

    When I run it i get an error though...

    Msg 206, Level 16, State 2, Line 3

    Operand type clash: master.sys.geography is incompatible with ibbd.sys.geometry

    Any idea what is causing it?

    Thanks

    Trev

    Saturday, February 13, 2010 12:52 PM
  • More information on this....

    I used ShapeToSql to upload the shapfile I had and the following options were used...

    Planar Geometry and NOT Geography (Spheric)

    I tried Geography but the program warned my that data extended beyond allowable bounds of the geography type

    I also checked the box "Set SRID = 4326"

    I also checked the option "Create Spatial Index"

    Not sure if the above has anything to do with it.

    I then changed the above sql and replaced the 2 geography occurences to geometry and ran the query again... this time i get the following error...

    Msg 6522, Level 16, State 1, Line 3

    A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":

    System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.

    System.ArgumentException:

    at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid()

    at Microsoft.SqlServer.Types.SqlGeometry.STIntersects(SqlGeometry other)

    Can anyone help please?

    Thanks

    Trev

    Saturday, February 13, 2010 1:11 PM
  • here is the contents of my prj file, dont know if it has any relevance :

    PROJCS["British_National_Grid"
    GEOGCS["GCS_OSGB_1936"
    DATUM["D_OSGB_1936"
    SPHEROID["Airy_1830",6377563.396,299.3249646]]
    PRIMEM["Greenwich",0]
    UNIT["Degree",0.017453292519943295]]
    PROJECTION["Transverse_Mercator"]
    PARAMETER["False_Easting",400000]
    PARAMETER["False_Northing",-100000]
    PARAMETER["Central_Meridian",-2]
    PARAMETER["Scale_Factor",0.999601272]
    PARAMETER["Latitude_Of_Origin",49],UNIT["Meter",1]]
    Saturday, February 13, 2010 3:18 PM
  • Srid 4326 means geographic wgs84, and you have coordinates with other datum and also projected to Transversal Mercartor. So, upload the shape to sql server with the correct srid, or, if you don't know what it is, with 0. After that, you have a geometry table, not geography. So, try the same code Tanoshimi proposed, but using geometry:

    DECLARE @point geometry = geometry::Point(52, 0, 0);
    
    SELECT *
    FROM Table
    WHERE LocalAuthority.STIntersects(@point) = 1;

    where the third zero is the same srid you used to upload the shape. You can look for the correct srid at http://www.spatialreference.org
    Saturday, February 13, 2010 4:32 PM
  • Your shapefile uses the British National Grid System, so you want to use the geometry datatype, with SRID 27700. Coordinates measured in this system are projected coordinates (Easting / Northing) measured in metres, and look something like this:
    (644400, 327000)

    However, your original question was to ask how to determine which county a latitude/longitude coordinate was in - these are geographic coordinates (probably using SRID 4326), and look something like (52, 0.16)

    SQL Server can't convert between coordinate types or reproject different spatial reference systems, so you need to convert your shapefile into SRID 4326 before importing it into SQL Server. You can do this using a tool such as Safe FME.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Trevor Daniel Sunday, February 14, 2010 12:00 PM
    Saturday, February 13, 2010 5:14 PM
    Answerer
  • THANKS VERY VERY VERY MUCH!

    I have now got this to work.

    As tanoshimi suggested, I have used Safe FME to convert the file to 4326.

    Re uploaded it with shapetosql

    Run the following sql

    DECLARE

     

     

    @point geography = geography::Point(XX, XX, 4326);
    SELECT *
    FROM Table
    WHERE
    geom.STIntersects(@point) = 1;

    And it works perfectly!!!

    Thanks guys. brilliant help!

    Trev
    Sunday, February 14, 2010 10:47 AM
  • Thank You! (you just sold another copy of your E-Book)
    Friday, May 7, 2010 8:58 PM
  • im confused?

    I don't sell any e-books?

    Trev

    Saturday, May 8, 2010 8:38 AM
  • I think that comment might have been directed at me ;)

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Saturday, May 8, 2010 9:16 AM
    Answerer
  • @tanoshimi - It took me an hour to find this answer. But, things like the SRID matter. I don't mean to sound like a commercial, but it is hard to find information on how to do this stuff. Your book "Beginning Spatial with SQL Server" explains what all this stuff means.
    Saturday, May 8, 2010 12:15 PM
  • Did you post this comment in the correct thread? I think this issue is answered....
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, September 8, 2010 6:21 AM
    Answerer