Corrupted tiles when mapping multiple provider layers

The problem that I'm coming across is when trying to merge multiple provider layers into a mapping layers, some features will cause the tiles to corrupt and not output the data corresponding to the SQL queries.

What's more strange is if I separate these exact same features using the exact same SQL queries into separately called mapping layers, it doesn't run into any issues.

I've used the SQL debug "TEGOLA_SQL_DEBUG=EXECUTE_SQL ./tegola serve --config=test.toml" to test the outputs in postgis as they are queried and all were successful. However is is important to note that some tiles queried empty outputs (due to no features within those bounds).

I'll provide everything below. I've also provided an image of the output.

"Boundary" is the combined version of "boundary_z1 & boundary_z2"

[webserver]
port = ":8080"              # port to bind the web server to. defaults ":8080"

#   Optional - Allows tegola to respond to tile request with user defined headers. Default CORS headers values:
#   [webserver.headers]
#   Access-Control-Allow-Origin = "*"
#   Cache-Control = "no-cache, no-store, must-revalidate"

[cache]                     # configure a tile cache
type = "file"               # a file cache will cache to the local file system
basepath = "/tmp/tegola"    # where to write the file cache

#   OpenStreetMap (OSM_PLANET)
[[providers]]
name = "osm"
type = "postgis"
host = "*"
port = "5432"
database = "*"
user = "*"
password = "*"
srid = 3857

    # Boundary
    [[providers.layers]]
    name = "boundary_z0"
    geometry_fieldname = "geometry"
    id_fieldname = "osm_id"
    sql = """
    SELECT ST_AsBinary(geometry) AS geometry,
        osm_id,
        2 AS admin_level,
        (CASE WHEN featurecla LIKE 'Disputed%' THEN true ELSE false END) AS disputed,
        NULL::text AS disputed_name,
        NULL::text AS claimed_by,
        false AS maritime
    FROM ne_110m_admin_0_boundary_lines_land
    WHERE !BBOX! && geometry;
    """
    geometry_type = "MultiLineString"

    [[providers.layers]]
    name = "boundary_z1"
    geometry_fieldname = "geometry"
    id_fieldname = "osm_id"
    sql = """
    SELECT geometry, osm_id, admin_level, disputed, disputed_name, claimed_by, maritime FROM (
        SELECT ST_AsBinary(geometry) AS geometry,
            osm_id,
            2 AS admin_level,
            (CASE WHEN featurecla LIKE 'Disputed%' THEN true ELSE false END) AS disputed,
            NULL AS disputed_name,
            NULL AS claimed_by,
            false AS maritime
        FROM ne_50m_admin_0_boundary_lines_land
        UNION ALL
        SELECT ST_AsBinary(geometry) AS geometry,
            osm_id,
            4 AS admin_level,
            false AS disputed,
            NULL AS disputed_name,
            NULL AS claimed_by,
            false AS maritime
        FROM ne_50m_admin_1_states_provinces_lines
        UNION ALL
        SELECT ST_AsBinary(geometry) AS geometry,
            osm_id,
            admin_level,
            true AS disputed,
            edit_name(name) AS disputed_name,
            claimed_by,
            maritime
        FROM osm_border_disp_linestring_gen11
    ) AS zoom_level
    WHERE !BBOX! && geometry;
    """
    geometry_type = "MultiLineString"

[[maps]]
name = "osm"
attribution = "OpenStreetMap" # map attribution
center = [-76.275329586789, 39.153492567373, 1.5] # optional center value. part of the TileJSON spec

    # Country Lines Layer
    [[maps.layers]]
    name = "boundary_z0"
    provider_layer = "osm.boundary_z0"
    min_zoom = 0
    max_zoom = 0
    dont_simplify = true

    # Country Lines Layer
    [[maps.layers]]
    name = "boundary_z1"
    provider_layer = "osm.boundary_z1"
    min_zoom = 1
    max_zoom = 2
    dont_simplify = true

    # Country Lines Layer
    [[maps.layers]]
    name = "boundary"
    provider_layer = "osm.boundary_z0"
    min_zoom = 0
    max_zoom = 0
    dont_simplify = true

    # Country Lines Layer
    [[maps.layers]]
    name = "boundary"
    provider_layer = "osm.boundary_z1"
    min_zoom = 1
    max_zoom = 2
    dont_simplify = true

