Search code examples
postgresqlpostgis

ST_Intersect returns with double result

I am trying to write case statement to intersect point in areas using Postgres as below:

SELECT Point.id, Point.geom, Box.Postcode,
CASE
    WHEN ST_Intersects(Box.geom, Point.geom) AND Box.postcode = 'A' THEN 'A'
    WHEN ST_Intersects(Box.geom, Point.geom) AND Box.postcode = 'B' THEN 'B'
    WHEN ST_Intersects(Box.geom, Point.geom) AND Box.postcode = 'C' THEN 'C'
    ELSE 'No postcode'
END AS PointPostcode

The idea is that every Point which is located inside the Box will have a new attribute column named 'PointPostCode' and have postcodes information: 'A','B','C' depends on their locations. And for the point located outside the box will have information 'No postcode'.

I run those queries successfully, I also got the new column as what I want. The points located inside the Box also have the postcode information correctly but at the sametime it is also given 'No postcode' label for all point (re-double the value).

Could someone help me to figure it out where is the mistake of my query?

Solution

  • I believe you want to execute following query:

    SELECT Point.id, Point.geom, Box.Postcode,
CASE
    WHEN Box.postcode = 'A' THEN 'A'
    WHEN Box.postcode = 'B' THEN 'B'
    WHEN Box.postcode = 'C' THEN 'C'
    ELSE 'No postcode'
END AS PointPostcode
WHERE ST_Intersects(Box.geom, Point.geom)