fieldname (geometry) not found

[pessimo]

I tried to use the edge build as well as the latest build of gospatial/tegola docker image, with a pg14 database, but it gives me error.

2022-07-26 09:22:38 [ERROR] map.go:342: err fetching tile (z: 5, x: 22, y: 11) features: postgis: geom fieldname (geometry) not found for layer (building)
2022-07-26 09:22:38 [ERROR] map.go:342: err fetching tile (z: 5, x: 21, y: 12) features: postgis: geom fieldname (geometry) not found for layer (building)
2022-07-26 09:22:38 [ERROR] map.go:342: err fetching tile (z: 5, x: 24, y: 11) features: postgis: geom fieldname (geometry) not found for layer (building)
2022-07-26 09:22:38 [ERROR] map.go:342: err fetching tile (z: 5, x: 23, y: 10) features: postgis: geom fieldname (geometry) not found for layer (building)
2022-07-26 09:22:38 [ERROR] map.go:342: err fetching tile (z: 5, x: 23, y: 12) features: postgis: geom fieldname (geometry) not found for layer (building)
2022-07-26 09:22:38 [ERROR] map.go:342: err fetching tile (z: 5, x: 23, y: 11) features: postgis: geom fieldname (geometry) not found for layer (building)
2022-07-26 09:22:38 [ERROR] map.go:342: err fetching tile (z: 5, x: 22, y: 12) features: postgis: geom fieldname (geometry) not found for layer (building)
2022-07-26 09:22:38 [ERROR] map.go:342: err fetching tile (z: 5, x: 24, y: 10) features: postgis: geom fieldname (geometry) not found for layer (building)
2022-07-26 09:22:46 [ERROR] map.go:342: err fetching tile (z: 5, x: 25, y: 10) features: postgis: geom fieldname (geometry) not found for layer (building)
2022-07-26 09:22:46 [ERROR] map.go:342: err fetching tile (z: 5, x: 25, y: 11) features: postgis: geom fieldname (geometry) not found for layer (building)

This error persist even if i change to use prebuilt binary from the release.

This is my config.

[webserver]
port = ":8080"
CORSAllowedOrigin = "*"

# register data providers
[[providers]]
# provider name is referenced from map layers (required)
name = "ppad"
# the type of data provider. currently only supports postgis (required)
type = "postgis"
uri = "postgres://postgres:passwd@host:port/postgres"
# The default srid for this provider. Defaults to WebMercator (3857) (optional)
srid = 4326
# The max connections to maintain in the connection pool. Default is 100. (optional)
max_connections = 50

  [[providers.layers]]
  name = "building"
  # tablename = "vm_0.customer_0"
  geometry_fieldname = "geometry"
  geometry_type = "Polygon"
  id_fieldname = "id"
  sql = "SELECT ST_AsBinary(geometry) AS geom, id FROM vm_0.customer_0 WHERE geometry && !BBOX!"

#   [[providers.layers]]
#   name = "main_roads"
#   geometry_fieldname = "wkb_geometry"
#   id_fieldname = "ogc_fid"
#   sql = "SELECT ST_AsBinary(wkb_geometry) AS wkb_geometry, name, ogc_fid FROM main_roads_3857 WHERE wkb_geometry && !BBOX!"

#   [[providers.layers]]
#   name = "lakes"
#   geometry_fieldname = "wkb_geometry"
#   id_fieldname = "ogc_fid"
#   sql = "SELECT ST_AsBinary(wkb_geometry) AS wkb_geometry, name, ogc_fid FROM lakes_3857 WHERE wkb_geometry && !BBOX!"

[[maps]]
name = "default"

  [[maps.layers]]
  provider_layer = "ppad.building"
  min_zoom = 5
  max_zoom = 20

#   [[maps.layers]]
#   provider_layer = "bonn.main_roads"
#   min_zoom = 5
#   max_zoom = 20

#   [[maps.layers]]
#   provider_layer = "bonn.lakes"
#   min_zoom = 5
#   max_zoom = 20

the database table structure looks like this

so I'm pretty sure 'geometry' column is in the table.

content was downloaded from the internet, they were outlines of countries.

Also, I noticed that if I level geometry_type blank, the serve command will output a error like this

2022-07-26 09:34:59 [INFO] handle_map_style.go:138: unable to infer geometry type for providerLayerName: building. style definition not generated

I tried to debug with

 TEGOLA_SQL_DEBUG=LAYER_SQL ./tegola serve --config=./tegola_config/config.toml

but it didn't output sql I want.

I tried to change sql with something like

SELECT ST_AsMVTGeom(geometry,!BBOX!) AS geom, id FROM vm_0.customer_0 WHERE geometry && !BBOX!;

It says

2022-07-26 09:48:55 [ERROR] map.go:342: err fetching tile (z: 5, x: 0, y: 17) features: ERROR: ST_AsMVTGeom: Compiled without protobuf-c support (SQLSTATE XX000)

[pessimo]

there's something wrong with my postgresql build. I found that I'm using a image which lack of protobuf-c support. So, I changed the image and the last problem goes away.

but still, tegola can't find geometry column.

if I change to use ST_AsBinary, and tell tegola geometry_type specificly, tegola will output something like

unable to convert geometry field (geometry) into bytes

and I find a similar issue in #779

[iwpnd]

Hey @pessimo

You are overwriting your geometry column in your sql.

  [[providers.layers]]
  name = "building"
  # tablename = "vm_0.customer_0"
  geometry_fieldname = "geometry"
  geometry_type = "Polygon"
  id_fieldname = "id"
  sql = "SELECT ST_AsBinary(geometry) AS geom, id FROM vm_0.customer_0 WHERE geometry && !BBOX!"

Either set geometry_fieldname to geom or change your sql to SELECT ST_AsBinary(geometry) AS geometry [...]. The postgis provider expects the geometry as binary, the mvt_provider expects the geometry as mvtgeom.

[pessimo]

thanks for the reply, I understand now.

1、the geometry_fieldname is not pointing to the geometry column in the real table but is actually pointing to the result output of sql, so you need to match them two

2、 postgis provider need ST_AsBinary in sql and mvt_postgis provider need ST_AsMVTGeom, is that correct?

I tried the two providers, mvt_postgis paired with st_asmvtgeom gives me the right result with no error(I can see tiles in the viewer), while postgis provider paired with st_asbinary gives me no output(no tile in the viewer and also no error output).

so is there any difference between postgis and mvt_postgis provider？

[iwpnd]

There is a world of difference between the two.

• postgis: tegola fetches row from PostGIS, infers the geometry type, cuts data into pieces, creates the tiles and pushes it to the client.
• mvt_postgis: PostGIS does the heavy lifting, tegola just passes the created tiles forward.

https://github.com/iwpnd/tegola-example-bonn

This package contains the example for Bonn in a quick to start containerized setup. Check the configs there and see if that helps you to figure out the issue.

[pessimo]

thanks again for the reply, I'll check the Bonn example