tegola_issue

@Dotaknight apologies for the slow response. Looking at your queries and screenshots, it looks to me like you have the OMS boundary data but you don't have any water or land polygons which would outline the landmass. What makes you believe the data is corrupted?

No worries! The reason the I consider the tiles to be corrupted is that any subsequent mapping layers will not display the data even though the subsequent sql queries are properly loading in the debug reports.

This is shown on the previous screenshot by the lack of missing data from boundary_z1 (represented by the green lines). But the problem doesn't only affect that specific layer, it affects any others that would follow (Lets say I add a water layer, poi layer). None of those will display their data either unless boundary is removed or commented out.

Also if I copy the sql debug of the layer that is causing the issue (in this case "boundary"), the query is handled perfectly fine (no errors) and I'm able to view the geometry through the postgresql geometry viewer.

Interestingly enough, through a fair amount of testing, I've found that the problem arises when tegola tries to merge mapping layers at different zoom levels with the same mapping layer name represented by the following example below.

    # Country Lines Layer
    [[maps.layers]]
    name = "boundary"
    provider_layer = "osm.boundary_z0"
    min_zoom = 0
    max_zoom = 0
    dont_simplify = true

    # Country Lines Layer
    [[maps.layers]]
    name = "boundary"
    provider_layer = "osm.boundary_z1"
    min_zoom = 1
    max_zoom = 2
    dont_simplify = true

However, I've been able to "fix" the problem by merging all of the layers in the provider layer using "UNION ALL". This is definitely not an ideal fix. Example of the new provider layer below.

    # Boundary
    [[providers.layers]]
    name = "boundary"
    geometry_fieldname = "geometry"
    id_fieldname = "osm_id"
    sql = """
    SELECT geometry, osm_id, admin_level, disputed, disputed_name, claimed_by, maritime FROM (
        SELECT ST_AsBinary(geometry) AS geometry,
            osm_id,
            2 AS admin_level,
            (CASE WHEN featurecla LIKE 'Disputed%' THEN true ELSE false END) AS disputed,
            NULL::text AS disputed_name,
            NULL::text AS claimed_by,
            false AS maritime
        FROM ne_110m_admin_0_boundary_lines_land
        WHERE !ZOOM! = 0
        UNION ALL
        SELECT ST_AsBinary(geometry) AS geometry,
            osm_id,
            2 AS admin_level,
            (CASE WHEN featurecla LIKE 'Disputed%' THEN true ELSE false END) AS disputed,
            NULL AS disputed_name,
            NULL AS claimed_by,
            false AS maritime
        FROM ne_50m_admin_0_boundary_lines_land
        WHERE !ZOOM! BETWEEN 1 AND 2
        UNION ALL
        SELECT ST_AsBinary(geometry) AS geometry,
            osm_id,
            4 AS admin_level,
            false AS disputed,
            NULL AS disputed_name,
            NULL AS claimed_by,
            false AS maritime
        FROM ne_50m_admin_1_states_provinces_lines
        WHERE !ZOOM! BETWEEN 1 AND 2
        UNION ALL
        SELECT ST_AsBinary(geometry) AS geometry,
            osm_id,
            admin_level,
            true AS disputed,
            edit_name(name) AS disputed_name,
            claimed_by,
            maritime
        FROM osm_border_disp_linestring_gen11
        WHERE !ZOOM! BETWEEN 1 AND 2
    ) AS zoom_level
    WHERE !BBOX! && geometry;
    """
    geometry_type = "MultiLineString"

@Dotaknight would you be able to post a backup of your database so I can try to recreate the issue? I don't see any clear issues with what you're doing so I'm going to need to try to debug it directly. Also, it might be easier to chat through this issue on our slack: http://slack.go-spatial.org/

go-spatial / tegola

Corrupted tiles when mapping multiple provider layers #669