Jump to content

Our forum made possible by displaying online advertisements to our visitors.

Please consider supporting us by disabling your ads blocker.

  • Announcements

    • EmperoR

      Dear Guests   12/24/2016

      Welcome to the GIS-AREA. like most online communities you must register to view or post in our community, but don't worry this is a simple free process that requires minimal information for you to signup. Be a part of GIS-AREA by signing in or creating an account here. Once you have created your account and have introduced yourself to our community, you can start new topics, reply to others, subscribe interesting threads, get your customize profiles, make news friends, and others nice stuff. To make your stay as pleasant and constructive as possible, please don't forget to read through our pinned forum rules and board guidelines in each section before you do anything else.
Sign in to follow this  
Reyalino

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

×

Important Information

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