3

I am using the following PostGIS nearest neighbor query to determine how close a point is to an area (polygon) that has been flooded and it's working well:

SELECT 
     ST_AsText(ST_SetSRID(ST_MakePoint(531673.0, 180848.2),27700)),
     "flooding".gid, 
      ST_Distance(ST_SetSRID(ST_MakePoint(531673.0, 180848.2),27700),
         "flooding".geom) AS distance
FROM
  "flooding"
WHERE "flooding".geom && ST_Expand(ST_SetSRID(ST_MakePoint(531673.0, 180848.2),27700), 400)
ORDER BY ST_Distance(ST_SetSRID(ST_MakePoint(531673.0, 180848.2),27700), "flooding".geom) ASC
LIMIT 1;

However I would like to categorize the distance as follows and then output the result:

CASE
  WHEN distance > 300 THEN 'OUTSIDE'
  WHEN distance > 50 AND distance  <= 300 THEN 'CLOSE'
  WHEN distance < 50 THEN 'INSIDE'
END as flood_result

I know I need to use the "case when else" format to accomplish the above conditional statement, however I do not know how to do this with regard to ST_Expand.

  • 2
    I would use ST_DWithin rather than ST_Expand, after creating the point once in an initial CTE, eg, WITH point (geom) AS (SELECT ST_SetSRID(ST_MakePoint(531673.0, 180848.2),27700), as it is cleaner, and you can then refer to it in later queries rather than constantly recreating it. As far as the CASE is concerned, you need to put CASE WHEN distance > 300m THEN 'OUTSIDE'. Note you need single quotes, not double. – John Powell Apr 30 '18 at 14:42
  • @JohnPowellakaBarça I added WITH point_loc AS ( SELECT ST_SetSRID(ST_MakePoint(531673.0, 180848.2),27700) ) however when I used point_loc later, I get a syntax error. I noticed that point_loc is no longer a geometry but text, is that the problem? – Trashmonk Apr 30 '18 at 16:06
  • @JohnPowellakaBarça, also do you know how to save the distance from the WHERE statement so I can use it in the CASE.. END section? – Trashmonk Apr 30 '18 at 16:12
  • What do you see when you run: WITH point_loc (geom) AS ( SELECT ST_SetSRID(ST_MakePoint(531673.0, 180848.2),27700) ) SELECT st_geometrytype(geom) FROM point_loc; – John Powell Apr 30 '18 at 16:12
  • @JohnPowellakaBarça, I ran the above command and got: st_geometrytype (text): ST_Point. So it's text. I so ran the code below and got ERROR: syntax error at or near "SELECT" at the last SELECT. – Trashmonk Apr 30 '18 at 19:15
3

You can make this query cleaner by putting you point in an initial CTE, and then using ST_DWithin, rather than recreating a point form text, which will mean the spatial index isn't using on floods, if you have one, which you should for any non-trivial table.

Also, you can make the case statement a bit simpler, as you don't need between logic and you can use ELSE at the end.

WITH 
   point_loc (geom) AS ( 
     SELECT ST_SetSRID(ST_MakePoint(531673.0, 180848.2),27700) ),
   distances (gid, distance) AS (
     SELECT  
        fl.gid,
        ST_Distance(fl.geom, p.geom) AS distance
      FROM
         point_loc p, flooding fl
      WHERE ST_DWithin(p.geom, fl.geom, 400))
   SELECT 
      gid,
      CASE WHEN distance > 300 THEN 'outside'
           WHEN distance > 50 THEN 'CLOSE'
           ELSE 'INSIDE'
       END as flood_result
      FROM distances;

This is untested, but I believe close to what you are looking for.

0

@JohnPowellakaBarça, your solution worked, there was just a missing closing parenthesis:

WITH 
  point_loc (geom) AS ( 
    SELECT ST_SetSRID(ST_MakePoint(531673.0, 180848.2),27700) ),
  distances (gid, distance) AS (
    SELECT  
      fl.gid,
      ST_Distance(fl.geom, p.geom) AS distance
    FROM
      point_loc p, flooding fl
    WHERE ST_DWithin(p.geom, fl.geom, 400) )
  SELECT 
    gid,
    CASE WHEN distance > 300 THEN 'OUTSIDE'
         WHEN distance > 50 AND distance <= 300 THEN 'CLOSE'
         ELSE 'INSIDE'
    END as flood_result
  FROM distances;
  • Yes, I know that, I put it in the comments that I had corrected it. – John Powell Apr 30 '18 at 21:09
  • Sorry missed that comment. – Trashmonk Apr 30 '18 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.