utilery.yml

buffer: 16
srid: 3857
layers:
- name: buildings
  queries:
    - minzoom: 12
      maxzoom: 13
      sql: |-
        SELECT
          name, type, 0 as osm_id, ST_Union(geometry) AS way
        FROM
          osm_buildings_gen0
        WHERE
          geometry && !bbox!
        GROUP BY
          name, type
        ORDER BY
          sum(area) DESC
    - minzoom: 14
      maxzoom: 16
      sql: |-
        SELECT
          name, type, 0 as osm_id, ST_SimplifyPreserveTopology(ST_Union(geometry),!pixel_width!/2) AS way
        FROM
          osm_buildings
        WHERE
          geometry && !bbox!
        GROUP BY
          name, type
        ORDER BY
          sum(area) DESC
    - minzoom: 17
      maxzoom: 22
      sql: |-
        SELECT
          name, type, osm_id, geometry AS way
        FROM
          osm_buildings
        ORDER BY
          area DESC
- name: places
  buffer: 128
  queries:
    - maxzoom: 4
      sql: |-
        SELECT
          name, type, osm_id, geometry AS way
        FROM
          osm_places
        WHERE
          name IS NOT NULL
          AND type IN ('continent','ocean','country')
        ORDER BY
          z_order ASC
    - minzoom: 5
      maxzoom: 13
      sql: |-
        SELECT
          name, type, osm_id, geometry AS way
        FROM
          osm_places
        WHERE
          name IS NOT NULL
          AND type IN ('city','county','province','island','town','lake','village')
        ORDER BY
          z_order ASC
    - minzoom: 14
      maxzoom: 22
      sql: |-
        SELECT
          name, type, name, osm_id, geometry AS way
        FROM
          osm_places
        WHERE
          name IS NOT NULL
          AND type IN ('city','county','province','island','town','neighbourhood','suburb','locality','lake','village','hamlet','isolated_dwelling')
        ORDER BY
          z_order ASC
- name: railways
  clip: true
  buffer: 8
  queries:
    - maxzoom: 8
      sql: |-
        SELECT
          ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(geometry)),!pixel_width!) AS way,
          type, name, tunnel, bridge, oneway, ref, service, highspeed, class
        FROM
          osm_railways_gen0
        WHERE
          geometry && !bbox! and type='rail' and coalesce(service,'') in ('','main')
        GROUP BY
          z_order, type, name, tunnel, bridge, oneway, ref, service, highspeed, class
         ORDER BY
          z_order, highspeed DESC
    - minzoom: 9
      maxzoom: 11
      sql: |-
        SELECT
          ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(geometry)),!pixel_width!) AS way,
          type, name, tunnel, bridge, oneway, ref, service, highspeed, class
        FROM
          osm_railways_gen1
        WHERE
          geometry && !bbox! and type in ('rail','subway') and coalesce(service,'') in ('','main')
        GROUP BY
          z_order, type, name, tunnel, bridge, oneway, ref, service, highspeed, class
        ORDER BY
          z_order, highspeed DESC, coalesce(service,'') DESC
    - minzoom: 12
      maxzoom: 20
      sql: |-
        SELECT
          geometry AS way,
          type, name, tunnel, bridge, oneway, ref, service, highspeed, class
        FROM
          osm_railways
        ORDER BY
          z_order, highspeed DESC, coalesce(service,'') DESC
- name: landuse_auto
  clip: true
  buffer: 8
  queries:
    - minzoom: 11
      maxzoom: 15
      sql: |-
        SELECT
          ST_SimplifyPreserveTopology(ST_Union(ST_buffer(geometry,20,'join=mitre')),!pixel_width!/2) AS way, 'railway' as type, ST_Area(ST_Union(ST_buffer(geometry,20))) as area
        FROM
          osm_railways
        WHERE
          service in ('yard','spur','siding') and geometry && !bbox!
        UNION
        SELECT
          ST_SimplifyPreserveTopology(ST_Union(ST_buffer(b.geometry,50,'join=mitre mitre_limit=1')),!pixel_width!/2) AS way, 'buildings' as type, ST_Area(ST_Union(ST_buffer(b.geometry,50))) as area
        FROM
          osm_buildings b
        LEFT JOIN osm_landusages l ON (ST_Intersects(l.geometry, b.geometry) and l.type in ('residential','retail','industrial','farmyard'))
        WHERE
          b.geometry && !bbox! and b.area>50 and l.type is null
