Merging multiple layers

[pnorman]

c.f. #87 for more restricted case

I have a specification for a vector tile schema which calls for

• zoom 0 to 4, natural earth water_areas
• zoom 5 to 9, osm simplified_ocean_polygons
• zoom 10+, osm ocean_polygons
• zoom 5+ OSM waterway=riverbank or landuse=reservoir or natural=water

Right now this would require SQL like

SELECT
    ST_AsBinary(way) AS way,
    gid,
    water
  FROM (
  SELECT
      way,
      0::bigint AS gid,
      'ocean' AS water
    FROM ne_ocean
    WHERE !ZOOM! < 5
  UNION ALL
  SELECT
      way,
      0::bigint AS gid,
      'lake' AS water
    FROM ne_lake
    WHERE !ZOOM! < 5
  UNION ALL
  SELECT
      way,
      0::bigint AS gid,
      'ocean' AS water
    FROM simplified_ocean_polygons
    WHERE !ZOOM! < 10 !ZOOM! >= 5
  UNION ALL
  SELECT
      way,
      0::bigint AS gid,
      'ocean' AS water
    FROM ocean_polygons
    WHERE !ZOOM! < 10 !ZOOM! >= 5
  UNION ALL
  SELECT
      way,
      0::bigint AS gid,
      COALESCE(
        tags->'water',
        CASE WHEN landuse IN ('reservoir', 'basin') THEN landuse END,
        CASE WHEN waterway IN ('dock', 'riverbank') THEN waterway END) AS water
    FROM planet_osm_polygon
    WHERE !ZOOM! < 10 !ZOOM! >= 5
      AND (waterway IN ('dock', 'riverbank')
        OR landuse IN ('reservoir', 'basin')
        OR "natural" = 'water'))
    ) waters
  WHERE way && !BBOX!;

Although query performance is not an issue, writing SQL like this can become unwieldy as the parts of the query become more complex, there are more fields to match across the UNION ALLs, and the number of parts increases.

It would be useful to be able to write each query independently, then merge them with tegola.

[ARolek]

@pnorman i think this is a dupe of #94. From what I can tell, you're looking for a way to keep the layer name that is encoded in the tile but query from different tables from either the same or different providers.

This is something we're working on and is a fairly requested feature. I'm going to bump it up in priority due to demand. Please check #94 (I know you commented on it earlier today) to make sure we're addressing what you're looking for here.

[pnorman]

This issue is looking at where there are two sources for data at the same zoom, #94 doesn't cover that case.

[ARolek]

@pnorman and with the same layer name?

[pnorman]

Yes - all the same layer name in this case

[ARolek]

@pnorman I think you could simplify this query using the map layer_name config. For more information: https://tegola.io/documentation/configuration/#map-layers

[pnorman]

@pnorman I think you could simplify this query using the map layer_name config. For more information: https://tegola.io/documentation/configuration/#map-layers

It can do half of it - it means I can split up things by zoom, but not by source. For the latter, I tried this configuration

[[providers]]
name = "osm2pgsql"
type = "postgis"
host = "/var/run/postgresql" # Default for Debian-based distributions
port = 5432                     # postgis database port
database = "osmcarto_bc"        # postgis database name
user = ""
password = ""

  [[providers.layers]]
  name = "ne_ocean"
  geometry_fieldname = "way"
  geometry_type = "Polygon"
  sql = """
  SELECT
      ST_AsBinary(way) AS way
    FROM ne_ocean
    WHERE way && !BBOX!
  """

  [[providers.layers]]
  name = "ne_lake"
  geometry_fieldname = "way"
  geometry_type = "Polygon"
  sql = """
  SELECT
      ST_AsBinary(way) AS way
    FROM ne_lake
    WHERE way && !BBOX!
  """

[[maps]]
name = "bolder"

[[maps.layers]]
name = "water"
provider_layer = "osm2pgsql.ne_ocean"
min_zoom = 0
max_zoom = 5

[[maps.layers]]
name = "water"
provider_layer = "osm2pgsql.ne_lake"
min_zoom = 0
max_zoom = 5

and get the error

config: overlapping zooms for layer (osm2pgsql.ne_ocean) and layer (osm2pgsql.ne_lake)

[ARolek]

Yeah that's still not supported as it brings up a lot of implementation questions like how to handle layer and feature merging / conflict handling.

[pnorman]

Yeah that's still not supported as it brings up a lot of implementation questions like how to handle layer and feature merging / conflict handling.

I was thinking iterating through the layers writing out objects, but you need to know all the keys and values before you start to write. I'd go for the equivalent of UNION ALL, and I don't see any merging or conflict handling of features need to be done.

[yaras-phoenix]

Yeah that's still not supported as it brings up a lot of implementation questions like how to handle layer and feature merging / conflict handling.

Do I get it right? "Grouping feature" is created for rendering multiple provider layers on different zoom levels, so combining, say, two provider layers on a single zoom level is not possible?

[ARolek]

@yaras-phoenix that's correct. Tegola does not support merging data for the same layer at the same zoom from different providers.