3

I have been performing spatial joins but there is a problem. The geometries of both layers are slightly misaligned. I would like to join by location where parcels are within historic_subdistricts and to keep both matching and non matching records. The query below works but the problem is that ST_Within only works if geometry of one layer is completely contained by the other.

CREATE TABLE test_join AS
SELECT t.*, m.*
FROM parcels AS t
LEFT JOIN historic_subdistricts= AS m
ON ST_Within(t.geom, m.geom)

How would one write a query to perform a spatial join if 90% of the area of a parcel in parcels is within a subdistrict in historic_subdistricts ?

Here is a visualization of the problem:

enter image description here

6

You can try below query by getting the intersected area of both geometries and the total area of Parcel geometry, then get the percentage and compare it 90%:

CREATE TABLE test_join AS
SELECT t.*, m.*
FROM parcels AS t , historic_subdistricts AS m
where st_intersects(t.geom, m.geom) and    
(st_area(st_intersection(t.geom, m.geom))/st_area(t.geom)) > 0.9

Make sure both fields are indexed.

2

My favourite trick for cases like this is to join on the centroids:

 SELECT parcel.id, historic_subdistricts.id
 FROM parcel JOIN historic_subdistricts
 ON ST_Contains(historic_subdistricts.geom, ST_Centroid(parcel.geom))

This neatly avoids double-counting, and unless your overlaps are really really large should correctly assign all your boundary shapes.

  • This is normally a good trick, but be careful depending on your polygon shapes. For some polygons, the centroid may be outside of the polygon. See the example here: postgis.org/docs/ST_Centroid.html If you have multipolygons, you need to take even more care. – amball Dec 13 '16 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.