Closed dwoznicki closed 2 years ago
@dwoznicki try using the mvt_postgis provider rather than the postgis provider. The postgis provider has some issues with rendering certain polygons.
Cool, I'll give that a shot. It looks like this means I'll need to reseed my tile cache. Is that right?
Yeah, but mvt_postgis is pretty fast.
Hey @ARolek, I'm having a bit of trouble getting the mvt_postgis provider working. I'm using go-spatial/tegola-osm as my starting point for my tegola.toml file, and I've modified all the SQL queries to call ST_AsMVTGeom(geom_column, !BBOX!)
instead of ST_AsBinary(geom_column)
.
For example,
# Water
[[providers.layers]]
name = "water_areas"
geometry_fieldname = "geometry"
id_fieldname = "osm_id"
sql = "SELECT ST_AsBinary(geometry) AS geometry, osm_id, name, type, area FROM osm_water_areas WHERE type IN ('water', 'pond', 'basin', 'canal', 'mill_pond', 'riverbank', 'dock') AND geometry && !BBOX!"
becomes
# Water
[[providers.layers]]
name = "water_areas"
geometry_fieldname = "geometry"
id_fieldname = "osm_id"
sql = "SELECT ST_AsMVTGeom(geometry, !BBOX!) AS geometry, osm_id, name, type, area FROM osm_water_areas WHERE type IN ('water', 'pond', 'basin', 'canal', 'mill_pond', 'riverbank', 'dock') AND geometry && !BBOX!"
However, when I try and start the server, I get an error and the server does not start up.
16:59:41 ❯ tegola server --config /opt/tegola-osm/tegola.toml
2022-05-27 16:59:49 [INFO] config.go:317: loading local config (/opt/tegola-osm/tegola.toml)
2022-05-27 16:59:49 [WARN] postgis.go:332: Connecting to PostGIS with connection parameters is deprecated. Use 'uri' instead.
Error: could not register providers: error fetching geometry type for layer (water_areas): layer (water_areas) returned unsupported geometry type (<nil>)
I've confirmed that running the SQL query returns null for the geometry column in the database using ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096))
in place of !BBOX!
.
Do you have any idea what the problem here might be?
Hi @dwoznicki
You're not sharing the rest of your config, so I hope you set the provider type correctly
[[providers]]
name = "your_name"
type = "mvt_postgis"
[...]
!BBOX!
can be substituted with ST_MakeEnvelope(min_longitude, min_latitude, max_longitude, max_latitude, SRID)
. I'm not sure what you expected using ST_Point(4096, 4096)
. That way you can query your database and check the resulting geometry type. If a row does not have a geometry, tegola will err like that.
You could also try and use:
[[providers.layers]]
name = "your_layer_name"
geometry_type="polygon"
[...]
and tell tegola what geometry type to expect, however if there really is none, that will not work either.
@iwpnd Thanks for the response! I do have my provider type set to "mvt_postgis"
, though there could easily be some other misconfiguration.
Taking a look at my SQL statement log, it looks like the first query Tegola runs to check the geometry type for table land_polygons
succeeds.
vector=# SELECT ST_GeometryType(geometry) FROM (SELECT ST_AsMVTGeom(wkb_geometry, ST_MakeEnvelope(-2.0663680475625e+07,-2.0663680475625e+07,2.0663680475625e+07,2.0663680475625e+07,3857)) AS geometry, ogc_fid FROM land_polygons WHERE wkb_geometry && ST_MakeEnvelope(-2.0663680475625e+07,-2.0663680475625e+07,2.0663680475625e+07,2.0663680475625e+07,3857)) as q LIMIT 1;
st_geometrytype
-----------------
ST_Polygon
(1 row)
But the osm_water_areas
table query returns null.
vector=# SELECT ST_GeometryType(geometry) FROM (SELECT ST_AsMVTGeom(geometry, ST_MakeEnvelope(-2.0663680475625e+07,-2.0663680475625e+07,2.0663680475625e+07,2.0663680475625e+07,3857)) AS geometry, osm_id, name, type, area FROM osm_water_areas WHERE type IN ('water', 'pond', 'basin', 'canal', 'mill_pond', 'riverbank', 'dock') AND geometry && ST_MakeEnvelope(-2.0663680475625e+07,-2.0663680475625e+07,2.0663680475625e+07,2.0663680475625e+07,3857)) as q LIMIT 1;
st_geometrytype
-----------------
(1 row)
It looks like the ST_AsMVTGeom
call is returning a null geometry for this table, so it makes sense that the ST_GeometryType
call is failing.
vector=# SELECT ST_AsMVTGeom(geometry, ST_MakeEnvelope(-2.0663680475625e+07,-2.0663680475625e+07,2.0663680475625e+07,2.0663680475625e+07,3857)) AS geometry, osm_id, name, type, area FROM osm_water_areas WHERE type IN ('water', 'pond', 'basin', 'canal', 'mill_pond', 'riverbank', 'dock') AND geometry && ST_MakeEnvelope(-2.0663680475625e+07,-2.0663680475625e+07,2.0663680475625e+07,2.0663680475625e+07,3857) limit 1;
geometry | osm_id | name | type | area
----------+--------+-----------+-------+-----------
| -976 | Elms Pond | water | 1609.9071
(1 row)
The ST_AsBinary
function does return a geometry for this row.
vector=# SELECT ST_AsBinary(geometry) as geometry from osm_water_areas WHERE type IN ('water', 'pond', 'basin', 'canal', 'mill_pond', 'riverbank', 'dock') AND geometry && ST_MakeEnvelope(-2.0663680475625e+07,-2.0663680475625e+07,2.0663680475625e+07,2.0663680475625e+07,3857) AND osm_id = -976;
geometry
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\x0103000000020000000f0000001f9bcbf1bfd5d0c0d8c07276148259415b2f9b4d4ad5d0c01800956d16825941e67cd9bc73d3d0c036875ec418825941ba2badb3dccfd0c093526b991b8259413fb3a173f4cbd0c076ece65c1d82594161fdbcba99c8d0c0e00c2adb1d8259414c1df6e080c6d0c0bbfa60ce1c825941a43345efcfc4d0c04d511d741b8259412beac9ead6c3d0c0be26ad071a825941ae637cd9adc7d0c0b30a8bc018825941866571e26bccd0c0d6f594571682594158df2ec576d0d0c09cbd3a89138259411553653ab8d2d0c04383b49a118259412b68911d75d4d0c0484d45e6118259411f9bcbf1bfd5d0c0d8c0727614825941050000003b2b8d75c5ccd0c0ef51d969198259411ef79220b7ccd0c0c3295e1418825941f5bcc785e5cbd0c0afe93c5918825941e6ba792808ccd0c073231c7a198259413b2b8d75c5ccd0c0ef51d96919825941
(1 row)
Hm, pretty hard to debug without setting it up myself. What is the result if you set the geometry type yourself for the provider layer?
Hey, I think that did the trick! I just went through and added geometry_type = "polygon"
to every [[providers.layer]]
that was the Tegola server was unable to determine the geometry type for at startup, and the map looks like it's loading properly.
I checked a couple lakes, and some other water areas, and the shapes look correct to me. I also isolated a geometry query that Tegola ran for one of the lakes I was checking, and the result contained a valid looking geometry.
vector=# SELECT left(ST_AsMVTGeom(geometry, ST_MakeEnvelope(-1.361924394984375e+07,4.618019500234375e+06,-1.3540972432890624e+07,4.6962910171875e+06,3857)), 20) AS geometry, osm_id, name, type, area FROM osm_water_areas_gen0 WHERE type IN ('water', 'pond', 'basin', 'canal', 'mill_pond', 'riverbank') AND area > 100000000 AND geometry && ST_MakeEnvelope(-1.361924394984375e+07,4.618019500234375e+06,-1.3540972432890624e+07,4.6962910171875e+06,3857);
geometry | osm_id | name | type | area
----------------------+---------+----------------+-------+----------------
0103000020110F000018 | -304026 | Lake Berryessa | water | 1.25422984e+08
(1 row)
So my guess is that the ST_MakeEnvelope(-2.0663680475625e+07,-2.0663680475625e+07,2.0663680475625e+07,2.0663680475625e+07,3857)
box is somehow not capturing any geometries for the layers listed above. But, as you've probably noticed, I don't know much about GIS, so take that with a grain of salt.
Awesome!
Using "mvt_postgis" also solves the original issue of missing land geometries for me.
Thanks for your help @iwpnd @ARolek !
Nice! Glad you got it working. Onward!
In case you find it interesting, I did some benchmarks on reseeding my tile cache using mvt_postgis as a provider, and rendering all tiles between zoom 0 and 9 took 5 minutes (!) on my current setup. I can't remember exactly how long it took to do the same with postgis provider, but it was somewhere in the realm of 6+ hours. Nice performance boost!
@dwoznicki nice! the postgres mvt generator / encoder is fantastic. Glad it's working well for you.
I've found at least one tile, 8/41/99, which I expect to be marked as land. However, when I view it at zoom level 8, some portion of the tile does not appear to be recognized as land.
When I zoom in a tad to zoom level 9, it appears to be recognized as land again.
This does not appear to be an issue with the https://tegola-osm-demo.go-spatial.org tiles, so I think it might be related to my instance. I'm working off an instance built from source on the v0.15.x branch. Here's my current commit.
I tried reseeding this tile with
but that does not appear to have solved the issue.