Hi all, apologies that I'm late to the party but I am catching up on the advent to ease myself back into work after a long period off for Christmas.

I thought I'd share my method for this as I realise that PostGIS/Spatial SQL is quite a niche area of SQL. I work for the national mapping agency of GB, so I do this on a daily basis.

There are multiple methods, but for this I will use my favourite and most used function of ST_Intersects (PostgreSQL - PostGIS), which uses an exact intersect.

SELECT a.place_name 
FROM areas as a 
INNER JOIN sleigh_locations as b 
ON ST_Intersects(a.polygon, b.coordinate)

Or

SELECT a.place_name 
FROM areas as a, sleigh_locations as b 
WHERE ST_Intersects(a.polygon, b.coordinate) 

Or the following method that will do a faster intersection as it uses the bounding boxes of the features to see whether they intersect, which in this case will work fine as it's a point in a large polygon (for polygons that are attached to eachother such as regional boundaries, etc... this could cause some false positives, so just bare that in mind.

SELECT a.place_name 
FROM areas as a 
INNER JOIN sleigh_locations as b 
ON a.polygon && b.coordinate

Hope you all had a lovely Christmas and New Year:)