locked
polygon and determine what polygon a passed point belongs to RRS feed

  • Question

  •  

    Dear all,

     

    I have an ESRI shape file that describes a polygon of a certain geographical area. Like for example a state. That polygon verticies would be in lat,long. I would like to store this into SQL server in database table. Like store the state name and the spatial data related to it.

     

    Then what i would like to do is to allow a user to pass me a lat and lon of a certain point and for me through a stored procedure to determine what state that point is in. So i assume i would have to run through all the entries in the table and determine what polygon the point belongs to.

     

    Any advice or any samples of how to do something like this available?

     

    Any help would be apreciated.

     

    Sincerely

    Dan

    Monday, August 11, 2008 6:43 AM

Answers

  • Firstly - Sorry Kent. I was slightly teasing in my message (which, admittedly, doesn't come across very well in forums) - you know that we have a friendly rivalry for the title of 'chief pedant' in this forum and I couldn't resist the chance to pick you up on this one...

    The reason for STIntersects() rather than STContains() was because I was assuming that the original poster would use the geography datatype, for which STContains() is not defined. If they were using the geometry datatype, I would use STContains() too.

    You're correct - I don't know whether Morten's tool performs reprojection... I was assuming that specifying the SRID allowed you to describe the SRID of incoming shape data, rather than prescribe the SRID of outgoing data... Morten?

    And perhaps the difference between NAD83 and WGS84 is minimal for the application in question - but it's the principle that's important. There is already a lot for people to learn about geometry/geography for new users - let's not confuse the issue by being sloppy ourselves (and of course I know YOU know the difference between the two systems...).

    So - no hard feelings?
    Thursday, August 21, 2008 5:49 PM
    Answerer
  • ktegels: Read the FAQ. The tool does NOT do any reprojection. It merely sets the SRID you specify. You are required to input the correct SRID. Tanoshimi is right. What you are doing is VERY wrong.

     

    OGR2OGR is a great tool for doing quick and free reproject and datum transforms.

     

    Just because you got the right answer doesn't mean a thing. The datum errors are usually within a mile, and you are testing against counties that is a lot larger, but it's a LOT bigger than an observational error between NAD83 and WGS84 (unless you have VERY poor observations) but varies a lot from place to place. What the article refers to is the datum transform, ie the center of the ellipsoid, but NOT the size of the ellipsoid which is where you will see a lot of the error, especially as you move north.

    Friday, August 22, 2008 2:11 AM
    Answerer

All replies

  • This is kind of challenging since the Geography data expects vetricies to be longtitude/latitude order. However, I did sometime pretty similar to this in about 15 minutes this morning. Steps are as follows:

    1. Downloaded Morten Nielsen's very help SqlSpatial tools from http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx
    2. Downloaded the US Cenus state boundary shape file for the year 2000 from http://www.census.gov/geo/www/cob/st2000.html
    3. Unpacked both into a local folder
    4. Created a database in SQL Server 2008 (Microsoft SQL Server 2008 (RTM) - 10.0.1600.22) named "spatial"
    5. Ran Shape2Sql.exe to load the data from the shape file into a table called US_states
    6. Created the stored procedure shown below
    7. Tested that with the point -122.12574005126953,47.64359090020491 (SRID 4326). Sure enough, that's in Washington state.

    Code Snippet
    create procedure dbo.PointInState(@long float,@lat float)
    as begin
     declare @g geometry = geometry::Point(@long,@lat,4326);
     select id,name from dbo.US_states
     where geom.STContains(@g)=1
    end

     

     

    Helpful?

    Thursday, August 21, 2008 12:29 PM
    Answerer
  • Ouch! No, no no....

    Unfortunately, one of the features of working with spatial data that many people don't appreciate is that there is not a single 'latitude/longitude' co-ordinate pair that uniquely identifies a point on the earth. There are, in fact, many different systems of measuring latitude and longitude, and each system will have different values for the same point. Similarly, a given set of co-ordinates could refer to different places on the Earth, depending on the system from which they were obtained. Each spatial reference system is identified by a unique code, called a SRID (Spatial Reference Identifer).

    So, when you import any spatial data into SQL Server, you must be sure to state the appropriate SRID of the system from which the co-ordinates were obtained. In this case, U.S. Census data from the source specified is in NAD83 datum  (http://www.census.gov/geo/www/cob/st_metadata.html), which is denoted by SRID 4269.
    If you were to import it into SQL Server using SRID 4326 (the spatial reference system used by GPS units), as shown above, you would get erroneous results.

    Note that you can only perform operations against data defined using the same SRID, so if you import the data as SRID 4269, then you must also define the point which you are testing whether it is in a given state using the same system.
    If instead, you want to use the (most widespread) SRID of 4326, then you must first reproject the census boundary data using a third party tool such as Safe FME, or MapWindow (free).

    Once you've got your imported data in the correct format, then you can use the STIntersects() method of the geography datatype to determine which state a given point lies in.


    Thursday, August 21, 2008 1:33 PM
    Answerer
  • Amigo, you know I think you're right most of the time, but on this one, I think I lead you astray.

     

    Did I mention that I had his tool set the SRID to 4326? No. Sorry about that, it's my bad.

     

    What we don't know that Morten's tool doesn't (or does) do the conversion of SRIDs or not when you set it. While what you say here is right, you're making the assumption this tool doesn't do the reprojection. I don't know if it does or not. The fact that I got the right answer makes wonder either way.

     

    As to picking STIntersect over STContains, its boils down to what easier to think about, I'd say. The concept of a point "intersecting" the interior of a polygon -- while it makes sense to somebody that's read the OCG specs -- probably doesn't to the masses. Therefore, I'd suggest that contains makes more sense to the reader.

     

    Morten, can you answer the question "if I set an SRID with your ShapeFile Uploader for SQLServer 2008, does it do the reprojection?" question

    Thursday, August 21, 2008 2:30 PM
    Answerer
  • By the way, whats the difference between NAD83 and WGS84 for the referenced space (US states and equvillents)? Reading http://www.mentorsoftwareinc.com/resource/Nad83.htm makes it sound like its less than observational error anyway... (see section 22.2.4)

    Thursday, August 21, 2008 2:44 PM
    Answerer
  • Firstly - Sorry Kent. I was slightly teasing in my message (which, admittedly, doesn't come across very well in forums) - you know that we have a friendly rivalry for the title of 'chief pedant' in this forum and I couldn't resist the chance to pick you up on this one...

    The reason for STIntersects() rather than STContains() was because I was assuming that the original poster would use the geography datatype, for which STContains() is not defined. If they were using the geometry datatype, I would use STContains() too.

    You're correct - I don't know whether Morten's tool performs reprojection... I was assuming that specifying the SRID allowed you to describe the SRID of incoming shape data, rather than prescribe the SRID of outgoing data... Morten?

    And perhaps the difference between NAD83 and WGS84 is minimal for the application in question - but it's the principle that's important. There is already a lot for people to learn about geometry/geography for new users - let's not confuse the issue by being sloppy ourselves (and of course I know YOU know the difference between the two systems...).

    So - no hard feelings?
    Thursday, August 21, 2008 5:49 PM
    Answerer
  • Of course there's no hard feeling, mate and I do thank you for reminding me to confess to omission of the SRID change.

     

    >>And perhaps the difference between NAD83 and WGS84 is minimal for the application in question - but it's the principle that's important.

     

    Well, you can be the 'chief pedant' all you want. I'll settle for being chief problem. Smile

     

    kt

    Thursday, August 21, 2008 6:47 PM
    Answerer
  • ktegels: Read the FAQ. The tool does NOT do any reprojection. It merely sets the SRID you specify. You are required to input the correct SRID. Tanoshimi is right. What you are doing is VERY wrong.

     

    OGR2OGR is a great tool for doing quick and free reproject and datum transforms.

     

    Just because you got the right answer doesn't mean a thing. The datum errors are usually within a mile, and you are testing against counties that is a lot larger, but it's a LOT bigger than an observational error between NAD83 and WGS84 (unless you have VERY poor observations) but varies a lot from place to place. What the article refers to is the datum transform, ie the center of the ellipsoid, but NOT the size of the ellipsoid which is where you will see a lot of the error, especially as you move north.

    Friday, August 22, 2008 2:11 AM
    Answerer
  • Morten, thanks for the answer. For those folks who end up looking for that FAQ, there's a hyperlink to it. I'll post an updated version of this to blog in the morning.

     

    http://www.sharpgis.net/page/Shape2SQL.aspx

     

    Thanks all!
    kt

    Friday, August 22, 2008 2:25 AM
    Answerer
  • I added some more comments to the page to make this clear.

     

    Friday, August 22, 2008 2:41 AM
    Answerer
  • @Dan

     

    Did you find any solution to this problem? If you did, can you please post it here.

     

    Thanks,
    uday

    Wednesday, October 20, 2010 3:47 PM
  • shape2sql.exe download link(http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx) is broken. It redirects to error page(http://www.sharpgis.net/error404.aspx). Please tell me how can I get it.

     

    Thanks:

    Hassan Ali,

    Web Engineer

    Monday, December 6, 2010 5:07 AM
  • shape2sql.exe download link(http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx) is broken. It redirects to error page(http://www.sharpgis.net/error404.aspx). Please tell me how can I get it.

     

    Thanks:

    Hassan Ali,

    Web Engineer

    Monday, December 6, 2010 5:09 AM