search

go-spatial / tegola

Tegola is a Mapbox Vector Tile server written in Go
http://tegola.io/
MIT License
1.29k stars 196 forks source link

Running into unable to convert geometry field (geometry) into bytes #779

Closed geohacker closed 3 years ago

geohacker commented 3 years ago

@ARolek 👋 I'm writing from the OpenHistoricalMap project. We have been using Tegola as part of the OSM Seed stack. This is used for running OpenHistoricalMap. In the last few months, there has been some active mapping in the project and ST_AsBinary approach is turning out to be quite slow. So we are in the process of migrating to ST_AsMVTGeom.

We upgraded the database to Postgresql 12 + Postgis 3.0, upgraded to Tegola 0.13.0 and adjusted the queries. A small snippet is:

[[providers]]
name = "osm"
type = "postgis"
host = "${POSTGRES_HOST}"
port = 5435
database = "${POSTGRES_DB}"
user = "${POSTGRES_USER}"
password = "${POSTGRES_PASSWORD}"
srid = 3857
max_connections = 100

    [[providers.layers]]
    name = "land_0-20"
    geometry_fieldname = "geometry"
    id_fieldname = "ogc_fid"
    sql = "SELECT mvt_geom.tile AS geometry, mvt_geom.ogc_fid AS ogc_fid FROM (SELECT ST_AsMVTGeom(wkb_geometry,!BBOX!) AS tile, ogc_fid FROM land_polygons WHERE wkb_geometry && !BBOX!) AS mvt_geom WHERE mvt_geom.tile IS NOT NULL"

[[maps]]
name = "osm"
attribution = "OpenStreetMap" # map attribution
center = [-74.275329586789, -12.153492567373, 8.0] # optional center value. part of the TileJSON spec

    [[maps.layers]]
    name = "land"
    provider_layer = "osm.land_0-20"
    min_zoom = 0
    max_zoom = 20

This is the land_polygons that are imported via imposm3.

When I run the tiler against this database, I see the following errors:

2021/05/20 09:24:54 map.go:304: err fetching tile (z: 3, x: 4, y: 4) features: for layer (land_0-20) unable to convert geometry field (geometry) into bytes.
2021/05/20 09:24:54 map.go:304: err fetching tile (z: 3, x: 4, y: 5) features: for layer (land_0-20) unable to convert geometry field (geometry) into bytes.

I tried executing the sample queries directly on the database and they look ok to me, but I'm not sure if we are doing something wrong.

Sample query:

SELECT mvt_geom.tile AS geometry, mvt_geom.ogc_fid AS ogc_fid FROM (SELECT ST_AsMVTGeom(wkb_geometry,ST_MakeEnvelope(-1.0097025686953126e+07,-1.5106402771953125e+07,-4.931105568046876e+06,-9.940482653046872e+06,3857)) AS tile, ogc_fid FROM land_polygons WHERE wkb_geometry && ST_MakeEnvelope(-1.0097025686953126e+07,-1.5106402771953125e+07,-4.931105568046876e+06,-9.940482653046872e+06,3857)) AS mvt_geom WHERE mvt_geom.tile IS NOT NULL;

psql results:

                                                                                                                      |      74
 0103000020110F000001000000050000000000000000209C400000000000C2A7400000000000209C400000000000B2A94000000000003C98400000000000B2A94000000000003C98400000000000C2A7400000000000209C400000000000C2A740

Do you have any suggestions on what we should try next? We really enjoy using Tegola and are hoping make a push to ST_AsMVT to improve performance. Thank you!

cc @batpad @Rub21 @danrademacher

ARolek commented 3 years ago

@geohacker 👋 I was completely unaware of the OSM Seed Stack! It's great to see you all are using tegola. Please let me know if there's anything we can do to help support the effort!

Let's see if we can get this query fixed for you. Here's the critical piece:

SELECT mvt_geom.tile AS geometry, mvt_geom.ogc_fid AS ogc_fid FROM (SELECT ST_AsMVTGeom(wkb_geometry,!BBOX!) AS tile, ogc_fid FROM land_polygons WHERE wkb_geometry && !BBOX!) AS mvt_geom WHERE mvt_geom.tile IS NOT NULL

