PostGIS: Use the !ZOOM! token before the where-clause of the query.

[brianbancroft]

Hi there, and thanks for putting this together. I hope the march towards 0.7 continues steadily.

Requested Behavior

I seek to be able to use the !ZOOM! token before the WHERE-clause in a TOML-fed query for a given layer so that I can leverage the postGIS ST_Simplify function with the intended result of simplifying polygons at lower zoom levels.

Use Cases

Here's where I'd employ it:

[[providers.layers]]
  name = "pop_age_15_to_19_2016_ct"
  geometry_fieldname = "wkb_geometry"
  id_fieldname = "id"
  # TODO: Same issue: Get the geometry dumbing down using the exponential function
  sql = """
  SELECT
    ST_AsBinary(ST_Simplify(geo.wkb_geometry, (!ZOOM! * "mathy-like function", true))
      AS wkb_geometry,
    geo.ogc_fid
      AS id,
    data.total
      AS total
  FROM canada_ct_2016
    AS geo
  INNER JOIN pop_age_15_to_19_2016_ct
    AS data
    ON geo.ctuid = data.geo_id
  WHERE
    geo.wkb_geometry && !BBOX!
  GROUP BY
    wkb_geometry,
    id,
    total
  """

Current work arounds to accomplish current behaviour.

As a user, I have no workarounds at this time. For boundary polygons (Canadian Census Tracts) at zoom-level 9, the size of tiles being generate are upwards of 1MB. At zoom-level 14, they're at a manageable 20-40kb.

Thanks. If this functionality exists, I would gladly contribute to tegola-docs so that others in a similar position can carry out out.

Edit

The geographic data I used for this query can be found here

[ARolek]

@brianbancroft Thanks for the report.

Tegola applies simplification during geometry processing but sometimes it's better to control the simplification like you're trying to do. A possible work around is to create another table which holds the simplified geometries. This strategy is used in our OSM deployments. You would then want to turn off simplification for the given layer so tegola does not try to simplify it again. You would do this via the dont_simplify config for the map layer.

[brianbancroft]

Thanks for the workaround idea!

[olt]

Can you try with #466 and an explicit geometry_type?

[pnorman]

The errors when doing anything math-like with !ZOOM! are a duplicate of #343

[brianbancroft]

@pnorman is correct. This is a duplicate. Closing :)