- name: roads
  clip: true
  queries:
    - maxzoom: 9
      sql: |-
        SELECT
          geometry AS way, type, name, tunnel, bridge,
          (CASE WHEN type IN ('motorway', 'motorway_link') THEN 'highway'
                WHEN type IN ('trunk', 'trunk_link', 'secondary', 'primary') THEN 'major_road'
                ELSE 'minor_road' END) AS category,
          (CASE WHEN type LIKE '%_link' THEN 1
                ELSE 0 END) AS link
        FROM
          osm_roads_gen0
        ORDER BY
          z_order ASC
    - minzoom: 10
      maxzoom: 12
      sql: |-
        SELECT
          geometry AS way, type, name, tunnel, bridge,
          (CASE WHEN type IN ('motorway', 'motorway_link') THEN 'highway'
                WHEN type IN ('trunk', 'trunk_link', 'secondary', 'primary') THEN 'major_road'
                ELSE 'minor_road' END) AS category,
          (CASE WHEN type LIKE '%_link' THEN 1
                ELSE 0 END) AS link
        FROM
          osm_roads_gen1
        ORDER BY
          z_order ASC
    - minzoom: 13
      maxzoom: 20
      sql: |-
        SELECT
          geometry AS way, type, name, tunnel, bridge, osm_id,
          (CASE WHEN type IN ('motorway', 'motorway_link') THEN 'highway'
                WHEN type IN ('trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', 'tertiary', 'tertiary_link') THEN 'major_road'
                WHEN type IN ('footpath', 'track', 'footway', 'steps', 'pedestrian', 'path', 'cycleway') THEN 'path'
                ELSE 'minor_road' END) AS category,
          (CASE WHEN type LIKE '%_link' THEN 1
                ELSE 0 END) AS link
        FROM
          osm_roads
        ORDER BY
          z_order ASC
- name: road_labels
  clip: true
  buffer: -2
  queries:
    - maxzoom: 9
      sql: |-
        SELECT
          ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(geometry)),!pixel_width!) AS way,
          type, name, ref, tunnel, bridge,
          (CASE WHEN type IN ('motorway', 'motorway_link') THEN 'highway'
                WHEN type IN ('trunk', 'trunk_link', 'secondary', 'primary') THEN 'major_road'
                ELSE 'minor_road' END) AS category
        FROM
          osm_roads_gen0
        WHERE
          geometry && !bbox! AND name != ''
        GROUP BY
          type, name, ref, tunnel, bridge,category,z_order
        ORDER BY
          z_order DESC
    - minzoom: 10
      maxzoom: 12
      sql: |-
        SELECT
          ST_LineMerge(ST_Collect(geometry)) AS way,
          type, name, ref, tunnel, bridge,
          (CASE WHEN type IN ('motorway', 'motorway_link') THEN 'highway'
                WHEN type IN ('trunk', 'trunk_link', 'secondary', 'primary') THEN 'major_road'
                ELSE 'minor_road' END) AS category
        FROM
          osm_roads_gen1
        WHERE
          geometry && !bbox! AND name != ''
        GROUP BY
          type, name, ref, tunnel, bridge,category,z_order
        ORDER BY
          z_order ASC, st_length(ST_LineMerge(ST_Collect(geometry))) ASC
    - minzoom: 13
      maxzoom: 14
      sql: |-
        SELECT
          ST_LineMerge(ST_Collect(geometry)) AS way,
          type, name, ref, tunnel, bridge,
          (CASE WHEN type IN ('motorway', 'motorway_link') THEN 'highway'
                WHEN type IN ('trunk', 'trunk_link', 'secondary', 'primary') THEN 'major_road'
                ELSE 'minor_road' END) AS category
        FROM
          osm_roads_gen1
        WHERE
          geometry && !bbox! AND name != ''
        GROUP BY
          type, name, ref, tunnel, bridge,category,z_order
        ORDER BY
          z_order ASC, st_length(ST_LineMerge(ST_Collect(geometry))) ASC
    - minzoom: 15
      maxzoom: 20
      sql: |-
        SELECT
          ST_LineMerge(ST_Collect(geometry)) AS way,
          type, name, ref, tunnel, bridge, osm_id,
          (CASE WHEN type IN ('motorway', 'motorway_link') THEN 'highway'
                WHEN type IN ('trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', 'tertiary', 'tertiary_link') THEN 'major_road'
                WHEN type IN ('footpath', 'track', 'footway', 'steps', 'pedestrian', 'path', 'cycleway') THEN 'path'
                ELSE 'minor_road' END) AS category
        FROM
          osm_roads
        WHERE
          geometry && !bbox! AND name||ref != ''
        GROUP BY
          type, name, ref, tunnel, bridge, category, osm_id, z_order
        ORDER BY
          z_order ASC
