locked
Point in Polygon Assingment RRS feed

  • Question

  • I am trying to assign a polygon id to each point id that falls inside the underlying polygon.

    I am trying to run the query below in SqlServer 2008 an it runs fine on small polygon sets, but extremely slow on large polygon sets.  Not entirely sure it is using the spatial index.  It is Geograhpy spatial type.  Any suggestions greatly appreciated.

    Select
    p.objectid,  z.mukey
    from tester_geo_pnts p  cross join TEST_DMG_POLY_GEOG z
    where Geography::STGeomFromText(p.shape.STAsText(), 4326).STIntersects(Geography::STGeomFromText(z.shape.STAsText(), 4326)) = 1
    order by p.objectid

    Monday, October 17, 2011 2:52 PM

Answers

  • what is the datatype for z.shape? what is the srid for z.shape? What is the srid for p.shape?
    Ed Katibah Spatial Ed Blog
    • Proposed as answer by Spatial Ed Wednesday, October 19, 2011 9:03 PM
    • Marked as answer by Stephanie Lv Friday, October 21, 2011 10:16 AM
    Wednesday, October 19, 2011 8:02 PM
  • Thank you Ed the different SRID values was the problem.  My polygon Z SRID (4269) and the Points P (4326), once I reprojected the Points to SRID 4269 the query ran fine and used the index - thank you for the help
    • Proposed as answer by dmgimg Thursday, October 20, 2011 2:28 AM
    • Unproposed as answer by dmgimg Thursday, October 20, 2011 2:28 AM
    • Proposed as answer by dmgimg Thursday, October 20, 2011 2:29 AM
    • Marked as answer by Stephanie Lv Friday, October 21, 2011 10:16 AM
    Wednesday, October 19, 2011 9:01 PM

All replies

  • I have this working a bit faster now but can only Hint the Index on the points table.  When I try to Hint the Index on the MUPOLYGON table I get this error:

    "Msg 8622, Level 16, State 1, Line 3
    Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN."

     

    Select
    p.objectid,
    z.mukey
    from tester_geo_pnts p                WITH(INDEX(S27_idx))
        cross join MUPOLYGON_GEOG_AZ z 
    where
    p.shape.STIntersects(Geography::STGeomFromText(z.shape.STAsText(), 4326)) = 1 
    order by p.objectid

    Tuesday, October 18, 2011 1:54 PM
  • You need to remove the vacuous object construction (Geography::STGeomFromText(z.shape.STAsText(), 4326))) from the STIntersects method.  Replace with z.shape.

    - Ed


    Ed Katibah Spatial Ed Blog
    Wednesday, October 19, 2011 7:23 PM
  • When I remove (Geography::STGeomFromText(z.shape.STAsText(), 4326))) and replace with z.shape  my results set is empty.  Using the STGEOM is the only way I can get the correct results - am i missing something?

    thanks

    Wednesday, October 19, 2011 7:36 PM
  • what is the datatype for z.shape? what is the srid for z.shape? What is the srid for p.shape?
    Ed Katibah Spatial Ed Blog
    • Proposed as answer by Spatial Ed Wednesday, October 19, 2011 9:03 PM
    • Marked as answer by Stephanie Lv Friday, October 21, 2011 10:16 AM
    Wednesday, October 19, 2011 8:02 PM
  • Thank you Ed the different SRID values was the problem.  My polygon Z SRID (4269) and the Points P (4326), once I reprojected the Points to SRID 4269 the query ran fine and used the index - thank you for the help
    • Proposed as answer by dmgimg Thursday, October 20, 2011 2:28 AM
    • Unproposed as answer by dmgimg Thursday, October 20, 2011 2:28 AM
    • Proposed as answer by dmgimg Thursday, October 20, 2011 2:29 AM
    • Marked as answer by Stephanie Lv Friday, October 21, 2011 10:16 AM
    Wednesday, October 19, 2011 9:01 PM