srid 2056 transformation

[FabianRechsteiner]

Hello,

I have a PostGIS table named avprodukt.dk_einzelpunkt_mv containing point data in the EPSG:2056 coordinate system. Now, I want to serve these data as Vector Tiles (EPSG:3857).

However, when running the server, I encounter the following error message:

Error: could not register providers: could not generate SQL, for layer (dk_einzelpunkt_mv): Error trying to convert tile point: don't know how to convert from 2056 to 3857.

I am using the provider type "postgis" and the parameter srid = 2056.

What do I need to adjust to ensure that the data are transformed to 3857 before tiling?

Thank you for your assistance.

Regards,
Fabian

[ARolek]

@FabianRechsteiner take a look at this example which applies a transform on a geometry column in 4326 to convert it to 3857 at query time: https://github.com/go-spatial/tegola/tree/master/mvtprovider/postgis#example-mvt_postgis-and-map-config-for-srid-4326

[FabianRechsteiner]

thanks

I changed the type to mvt_postgis.
type = "mvt_postgis"

If I use the sql parameter without ST_Transform, the Points are shown for all zoom levels somewhere in africa.

   [[providers.layers]]
   name = "dk_einzelpunkt_mv"
   id_fieldname = "objectid"
   geometry_fieldname = "wkb_geometry"
   sql = "SELECT ST_AsMVTGeom(wkb_geometry,!BBOX!) AS wkb_geometry, objectid FROM avprodukt.dk_einzelpunkt_mv WHERE wkb_geometry && !BBOX!"

If I use the sql parameter with ST_Transform, the Points are shown on the correct position in switzerland for the zoomlevels 1 and 2. But from level 3 the points are no longer displayed.
Is my sql query wrong?

   [[providers.layers]]
   name = "dk_einzelpunkt_mv"
   id_fieldname = "objectid"
   geometry_fieldname = "wkb_geometry"
   sql = "SELECT ST_AsMVTGeom(ST_Transform(wkb_geometry, 3857),ST_Transform(!BBOX!,3857)) AS wkb_geometry, objectid FROM avprodukt.dk_einzelpunkt_mv WHERE wkb_geometry && !BBOX!"

[ARolek]

I think the issue is that you're not doing a transform on the !BBOX! token in the WHERE clause. In the example I shared tegola is sending the !BBOX! in with the 4326 project. You need to re-project that !BBOX! so your points intersect with it. For example:

sql = "SELECT ST_AsMVTGeom(ST_Transform(wkb_geometry, 3857),ST_Transform(!BBOX!,3857)) AS wkb_geometry, objectid FROM avprodukt.dk_einzelpunkt_mv WHERE wkb_geometry && ST_Transform(!BBOX!,2056) "

[FabianRechsteiner]

Thanks, that worked, but only for point geometry type.

If i use more providers.layers with different geometry types (Point, Linesting, Polygon), there is a problem:

tegola   | SELECT ST_AsMVTGeom(ST_Transform(wkb_geometry, 3857),ST_Transform(!BBOX!,3857)) AS wkb_geometry, objectid, art FROM avprodukt.bo_boflaeche_mv WHERE wkb_geometry && ST_Transform(!BBOX!,2056) 
tegola   | Error: could not register providers: error fetching geometry type for layer (bo_boflaeche_mv): layer (bo_boflaeche_mv) returned unsupported geometry type (<nil>)
tegola   | if custom parameters are used, remember to set geometry_type for the provider

[ARolek]

@FabianRechsteiner if the geometry_type of a layer is not defined, then tegola will attempt to infer it by forming up a query and then reading the type from the result. This operation is not that great. So simply set the geometry_type on the provider layer to the expected type and this error will go away.

[FabianRechsteiner]

perfect. That worked.

Thank you for your help.

Here ist my full config.toml File:

# --- /tegola/config.toml ---
[webserver]
port = ":8090"
#CORSAllowedOrigin = "*"

# --- register data providers ---
[[providers]]
name = "avprodukt"  # provider name is referenced from map layers (required)
type = "mvt_postgis"
uri = "postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:5432/${POSTGRES_DB}"

