Sample TOML file exposing OpenMapTiles vector scema layers

[s0m30n3]

I'm currently creating a website and I'd like to integrate a slippy map as it seems to be called. For several reasons, I want to host my own vector tile server. I tried/tested some existing solutions (tileserver/t-rex, kartotherian, OpenMapTiles, mapbox/tilelive, geoserver, tilezen/tileserver) and tegola meets my criteria (light, fast, a minimum of documentation, simple to install, to configure, to deploy using docker...).

I'm still new to all this GIS stuff but I think I now understand the whole process: we get an OpenStreetMap data extract, populate a PostreSQL database with this data + add a spatial extension and indexes, create a tile server configuration file to provide tile layers (= SQL queries), start the tile server and on client side, use a JS library (Mapbox GL in my case) which uses a 'style' file which describe what are the tile sources and how to style (as CSS do for HTML content) them.

Except a typo here and there, your documentation is clear and I can successfully display a map. I can use the tegola generated "debugger" style but my main goal is to use something more user-friendly. For some time, I though I'd have to build my own style at hand using maputnik editor and layers tegola provides, adding custom layers as needed.

I stumbled upon maputnik/osm-liberty github project and that's exactly what I need, at least to start with. Unfortunately, they use openmaptiles as their vector source. I changed that to my own tegola URL but I got several errors saying something like (for instance) park source layer was used but not found. It seems openmaptiles has designed their own vector tile schema ("park" is one of the styled layer but not exposed by tegola).

Would it be possible to add a sample TOML file exposing these layers ? All projects use OpenStreetMap and NaturalEarth data + imposm3 or osm2psql to transfer them to PostreSQL database server so I guess table names/structures are the same and that SQL queries can be imported from a project to another.

Thanks!

[s0m30n3]

Just as a follow-up, here is my understanding: imposm3 use a mapping file to know in which table place the .osm.pbf data. In this repository, this is the imposm3.json file that is used but we can change that.

In fact, we can generate this mapping by "generate-imposm3" which is a tool from openmaptiles-tools repository:

• git clone github.com/openmaptiles/openmaptiles.git
• docker run -v ./openmaptiles:/tileset openmaptiles/openmaptiles-tools generate-imposm3 /tileset/openmaptiles.yaml > imposm3-openmaptiles.yaml

Then we can use imposm import -connection [...] -mapping imposm3-openmaptiles.yaml -read [...].
Next step is to create a TOML file that expose these layers so that we can style them client-side.

[ARolek]

@s0m30n3 thanks for bringing up this discussion. I have wanted a tegola config that worked with OpenMapTiles spec for awhile now so thanks for taking on the task. It might make sense to spin up another repo (i.e. tegola-open-map-tiles) with instructions and the final config file. If I opened it up would send in a PR with the steps you used to stand up tegola against the OMT spec?

Regarding styles, we have a couple that you can use stored in the tegola-web-demo repo. You can also see the styles at the demo page

Except a typo here and there, your documentation is clear and I can successfully display a map.

Please send in a PR with any typos you find! I consider those bugs and we're always looking to improve documentation.

It's great to see how fast you have grasped standing up your own basemap. Feel free to add additional questions to this thread if you get stuck.

[s0m30n3]

I spent several days for this issue but it still doesn't work.

Before trying liberty style, I tried the generated one but it doesn't show anything.
I don't know if the issue is:

• SQL requests in the TOML file,
• wrong SRID,
• wrong layer geometry type,
• other.

Find_SRID('public', 'water_z0', 'geometry') gives me 3857 but generated (openmaptiles) data.yml says it's 900913.

I've carefully prepared a bash script to create and populate the database, following what does openmaptiles. If you have a bit of time, you can test it using my gist. There are many comments.
https://gist.github.com/s0m30n3/dafcb2235c10fd573c8d23fe36046ae1

Feel free to ask any question.
Thanks

[ARolek]

