CODEWITHSUNDEEP
sql-servert-sqlspatial-query
SurfRodeoDBA
SurfRodeoDBA

Reputation: 24

Find which points in one table are in which polygons in another table

I have a table t which are my points

create table t ( 
  PointID bigint 
 ,lat varchar(9)
 ,long varchar(9)
 ,geom geometry 
 ,PolygonID bigint 
);

And I have table p which are my polygons

create table p ( 
 PolygonID bigint 
 ,geom geometry 
);

Now I need something like 

;with polygon_cte as p 
(select geom from p) 

UPDATE t 
SET t.PolygonID = p.PolygonID 
WHERE p.geom.STContains(t.geom)

I'm sure my points and polygons have converted right, and I know I'll need to add a spatial index once I have this down but I'm having trouble figuring out how to update t my points table with the right PolygonID from my polygon table. There will be only one polygon for each point as the polygons are mutually exclusive in terms of geographic area.

Upvotes: 0

Views: 909

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5684

Try this:

UPDATE p SET PolygonID=(SELECT PolygonID FROM t WHERE t.geom.STContains(p.geom)=1)

If there are multiple polygons for the same point, you will get a "Subquery returned more than 1 value" error.

Upvotes: 2

Related Questions