7

I am trying to write the following SQL command using case clause.

with x as
(select the_geom as geom from table1
where st_dwithin(the_geom, st_setsrid(st_makepoint(28.959495,41.019913), 4326), 0.00012) limit 1)
select case count(geom)
when 0
    then st_setsrid(st_makepoint(28.959495,41.019913), 4326)
when 1
    then st_closestpoint(x.geom, st_setsrid(st_makepoint(28.959495,41.019913), 4326))       
end as the_geom
from x group by geom

As can be seen under the with clause, there could be either one row or no row in the record x. Code is working fine where there is one row in x but when the x is null (means count(geom) = 0) then the when 0 operation is not getting executed. I am getting the following output. output_1

Plus, after modifying my SQL code a bit like the following (only the when 2 is changed!)

with x as
(select the_geom as geom from table1
where st_dwithin(the_geom, st_setsrid(st_makepoint(28.959495,41.019913), 4326), 0.00012) limit 1)
select case count(geom)
when 0
then st_setsrid(st_makepoint(28.959495,41.019913), 4326)
when 2
then st_closestpoint(x.geom, st_setsrid(st_makepoint(28.959495,41.019913), 4326))       
end as the_geom
from x group by geom

The output is like the following screenshot when the count(geom) = 1. output_2

Queries:

  1. How to run the when 0 condition when the record x is empty?
  2. What is the difference between the two SQL outputs (as shown in the screenshots)?

closed as too broad by PolyGeo Oct 13 at 1:15

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

6

You can simplify the whole thing by using coalesce, which selects the first non-null item in a list, in this case, either the nearest point to you search point, (28.959495,41.019913), or the point itself.

with input_geom (geom) as 
   (select st_setsrid(st_makepoint(28.959495,41.019913), 4326)) 
select coalesce(
   (select st_closestpoint(pts.the_geom, ig.geom) 
      where st_dwithin(ig.geom, pts.the_geom, 0.00012))
    ,ig.geom) as geom 
 from table1 pts, input_geom ig limit 1;

I have turned your query on its head, by putting the search point in the initial with statement, as this is both a search term or a return value, so it gets referred to in both parts of the coalesce statement.

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