locked
Point in Polygon SQL Query RRS feed

  • Question

  • Ok so here it goes...

    Point in polygon search - I have two tables, one full of polygons and one full of points.

    I would like the unique ID from the points table to be linked with the polygon, in the polygon table.

    STIntersects() is clearly the right method. Does anyone have an example of this, or can they explain the logical steps?

    I know the points are within the polygons, I am not interested in the yes/no answer I actually need the ID so that I can tie the two datasets together. Without having to copy and paste for the rest of my life :) Because no-one want to do that!

    How do I go about starting this?

    First time forum user... so sorry if I havent given enough detail.

    Thanks, S*

    Friday, September 21, 2012 9:48 AM

Answers

All replies

  • The correct syntax will obviously depend on the exact structure of your table, but it's basically this:
    SELECT
      PointID,
      PolygonID
    FROM
      PointsTable
      JOIN PolygonTable ON PointGeom.STIntersects(PolygonGeom) = 1;



    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    • Marked as answer by Shulei Chen Monday, October 1, 2012 9:42 AM
    Friday, September 21, 2012 9:53 AM
    Answerer
  • Thanks

    Points are displayed as x,y and polygons as geometry...

    Friday, September 21, 2012 9:59 AM
  • So you need to create Points from your x,y coordinates first.

    ALTER TABLE Points
    CREATE PointGeom geometry;

    Then:

    UPDATE Points
    SET PointGeom = geometry::Point(x,y,srid);
    (replace srid with whatever srid you're using)


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    • Proposed as answer by Shulei Chen Wednesday, September 26, 2012 9:37 AM
    • Marked as answer by Shulei Chen Monday, October 1, 2012 9:42 AM
    Friday, September 21, 2012 11:05 AM
    Answerer
  • Thanks for your help, I still keep getting no rows within my results, going to test it on one specific item to see if that will work.

    Monday, September 24, 2012 10:50 AM
  • and doesnt work if its just looking at one item... :s

    Monday, September 24, 2012 11:03 AM
  • Are you sure your polygons and points are defined using the same SRID? And that your polygon is oriented correctly? And that the point is *definitely* within the polygon?


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    Monday, September 24, 2012 7:15 PM
    Answerer