0

I want to basically take one spatial table of polygons and determine if any of the polygons interest with another table of polygons and save the results in a new table with a new column saving the result as true or false. The script below works:

CREATE TABLE test_table AS
SELECT parcels.*, COALESCE(ST_Intersects(parcels.wkb_geometry, flood.wkb_geometry), false) AS intersects
FROM parcels LEFT OUTER JOIN flood ON ST_Intersects(parcels.wkb_geometry, flood.wkb_geometry);

However I do get duplicates when a polygon from the first table is intersected by multiple polygons from the second table. As seen below:

enter image description here

I would like to only have one entry per polygon, even if it has been intersected multiple times. I have been removing duplicate rows as a work around, but I wanted to know if there is a more elegant way of doing this.

4

If you add DISTINCT to your select statement that should return what you are looking for.

Try:

CREATE TABLE test_table AS
SELECT DISTINCT parcels.*, COALESCE(ST_Intersects(parcels.wkb_geometry, flood.wkb_geometry), false) AS intersects
FROM parcels LEFT OUTER JOIN flood ON ST_Intersects(parcels.wkb_geometry, flood.wkb_geometry);
  • Wouldn't you need to specify which column should be DISTINCT e.g. the IDs? – blabbath Jun 19 '18 at 6:00
  • Nope, no need to. The whole record is added multiple times, so we can just pick out the distinct ones. I tried on a test run to be sure. – Cushen Jun 19 '18 at 6:03

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.