2

I have been had a task: to allow a single WFS request to return two different responses depending on zoom: return usual

SELECT * FROM table

if zoom level >n, and return

SELECT ST_SimplifyPreserveTopology(geom, x) FROM table

if zoom level <=n.

I tried to do it by this definition like in advice from there:

SELECT wkb_geometry, ogc_fid, key
  (CASE 
  WHEN %zoom_lvl% > CAST ('8' as smallint) 
    THEN wkb_geometry=ST_SimplifyPreserveTopology(wkb_geometry, 49::double precision)
  WHEN %zoom_lvl% <= CAST ('8' as smallint) 
    THEN wkb_geometry = wkb_geometry
END)

from input.admin_centers_and_districts

But when I try to do it I face this

ERROR: function key(boolean) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 46

enter image description here

What is wrong? cast('8'smallint) is useless.

  • 1
    That SQL snipped you posted is a little messy (no offense); there's no need to cast a string '8' to a number, but most of all, SQL is not comparable to procedural languages, e.g. you cannot simply assign values to variables (e.g. wkb_geometry = ST_Simplify...). That specific error results from a missing , between key and the following (unnecessarily bracket enclosed) CASE statement; without the comma, PostgreSQL treats that part as a function invocation (SELECT ..., key(CASE ... END) ...)! – geozelot Dec 18 '19 at 10:22
4

PostGIS is saying it can't find a function called key that takes a boolean operator.

SELECT wkb_geometry, ogc_fid, key
  (CASE 
  WHEN %zoom_lvl% > CAST ('8' as smallint) 
    THEN wkb_geometry=ST_SimplifyPreserveTopology(wkb_geometry, 49::double precision)
  WHEN %zoom_lvl% <= CAST ('8' as smallint) 
    THEN wkb_geometry = wkb_geometry
END)

from input.admin_centers_and_districts

This is because this statement evaluates to be something like:

SELECT wkb_geometry, ogc_fid, key
  true
from input.admin_centers_and_districts

Which is nonsense. SQL might look like a programming language but it isn't really one. So you want something like

SELECT ogc_fid, key,
  (CASE 
  WHEN %zoom_lvl% > 8) 
    THEN ST_SimplifyPreserveTopology(wkb_geometry, 49::double precision)
  else 
    wkb_geometry
END) as wkb_geometry

from input.admin_centers_and_districts
|improve this answer|||||
2

In SQL select, you're supposed to describre the result you want, not what to do. Here you put a lot of assignement =, in SQL you use it as comparison (equivalent to == in many languages) except in a few cases like update. If you want to rename the column of your result, you write as new_name at the end of the column description. I suppose that's what you wanted to do, because the way you wrote it you have key(CASE...END) which is interpreted as a function called with a parameter boolean because you compare something in your CASE with = (not found so -> error). Here is an exemple, simplified, and without = inside your CASE:

SELECT 
    wkb_geometry, 
    ogc_fid, 
    (
        CASE 
            WHEN %zoom_lvl% > 8 THEN ST_SimplifyPreserveTopology(wkb_geometry, 49)
            ELSE wkb_geometry
        END
    ) as key
FROM input.admin_centers_and_districts
|improve this answer|||||

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.