- name: landuse
  clip: true
  buffer: 8
  queries:
    - maxzoom: 9
      sql: |-
        SELECT
          ST_SimplifyPreserveTopology(geometry,!pixel_width!) AS way, type, name
        FROM
          osm_landusages_gen0
        WHERE
          geometry && !bbox! and area>(!pixel_width!*!pixel_width!*4)
        ORDER BY
          z_order DESC, area DESC
    - minzoom: 10
      maxzoom: 12
      sql: |-
        SELECT
          ST_SimplifyPreserveTopology(geometry,!pixel_width!) AS way, type, name
        FROM
          osm_landusages_gen1
        WHERE
          geometry && !bbox! and area>(!pixel_width!*!pixel_width!*4)
        ORDER BY
          z_order DESC, area DESC
    - minzoom: 13
      maxzoom: 15
      sql: |-
        SELECT
          ST_SimplifyPreserveTopology(geometry,!pixel_width!) AS way, type, name
        FROM
          osm_landusages
        WHERE
          geometry && !bbox! and area>(!pixel_width!*!pixel_width!*4)
        ORDER BY
          z_order DESC, area DESC
    - minzoom: 16
      maxzoom: 20
      sql: |-
        SELECT
          geometry AS way, type, name
        FROM
          osm_landusages
        ORDER BY
          z_order DESC, area DESC
- name: landuse_labels
  clip: false
  buffer: 128
  queries:
    - maxzoom: 9
      sql: |-
        SELECT
          ST_PointOnSurface(ST_Union(geometry)) AS way,
          round(log(sum(area))::numeric,0)::int as area_log,
          length(name) as name_length,
          type, name
        FROM
          osm_landusages_gen0
        WHERE
          geometry && !bbox! and name !='' and area>(100*!pixel_width!*!pixel_width!)
        GROUP BY
          type,name,z_order
        ORDER BY
          z_order DESC, sum(area) DESC
    - minzoom: 10
      maxzoom: 12
      sql: |-
        SELECT
          ST_PointOnSurface(ST_Union(geometry)) AS way,
          round(log(sum(area))::numeric,0)::int as area_log,
          length(name) as name_length,
          type, name
        FROM
          osm_landusages_gen1
        WHERE
          geometry && !bbox! and name !='' and area>(100*!pixel_width!*!pixel_width!)
        GROUP BY
          type,name,z_order
        ORDER BY
          z_order DESC, sum(area) DESC
    - minzoom: 13
      maxzoom: 20
      sql: |-
        SELECT
          ST_PointOnSurface(ST_Union(geometry)) AS way,
          round(log(sum(area))::numeric,0)::int as area_log,
          length(name) as name_length,
          type, name
        FROM
          (SELECT name,type,area,z_order,geometry FROM osm_landusages UNION SELECT name,type,area,0 as z_order,geometry FROM osm_waterareas) as areas
        WHERE
          geometry && !bbox! and name !='' and area>(100*!pixel_width!*!pixel_width!)
        GROUP BY
          type,name,z_order
        ORDER BY
          z_order DESC, sum(area) DESC
- name: waterarea
  clip: true
  buffer: 8
  queries:
    - maxzoom: 9
      sql: |-
        SELECT
          ST_SimplifyPreserveTopology(geometry,!pixel_width!) AS way, type, name
        FROM
          osm_waterareas_gen0
        WHERE
          geometry && !bbox! and area>(!pixel_width!*!pixel_width!*4)
        ORDER BY
          area DESC
    - minzoom: 10
      maxzoom: 12
      sql: |-
        SELECT
          geometry AS way, type, name
        FROM
          osm_waterareas_gen1
        WHERE
          area > (!pixel_width!*!pixel_width!*4)
        ORDER BY
          area DESC
    - minzoom: 13
      maxzoom: 20
      sql: |-
        SELECT
          geometry AS way, type, name
        FROM
          osm_waterareas
        WHERE
          area > (!pixel_width!*!pixel_width!*4)
        ORDER BY
          area DESC
- name: waterway
  clip: true
  buffer: 8
  queries:
    - maxzoom: 9
      sql: |-
        SELECT
          ST_SimplifyPreserveTopology(geometry,!pixel_width!) AS way, type, name, tunnel, bridge
        FROM
          osm_waterways_gen0
        WHERE
          geometry && !bbox! and type in ('river','canal')
    - minzoom: 10
      maxzoom: 11
      sql: |-
        SELECT
          geometry AS way, type, name, tunnel, bridge
        FROM
          osm_waterways_gen1
        WHERE
           type in ('river','canal')
    - minzoom: 12
      maxzoom: 20
      sql: |-
        SELECT
          geometry AS way, type, name, tunnel, bridge
        FROM
          osm_waterways