pg_tileserv: Clipping in ST_AsMVTGeom crashes to 0-dimension geometry
Hi, Sometimes pg_tileserv returns error like ERROR: lwcollection_construct: mixed dimension geometries: 2/0 SQL state: XX000
I found the crashed query with pg_stat_activity:
SELECT ST_AsMVT(mvtgeom, 'reitti.tieosoiteverkko', 4096, 'geometry') FROM ( SELECT ST_AsMVTGeom( ST_Transform(t."geometry", 3857), bounds.geom_clip, 4096, 256 ) AS "geometry" , "id" FROM "reitti"."tieosoiteverkko" t, ( SELECT ST_MakeEnvelope(3.0525891615967937e+06, 8.531595349078214e+06, 3.130860678560812e+06, 8.609866866042234e+06, 3857) AS geom_clip, ST_MakeEnvelope(3.0476971917865425e+06, 8.526703379267963e+06, 3.135752648371063e+06, 8.614758835852485e+06, 3857) AS geom_query ) bounds WHERE ST_Intersects(t."geometry", ST_Transform(bounds.geom_query, 3067)) LIMIT 10000 ) mvtgeom
It seems that the clipping in ST_AsMVTGeom creates ‘GeometryCollection Empty’. I found them by querying
SELECT ST_AsText(ST_Transform(t."geometry", 3857)) AS "original_geometry", ST_AsText( ST_Intersection(ST_Transform(t."geometry", 3857),bounds.geom_clip)) AS "geometry" FROM "reitti"."tieosoiteverkko" t, ( SELECT ST_MakeEnvelope(3.0525891615967937e+06, 8.531595349078214e+06, 3.130860678560812e+06, 8.609866866042234e+06, 3857) AS geom_clip, ST_MakeEnvelope(3.0476971917865425e+06, 8.526703379267963e+06, 3.135752648371063e+06, 8.614758835852485e+06, 3857) AS geom_query ) bounds WHERE ST_Intersects(t."geometry", ST_Transform(bounds.geom_query, 3067)) AND ST_Dimension(ST_Intersection(ST_Transform(t."geometry", 3857),bounds.geom_clip)) != 0 LIMIT 10000
ST_Dimension returns 0 for those empty collections.
The query is generated here in code: https://github.com/CrunchyData/pg_tileserv/blob/220bc68e42dda153db08c9b4f6890a7c11c0a6d5/layer_table.go#L386-L404
I found a way to fix this, by extending WHERE-clause with
AND ST_Dimension(ST_Intersection(ST_Transform(t."geometry", 3857),bounds.geom_clip)) != 0
As a downside, it makes the query slow, as clipping is performed twice. Probably there is some better way to fix it, but I don’t know if the fix should be done in PostGIS to ST_AsMVTGeom-function
About this issue
- Original URL
- State: closed
- Created 4 years ago
- Comments: 20
I’ve pulled this to a PostGIS ticket here https://trac.osgeo.org/postgis/ticket/4690