Point in Polygon with Join

Problem

How do you find which points from one table are inside of polygon features from another table.

Solution

The Sql Spatial functions used for this are STCentroid and STWithin. In this example both layers (Parcels and Zipcode) are polygons. The result shows the zipcode for all parcels by accountid.

SELECT a.AccountID , b.Name AS ZipCode FROM dbo.Parcels a , dbo.zipcode b WHERE a.geom.STCentroid().STWithin(b.geom) = 1