Jump to content

Help PostgreSQL: Case Statement (ST_Intersects)


Recommended Posts

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

Link to comment
Share on other sites

  • 4 weeks later...
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)

 

  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.

Disable-Adblock.png

 

If you enjoy our contents, support us by Disable ads Blocker or add GIS-area to your ads blocker whitelist