Menu
I have 2 tables one with points as geographies and other with polygons as geographies. I am able to find which polygon a single point falls(from the point table) by the following query:
DECLARE @p geography;
select @p = PointGeom from dbo.PointTable where ID = 1
SELECT a.ID, ATTRIBUTE1, geom
from dbo.PolygonTable a
where geom.STIntersects(@p) = 1;
However, I want to do a join between the two tables and get the polygons in which each of the points in the Point Table fall. Is it even possible? Or do I need to loop through the Point table and call the above query multiple times?
261
Spatial joins are the bread-and-butter of spatial databases. They allow you to combine information from different tables by using spatial relationships as the join key. Much of what we think of as “standard GIS analysis” can be expressed as spatial joins.
A spatial join involves matching rows from the Join Features to the Target Features based on their relative spatial locations. By default, all attributes of the join features are appended to attributes of the target features and copied over to the output feature class.
A JOIN simply pairs up rows based on whether a condition is true. In a spatial join, the condition is just a geometric operation on geometric data (e.g., two polygons must intersect). This does not change with spatial queries.
For example, if you have 1 land parcel. This parcel has 3 points in it with 3 different owners. A spatial join will create 3 identical land parcels. But each record will have the landowner's name on it.
This should work:
SELECT
polyTable.[PolygonID]
, pointTable.[PointID]
FROM
[PolygonTable_Name] polyTable WITH(INDEX([SPATIAL_INDEX_NAME]))
INNER JOIN
[PointTabl_Name] pointTable
ON
polyTable.Geog.STIntersects(pointTable.Geog) = 1
I have added an index hint " WITH(INDEX(...)) " as this will speed up the query.
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