# --- provider-layers ---

    [[providers.layers]]
    name = "bo_boflaeche_mv"
    id_fieldname = "objectid"
    geometry_fieldname = "wkb_geometry"
    geometry_type = "polygon"
    sql = "SELECT ST_AsMVTGeom(ST_Transform(wkb_geometry, 3857),ST_Transform(!BBOX!,3857)) AS wkb_geometry, objectid, art FROM avprodukt.bo_boflaeche_mv WHERE wkb_geometry && ST_Transform(!BBOX!,2056) "

    [[providers.layers]]
    name = "gg_gemeindegrenze_mv"
    id_fieldname = "objectid"
    geometry_fieldname = "wkb_geometry"
    geometry_type = "polygon"
    sql = "SELECT ST_AsMVTGeom(ST_Transform(wkb_geometry, 3857),ST_Transform(!BBOX!,3857)) AS wkb_geometry, objectid, gemeinde_name, gemeinde_bfsnr FROM avprodukt.gg_gemeindegrenze_mv WHERE wkb_geometry && ST_Transform(!BBOX!,2056) "

    [[providers.layers]]
    name = "li_liegenschaft_mv"
    id_fieldname = "objectid"
    geometry_fieldname = "wkb_geometry"
    geometry_type = "polygon"
    sql = "SELECT ST_AsMVTGeom(ST_Transform(wkb_geometry, 3857),ST_Transform(!BBOX!,3857)) AS wkb_geometry, objectid, gs_nummer, gs_egris_egrid FROM avprodukt.li_liegenschaft_mv WHERE wkb_geometry && ST_Transform(!BBOX!,2056) "

    [[providers.layers]]
    name = "li_grenzpunkt_mv"
    id_fieldname = "objectid"
    geometry_fieldname = "wkb_geometry"
    geometry_type = "point"
    sql = "SELECT ST_AsMVTGeom(ST_Transform(wkb_geometry, 3857),ST_Transform(!BBOX!,3857)) AS wkb_geometry, objectid, punktzeichen FROM avprodukt.li_grenzpunkt_mv WHERE wkb_geometry && ST_Transform(!BBOX!,2056) "

    [[providers.layers]]
    name = "eo_linienelement_mv"
    id_fieldname = "objectid"
    geometry_fieldname = "wkb_geometry"
    geometry_type = "linestring"
    sql = "SELECT ST_AsMVTGeom(ST_Transform(wkb_geometry, 3857),ST_Transform(!BBOX!,3857)) AS wkb_geometry, objectid, art FROM avprodukt.eo_linienelement_mv WHERE wkb_geometry && ST_Transform(!BBOX!,2056) "

    [[providers.layers]]
    name = "bo_projgebaeude_mv"
    id_fieldname = "objectid"
    geometry_fieldname = "wkb_geometry"
    geometry_type = "polygon"
    sql = "SELECT ST_AsMVTGeom(ST_Transform(wkb_geometry, 3857),ST_Transform(!BBOX!,3857)) AS wkb_geometry, objectid FROM avprodukt.bo_projgebaeude_mv WHERE wkb_geometry && ST_Transform(!BBOX!,2056) "

    [[maps]]
    name = "avprodukt"
    attribution = "Amtliche Vermessung"
    center = [9.08545538325693, 47.57304195845273, 10.0] # set the center of the map so the user is auto navigated to Canton Thurgau

    [[maps.layers]]
    provider_layer = "avprodukt.bo_boflaeche_mv"
    min_zoom = 10
    max_zoom = 22

    [[maps.layers]]
    provider_layer = "avprodukt.gg_gemeindegrenze_mv"
    min_zoom = 10
    max_zoom = 22

    [[maps.layers]]
    provider_layer = "avprodukt.li_liegenschaft_mv"
    min_zoom = 5
    max_zoom = 22

    [[maps.layers]]
    provider_layer = "avprodukt.li_grenzpunkt_mv"
    min_zoom = 0
    max_zoom = 22

    [[maps.layers]]
    provider_layer = "avprodukt.eo_linienelement_mv"
    min_zoom = 15
    max_zoom = 22

    [[maps.layers]]
    provider_layer = "avprodukt.bo_projgebaeude_mv"
    min_zoom = 10
    max_zoom = 22

[ARolek]

Excellent! I always love the tegola generated style renders. Thanks for sharing yours ;-)