Help PostgreSQL: Case Statement (ST_Intersects)

[Reyalino]

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
INTO PointCode
FROM Point, Box

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 not with the 'No postcode' information. Could someone help me to correct the query? :-D

[Reyalino]

On 5/4/2017 at 11:05 AM, Reyalino said:

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
INTO PointCode
FROM Point, Box

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 not with the 'No postcode' information. Could someone help me to correct the query? :-D

I've figured what's wrong with my query :-D

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)

 

[Lurker]

glad you made it, keep your good work