Reyalino Posted May 4, 2017 Report Share Posted May 4, 2017 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 Quote Link to comment Share on other sites More sharing options...
Reyalino Posted May 30, 2017 Author Report Share Posted May 30, 2017 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) 1 Quote Link to comment Share on other sites More sharing options...
Lurker Posted May 30, 2017 Report Share Posted May 30, 2017 glad you made it, keep your good work 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.