I have been spatially joining land use zones to parcels successfully with the following query:
CREATE TABLE new_parcel AS SELECT DISTINCT ON(t.geom) t.*, m.basezone, m.overlay FROM parcel AS t LEFT JOIN zoning AS m ON st_intersects(t.geom, m.geom) AND st_area(st_intersection(t.geom, m.geom)) / st_area(t.geom) > 0.5
Currently, more than 50% of the parcel's area must be within a single zone for a join to occur.
However, it turns out that some of the parcels overlap 3 or more zones and are not getting joined to the
zoning table because less than 50% of their area overlaps. For example, the parcels of dark purple color overlap more than two zones:
How would one modify the query to add extra columns in the resulting table which would include data from all zones overlapped?
The existing code produces a table that has
basezone | overlay
How could one produce
and so on for all zones overlapped?