With the mvt_postgis provider tegola is going to combine all the layers into a compound query and then return an encoded MVT. It looks like you're trying to wrap the query again and then access the tile, but this is not necessary. Try using this query:

SELECT ST_AsMVTGeom(wkb_geometry,!BBOX!) AS tile, ogc_fid FROM land_polygons WHERE wkb_geometry && !BBOX!

Tegola will handle the rest of the necessary wrapping for you.

LMK if it works!

geohacker commented 3 years ago

@ARolek thank you for the quick response! There are some things I'd love to chat about integrating as part ohm but I'll reach out separately.

I did suspect that query and was using the straightforward query without the subquery initially. That also unfortunately gives me the same error.

The reason why I tried to wrap the st_asmvtgeom is because Tegola had trouble determining the geometry type when some tiles come empty. It wouldn't start Tegola and say that couldn't support geometry type nil.(Typing this from my phone so I don't have the exact message)

I'll poke around the query a bit more today and post here but let me know if there's another direction I should look as well. Thank you so much. Really appreciate your support!

ARolek commented 3 years ago

Tegola had trouble determining the geometry type when some tiles come empty

Set the geometry type so tegola doesn't try to infer it (the infer part is not that great):

    [[providers.layers]]
    name = "land_0-20"
    geometry_fieldname = "tile"
    id_fieldname = "ogc_fid"
    geometry_type = "polygon"
    sql = "SELECT ST_AsMVTGeom(wkb_geometry,!BBOX!) AS tile, ogc_fid FROM land_polygons WHERE wkb_geometry && !BBOX!"

Note I also adjusted the geometry_fieldname in this example.

There are some things I'd love to chat about integrating as part ohm but I'll reach out separately.

Reach out anytime. You can also catch us on Slack.

geohacker commented 3 years ago

@ARolek thank you! I'm going to try this and get back!

geohacker commented 3 years ago

Still same:

2021/05/21 05:04:50 postgis.go:529: TEGOLA_SQL_DEBUG:EXECUTE_SQL for layer (land_0-20): SELECT ST_AsMVTGeom(wkb_geometry,ST_MakeEnvelope(-1.7571955555976562e+07,4.970241326523438e+06,-1.4988995496523436e+07,7.553201385976562e+06,3857)) AS geometry, ogc_fid FROM land_polygons WHERE wkb_geometry && ST_MakeEnvelope(-1.7571955555976562e+07,4.970241326523438e+06,-1.4988995496523436e+07,7.553201385976562e+06,3857)
2021/05/21 05:04:51 map.go:304: err fetching tile (z: 4, x: 1, y: 5) features: for layer (land_0-20) unable to convert geometry field (geometry) into bytes.

This is how the config looks like now, based on your suggestion:

[[providers]]
name = "osm"
type = "postgis"
host = "${POSTGRES_HOST}"
port = "${POSTGRES_PORT}"
database = "${POSTGRES_DB}"
user = "${POSTGRES_USER}"
password = "${POSTGRES_PASSWORD}"
# srid = 3857
max_connections = 40

    [[providers.layers]]
    name = "land_0-20"
    geometry_fieldname = "geometry"
    geometry_type = "polygon"
    id_fieldname = "ogc_fid"
    sql = "SELECT ST_AsMVTGeom(wkb_geometry,!BBOX!) AS geometry, ogc_fid FROM land_polygons WHERE wkb_geometry && !BBOX!"

I also added back the water_areas layer and that layer also gives me the same error. I'm pretty sure we are doing something wrong but can't seem to pin point. When I run the queries directly on the database, I do get reasonable looking results :(

2021/05/21 05:17:54 postgis.go:529: TEGOLA_SQL_DEBUG:EXECUTE_SQL for layer (water_areas): SELECT ST_AsMVTGeom(geometry,ST_MakeEnvelope(-2.005707621923828e+07,6.242153477011718e+06,-1.876559618951172e+07,7.533633506738281e+06,3857)) AS geometry, osm_id, name, type, area, tags->'start_date' as start_date, tags->'end_date' as end_date, isodatetodecimaldate(pad_date(tags->'start_date', 'start'), FALSE) AS start_decdate, isodatetodecimaldate(pad_date(tags->'end_date', 'end'), FALSE) AS end_decdate FROM osm_water_areas WHERE type IN ('water', 'pond', 'basin', 'canal', 'mill_pond', 'riverbank', 'dock') AND geometry && ST_MakeEnvelope(-2.005707621923828e+07,6.242153477011718e+06,-1.876559618951172e+07,7.533633506738281e+06,3857)

2021/05/21 05:17:57 map.go:304: err fetching tile (z: 5, x: 1, y: 8) features: for layer (water_areas) unable to convert geometry field (geometry) into bytes.
geohacker commented 3 years ago

Quick update, I noticed I was using the wrong provider postgis instead of mvt_postgis — but still running into the error mentioned above without geometry_type. But now with geometry_type I'm making some progress:

2021/05/21 05:54:22 postgis.go:674: TEGOLA_SQL_DEBUG:EXECUTE_SQL: SELECT ((SELECT ST_AsMVT(q,'land',4096,'geometry','ogc_fid') AS data FROM (SELECT ST_AsMVTGeom(wkb_geometry,ST_MakeEnvelope(-2.003750834e+07,2.5046885425000004e+06,-1.75328197975e+07,5.009377085000001e+06,3857)) AS geometry, ogc_fid FROM land_polygons WHERE wkb_geometry && ST_MakeEnvelope(-2.003750834e+07,2.5046885425000004e+06,-1.75328197975e+07,5.009377085000001e+06,3857)) AS q)||(SELECT ST_AsMVT(q,'water_areas',4096,'geometry','id') AS data FROM (SELECT ST_AsMVTGeom(geometry,ST_MakeEnvelope(-2.003750834e+07,2.5046885425000004e+06,-1.75328197975e+07,5.009377085000001e+06,3857)) AS geometry, osm_id, name, type, area, tags->'start_date' as start_date, tags->'end_date' as end_date, isodatetodecimaldate(pad_date(tags->'start_date', 'start'), FALSE) AS start_decdate, isodatetodecimaldate(pad_date(tags->'end_date', 'end'), FALSE) AS end_decdate FROM osm_water_areas WHERE type IN ('water', 'pond', 'basin', 'canal', 'mill_pond', 'riverbank', 'dock') AND geometry && ST_MakeEnvelope(-2.003750834e+07,2.5046885425000004e+06,-1.75328197975e+07,5.009377085000001e+06,3857)) AS q)) AS data
2021/05/21 05:54:22 postgis.go:676: TEGOLA_SQL_DEBUG:EXECUTE_SQL: returned error ERROR: mvt_agg_transfn: Could not find column 'id' of integer type (SQLSTATE XX000)
2021-05-21 05:54:22 [ERROR] handle_map_layer_zxy.go:165: error marshalling tile: ERROR: mvt_agg_transfn: Could not find column 'id' of integer type (SQLSTATE XX000)

Though no errors from the land_polygons layer, the tiles are all empty. Will keep digging.

geohacker commented 3 years ago

image

!!! ALRIGHT !!

@ARolek this is now looking great. It came down to:

  1. Setting explicit geometry_type — how should this be when we have multiple geometry types?
  2. My mishap that caused the type to be incorrect.
  3. id needs to be in the select clause

I'm going to try add all the layers back and see how it goes. Thank you so much for your support. I dont think I'd have been able to figure out if I didn't talk to you!

geohacker commented 3 years ago

I've deployed this to staging now and it works great https://staging.openhistoricalmap.org/. I'll close this ticket. Once again huge thank you to @ARolek and all the contributors of Tegola!

ARolek commented 3 years ago

@geohacker excellent! I'm glad you got it working!

Setting explicit geometry_type — how should this be when we have multiple geometry types?

Geometry collections are not supported by MVT encoding. You must use a specific geoemtry type per layer.

The maps look great!

pnorman commented 3 years ago

Geometry collections are not supported by MVT encoding. You must use a specific geoemtry type per layer.

Geometry type is per feature, not per layer.

ARolek commented 3 years ago

@pnorman is correct.

pnorman commented 3 years ago

My point was that you can have multiple geometry types in a given layer for a MVT

ARolek commented 3 years ago

Yep. That's why I said you're correct. Tegola's internal viewer auto generates a style from the geometry_type field, so the viewer doesn't support more than one geometry_type per layer. The encoder does not use the geometry_type field so a custom style can work with a layer of heterogenous geometry types.