@s0m30n3 900913 == 3857. 900913 is "GOOGLE" translated to numbers and the projection codes are nice and confusing. Take a look at [this wikipedia article](https://en.wikipedia.org/wiki/Web_Mercator_projection#Identifiers on Web Mercator identifiers.

Are you seeing empty tiles returned by tegola or any error logs being spit out to the console?

[s0m30n3]

Thanks for the information. I thought reference was different hence needing conversion.

I did some tests but my demo page stays white:

• I see the server is sending some data via Firefox Network monitor so it's working
• nothing on console except if I zoom out too many times (zoom 6 and less, "unable to convert geometry field into bytes")
• I disabled caching to be sure I'm receiving fresh data
• I only kept water layer
• I added ?debug=true in each "tiles" pbf URL
• I tried TEGOLA_SQL_DEBUG=EXECUTE_SQL to see actual SQL requests and replay them using psql but it only shows a blank page with "More" at the bottom. I guess the geometry is binary so not printable. I did some SELECT COUNT(*) to see if there were lines and no, except if I change the bounding box.

Maybe !BBOX! is not correctly translated to what expects layer_XxX (layer_water in my case).

I can upload a 1.4GB database dump to my Google Drive if you want to test. You'll need to compile osml10n though, as described in the install.sh script (see my gist).

[ARolek]

@s0m30n3 yeah I'm game to give it a try. Want to upload the dump and I can take a quick look?

[s0m30n3]

You can get it at https://drive.google.com/open?id=1N6sZrW9uYRZtB-CXpI51ZQWqwv7sodUW.
It's a zip file with a README and docker + docker-compose files to play with.

[s0m30n3]

I gave it another shot using a bigger OSM file (France now, Mayotte previously) and I discovered an issue with the for loop I used to import PBF files. I fixed that and used cython to speed up Wikipedia importing step but it didn't change anything. The "boundary" layer is kind of working now (changed polygon to linestring) so at least I can see continents, France departments, etc The database dump is now 1.7GB which is still below the PBF file size. Except tweaking the TOML file, I don't see what I can do to make it working. I will browse the openmaptiles repository (again) to see if I missed a step.
In the mean time, I uploaded the new database dump : https://drive.google.com/file/d/1x6E8tK1zVNsaBIyI-9Wy2xFT40iPk2qp

[skylarmt]

Did you ever get Tegola working with the OpenMapTiles schema?

[ARolek]

@skylarmt I run tegola against an OpenMapTiles schema deployment. I did have to do some workarounds as I use AWS RDS and OMT relies on a language plugin that's not available on AWS RDS.

I just returned from a trip so I'm still getting caught up, but here's a quick example config that I'm using. I also have some detailed instructions for myself that I could add to this repo, but hopefully, this gets you started. Note, I didn't bring in every OMT layer as it's unnecessary for my application.

# open street map
[[providers]]
name = "omt" # OpenMapTiles schema for Open Street Map and Natural Earth data
type = "mvt_postgis"
host = "${OMT_DB_HOST}"
port = 5432
database = "${OMT_DB_NAME}"
user = "${OMT_DB_USER}"
password = "${OMT_DB_PW}"
max_connections = 3

	[[providers.layers]]
	name = "buildings"
	geometry_type = "polygon"
	geometry_fieldname = "geom"
	id_fieldname = "osm_id"
	sql = "SELECT ST_AsMVTGeom(geometry, !BBOX!) AS geom, osm_id, render_height, render_min_height, colour, hide_3d FROM layer_building(!BBOX!, !ZOOM!)"

	[[providers.layers]]
	name = "boundary"
	geometry_type = "linestring"
	geometry_fieldname = "geom"
	id_fieldname = "gid"
	sql = "SELECT ST_AsMVTGeom(geometry, !BBOX!) AS geom, 1 AS gid, admin_level, disputed, disputed_name, claimed_by, maritime FROM layer_boundary(!BBOX!, !ZOOM!)"

	[[providers.layers]]
	name = "transportation"
	geometry_type = "linestring"
	geometry_fieldname = "geom"
	id_fieldname = "osm_id"
	sql = "SELECT ST_AsMVTGeom(geometry, !BBOX!) AS geom, osm_id, class, subclass, ramp, oneway, brunnel, service, layer, level, indoor, bicycle, foot, horse, mtb_scale, surface FROM layer_transportation(!BBOX!, !ZOOM!)"

	[[providers.layers]]
	name = "transportation_name"
	geometry_type = "linestring"
	geometry_fieldname = "geom"
	id_fieldname = "osm_id"
	sql = "SELECT ST_AsMVTGeom(geometry, !BBOX!) AS geom, osm_id, name, ref, ref_length, network, class, subclass, layer, level, indoor FROM layer_transportation_name(!BBOX!, !ZOOM!)"

	[[providers.layers]]
	name = "water"
	geometry_type = "polygon"
	geometry_fieldname = "geom"
	id_fieldname = "gid"
	sql = "SELECT ST_AsMVTGeom(geometry, !BBOX!) AS geom, 1 AS gid, class, brunnel, intermittent FROM layer_water(!BBOX!, !ZOOM!)"

	[[providers.layers]]
	name = "water_name"
	geometry_type = "point"
	geometry_fieldname = "geom"
	id_fieldname = "gid"
	sql = "SELECT ST_AsMVTGeom(geometry, !BBOX!) AS geom, 1 AS gid, name, class, intermittent FROM layer_water_name(!BBOX!, !ZOOM!)"

	[[providers.layers]]
	name = "waterway"
	geometry_type = "linestring"
	geometry_fieldname = "geom"
	id_fieldname = "gid"
	sql = "SELECT ST_AsMVTGeom(geometry, !BBOX!) AS geom, 1 AS gid, name, class, brunnel, intermittent FROM layer_waterway(!BBOX!, !ZOOM!)"

[[maps]]
name = "basemap"
attribution = "© OpenStreetMap"
center = [-117.2854, 32.787, 10.0] # San Diego

	[[maps.layers]]
	provider_layer = "omt.water"
	min_zoom = 0
	max_zoom = 16

	[[maps.layers]]
	provider_layer = "omt.water_name"
	min_zoom = 0
	max_zoom = 16

	[[maps.layers]]
	provider_layer = "omt.waterway"
	min_zoom = 0
	max_zoom = 16

	[[maps.layers]]
	provider_layer = "omt.boundary"
	min_zoom = 0
	max_zoom = 16

	[[maps.layers]]
	provider_layer = "omt.transportation"
	min_zoom = 0
	max_zoom = 16

	[[maps.layers]]
	provider_layer = "omt.transportation_name"
	min_zoom = 0
	max_zoom = 16

	[[maps.layers]]
	provider_layer = "omt.buildings"
	min_zoom = 14
	max_zoom = 16

[MrSatan]

FROM layer_water
how do you produce those tables and functions ? i tried openmaptiles.yaml it didnt worked

[dechristopher]

@ARolek what kind of workarounds did you do to get osml10n to work on RDS? I'm hitting that roadblock right now with a deployment.

[ARolek]

@dechristopher ahh yes, the osml10n issue. I went back and reviewed my documentation and I found this note from my past self:

---

Import the language file

Important: this language file is not exactly the same as the one in the openmaptiles-tools directory.
The following line has been removed from the get_latin_name function as it requires an plugin that is not supported on RDS and regular installs of Postgres / PostGIS:

NULLIF(osml10n_get_name_without_brackets_from_tags(tags, 'en', geometry), '')

I tried very hard to see if we could maintain this call, but after traversing the source code the plugin needs access
to the local file system to access a database it installs for various language operations. I'm not quite sure how far-reaching the effects will be for this change. I suspect the main issue will be the lack of naming support in other languages
but that's not the current use case of OSM for Scoutred.

$ docker run --rm -v "${PWD}/sql:/sql" -e PGUSER=postgres -e PGPASSWORD="" -e PGHOST="host.docker.internal" -e PGDATABASE="openmaptiles" openmaptiles/openmaptiles-tools:3.1.0 import-sql /sql/language.sql
$ docker run --rm -e PGUSER=postgres -e PGPASSWORD="" -e PGHOST="host.docker.internal" -e PGDATABASE="openmaptiles" -v "${PWD}/sql/osml10n:/sql" openmaptiles/openmaptiles-tools:3.1.0 import-sql /sql/street_abbrv.sql

---

And here's what the modified language.sql file looks like:

-- This file was migrated from https://github.com/openmaptiles/import-sql/blob/master/language.sql

CREATE OR REPLACE FUNCTION delete_empty_keys(tags hstore) RETURNS hstore AS $$
DECLARE
  result hstore;
BEGIN
  select
    hstore(array_agg(key), array_agg(value)) into result
  from
    each(hstore(tags))
  where nullif(value, '') is not null;
  RETURN result;
END;
$$ STRICT
LANGUAGE plpgsql IMMUTABLE;


CREATE OR REPLACE FUNCTION remove_latin(text) RETURNS text AS $$
  DECLARE
    i integer;
  DECLARE
    letter text;
    result text = '';
  BEGIN
    FOR i IN 1..char_length($1) LOOP
      letter := substr($1, i, 1);
      IF (unaccent(letter) !~ '^[a-zA-Z].*') THEN
        result := result || letter;
      END IF;
    END LOOP;
    result := regexp_replace(result, '(\([ -.]*\)|\[[ -.]*\])', '');
    result := regexp_replace(result, ' +\. *$', '');
    result := trim(both ' -\n' from result);
    RETURN result;
  END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;

-- See osml10n_is_latin
-- https://github.com/giggls/mapnik-german-l10n/blob/ea5da9cdfa6c931ae73eac747849140547ecd321/plpgsql/get_localized_name.sql#L19
CREATE or REPLACE FUNCTION omt_is_latin(text) RETURNS BOOLEAN AS $$
  DECLARE
    i integer;
    ascii_val int;
  BEGIN
    FOR i IN 1..char_length($1) LOOP
      ascii_val := ascii(substr($1, i, 1));
      IF (ascii_val > 591
          -- Vietnam
          -- https://en.wikipedia.org/wiki/Latin_script_in_Unicode
          -- https://en.wikipedia.org/wiki/Latin_Extended_Additional
          AND ascii_val NOT BETWEEN x'1E00'::int AND x'1EFF'::int
          -- https://en.wikipedia.org/wiki/Combining_character
          AND ascii_val NOT BETWEEN x'0300'::int AND x'036F'::int

          -- Azerbaijan
          -- https://en.wikipedia.org/wiki/IPA_Extensions
          AND ascii_val <> x'0259'::int
      ) THEN
        RETURN false;
      END IF;
    END LOOP;
    RETURN true;
  END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;

CREATE OR REPLACE FUNCTION get_latin_name(tags hstore, geometry geometry) RETURNS text AS $$
    SELECT COALESCE(
      CASE
        WHEN tags->'name' is not null and omt_is_latin(tags->'name')
          THEN tags->'name'
        ELSE NULL
      END,
      NULLIF(tags->'name:en', ''),
      NULLIF(tags->'int_name', '')
    );
$$ LANGUAGE SQL IMMUTABLE STRICT;


CREATE OR REPLACE FUNCTION get_nonlatin_name(tags hstore) RETURNS text AS $$
    SELECT
      CASE
        WHEN tags->'name' is not null and omt_is_latin(tags->'name')
          THEN NULL
        WHEN unaccent(tags->'name') ~ '[a-zA-Z]'
          THEN remove_latin(tags->'name')
        ELSE tags->'name'
      END;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION get_basic_names(tags hstore, geometry geometry) RETURNS hstore AS $$
DECLARE
  tags_array text[] := ARRAY[]::text[];
  name_latin text;
  name_nonlatin text;
  name_int text;
BEGIN
  name_latin := get_latin_name(tags, geometry);
  name_nonlatin := get_nonlatin_name(tags);
  IF (name_nonlatin = name_latin) THEN
    name_nonlatin := null;
  END IF;
  name_int := COALESCE(
    NULLIF(tags->'int_name', ''),
    NULLIF(tags->'name:en', ''),
    NULLIF(name_latin, ''),
    tags->'name'
  );
  IF name_latin IS NOT NULL THEN
    tags_array := tags_array || ARRAY['name:latin', name_latin];
  END IF;
  IF name_nonlatin IS NOT NULL THEN
    tags_array := tags_array || ARRAY['name:nonlatin', name_nonlatin];
  END IF;
  IF name_int IS NOT NULL THEN
    tags_array := tags_array || ARRAY['name_int', name_int];
  END IF;
  RETURN hstore(tags_array);
END;
$$ STRICT
LANGUAGE plpgsql IMMUTABLE;

CREATE TABLE IF NOT EXISTS wd_names(id varchar(20), page varchar(200), labels hstore);

CREATE OR REPLACE FUNCTION merge_wiki_names(tags hstore) RETURNS hstore AS $$
DECLARE
  result hstore;
BEGIN

  IF (tags ? 'wikidata' OR tags ? 'wikipedia') THEN
    select INTO result
    CASE
      WHEN avals(wd.labels) && avals(tags)
        THEN slice_language_tags(wd.labels) || tags
      ELSE tags
    END
    FROM wd_names wd
    WHERE wd.id = tags->'wikidata' OR wd.page = tags->'wikipedia';
    IF result IS NULL THEN
      result := tags;
    END IF;
  ELSE
    result := tags;
  END IF;

  RETURN result;
END;
$$ STRICT
LANGUAGE plpgsql IMMUTABLE;


CREATE OR REPLACE FUNCTION update_tags(tags hstore, geometry
  geometry) RETURNS hstore AS $$
DECLARE
  result hstore;
BEGIN
  result := delete_empty_keys(tags) || get_basic_names(tags, geometry);
  result := merge_wiki_names(result);
  RETURN result;
END;
$$ STRICT
LANGUAGE plpgsql IMMUTABLE;