locked
Points in Polygon Query RRS feed

  • Question

  • Hi All -

     

     I have been looking at taking advantage of SQL Server 200* spatial toolset to do some points in a polygon join.  Anyhow, I have large tables (~500K records) that I would need to query with POLYGONS or MULTIPOLYGONS on the fly.  The business tables would have a type geography field respresenting the location of the point and the polygon(s) created would also have a geography representation.  I created a demo (below) to test performance and found it took almost 10 mins to query a table of 285K records against a single polygon.  Does that sound right?  I used Shape2Sql to load a test points shapefile in SQL Server and had it apply a spatial index.  The code I used is below - is there a better way to do this than run each record through a loop using STIntersects ?

    -- Create polygon
    			
    
    			IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL 
    				DROP TABLE dbo.SpatialTable;
    			GO
    
    			CREATE TABLE SpatialTable 
    				( id int IDENTITY (1,1),
    				GeogCol1 geography, 
    				GeogCol2 AS GeogCol1.STAsText() );
    			GO
    
    			INSERT INTO SpatialTable (GeogCol1)
    			VALUES (geography::Parse('POLYGON((-121.038 37.597, -120.995 37.609, -120.998 37.636, -121.039 37.639, -121.038 37.597))'));
    			GO	  
    
    			SELECT * FROM SpatialTable
    
    
    -- The following will test if the points in the table above are within the newly created polygon
    
    DECLARE		@poly				geography,
    			@point				geography,
    			@iReturnCode		int,
          @iCurrentRowId		int,
          @iLoopControl		int,
          @iNextRowId			int
    
    SELECT		@poly = GeogCol1
    FROM		dbo.SpatialTable
    
    SELECT		@iLoopControl = 1
    
    SELECT		@iNextRowId = MIN(id)
    FROM		dbo.test_points_indexed
    
    --Get First row of data
    
    SELECT		@iCurrentRowId = id,
    			@point = geog
    FROM		test_points_indexed
    WHERE		id = @iNextRowId 
    
    UPDATE		dbo.test_points_indexed
    SET			In_Out_of_Poly = (SELECT @poly.STIntersects(@point))
    WHERE		id = @iCurrentRowId
    
    
    ---LOOP
    
    WHILE		@iLoopControl = 1
    
    
    BEGIN
    
    --Reset loop vatiables
    
    --SELECT  iNextRowId = NULL  
    	
    --get next id 
    
    SELECT		@iNextRowId = MIN(id)
    FROM		test_points_indexed
    WHERE		id > @iCurrentRowId
    
    
    --did we get a valid next row id?
    
    IF ISNULL(@iNextRowId,0) = 0
      BEGIN
          BREAK
      END
          
    -- get the next row.
    
    SELECT		@iCurrentRowId = id,
    			@point = geog
    FROM		test_points_indexed
    WHERE		id = @iNextRowId 
    
    UPDATE		dbo.test_points_indexed
    SET			In_Out_of_Poly = (SELECT @poly.STIntersects(@point))
    WHERE		id = @iNextRowId
    
    		 
    END
    
    
    SELECT *
    FROM dbo.test_points_indexed
    Friday, January 7, 2011 7:51 PM

Answers

  • I'm slightly lost as to why you're doing all the "looping" to update the In_out_of_Poly column for each point in the test_points_indexed table individually. Don't you just want to do this?

     

    DECLARE @poly geography;
    SELECT @poly = GeogCol1 FROM dbo.SpatialTable;
    
    UPDATE dbo.test_points_indexed
    SET In_Out_of_Poly = @poly.STIntersects(geog);
    

     

    This will set the In_out_of_polygon value for each point to 1 if it lies in the polygon, or 0 otherwise, as follows:


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Saturday, January 8, 2011 10:13 AM
    Answerer
  • Never mind - it was beacuse I was had my script running an update - a selet took less than 3 secs.
    Wednesday, January 12, 2011 5:16 PM

All replies

  • I'm slightly lost as to why you're doing all the "looping" to update the In_out_of_Poly column for each point in the test_points_indexed table individually. Don't you just want to do this?

     

    DECLARE @poly geography;
    SELECT @poly = GeogCol1 FROM dbo.SpatialTable;
    
    UPDATE dbo.test_points_indexed
    SET In_Out_of_Poly = @poly.STIntersects(geog);
    

     

    This will set the In_out_of_polygon value for each point to 1 if it lies in the polygon, or 0 otherwise, as follows:


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Saturday, January 8, 2011 10:13 AM
    Answerer
  • Hi Tanoshimi -

    Thanks for pointing out my error - no need to loop - I should have picked that up.  I re-ran the query and now the time to query the 285K records is approx 30 seconds - in your experience does that sound right?  Is there anything you might know of or can point me to that I could use to speed up this query?

    Thansk again -

    Stan

    Wednesday, January 12, 2011 4:53 PM
  • Never mind - it was beacuse I was had my script running an update - a selet took less than 3 secs.
    Wednesday, January 12, 2011 5:16 PM
  • Hi all,

    STContains() is a proper method, too. Examples:

    update test_points_indexed
    set in_out_of_poly = geometry::Parse('POLYGON((121.038 37.597, 120.995 37.609, 120.998 37.636, 121.039 37.639, 121.038 37.597))').STContains(geog)
    from tab;

    update test_points_indexed
    set in_out_of_poly = x.polygon.STContains(y.geogcol1)
    from spatialtable x, test_points_indexed y;

    Regards,

    Klaus

    Sunday, January 23, 2011 8:13 AM
  • @K.Kuehne - STContains() is a method for the geometry datatype only - @SMarriott is using geography data.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Sunday, January 23, 2011 8:42 AM
    Answerer
  • Hello Tnoshimi,

    sorry, I didn't look on the datatype...

    Regards,

    Klaus

    Sunday, January 23, 2011 11:01 AM
  • STContains() can be used for geography data also as per http://technet.microsoft.com/en-us/library/ff929274.aspx

    Monish Gupta

    Monday, March 10, 2014 6:19 PM