MVT_Postgis vs postgres tile difference, checkerboarding when styled

[KCC-IT-Admin]

I've attempted to migrate our tegola setup to mvt_postgis to capitalize on the performance improvements, and resolve some weird blank spots in maps, but am instead getting strange checkerboarding across tiles when I attempt to style.

[[providers.layers]]
name = "us_adm1"
tablename = "public.us_adm1"
geometry_type = "multipolygon"
geometry_fieldname = "geom"
id_fieldname = "gid"
sql = 'SELECT "gid", ST_AsMVTGeom(ST_Transform(geom, 3857),ST_Transform(!BBOX!, 3857)) AS geom, "adm0code", "adm1code", "name" FROM public.us_adm1 WHERE geom && !BBOX!'

I don't get this banding with my regular format, but instead get weirdly cut polgyons that leave gaps. My usual config section for the same is this:

[[providers.layers]]
name = "us_adm1"
geometry_type = "MultiPolygon"
sql = "SELECT name, gid, adm1code, adm0code, ST_AsBinary(geom) AS geom FROM public.us_adm1 WHERE geom && !BBOX!"

[ARolek]

@KCC-IT-Admin what projection are your geometries in? It looks like you're trying to reproject to 3857 on the fly using ST_Transform(), which will work but is going to be much slower. Anyways, I think the issue is you're not reprojecting the second !BBOX! token:

Change:

WHERE geom && !BBOX!

To

WHERE geom && ST_Transform(!BBOX!, 3857)

[KCC-IT-Admin]

yes, our data is all in 4326.

Would I need to transform the geom as well as the BBOX?

[KCC-IT-Admin]

OK- quick update, I ran the updated query as written, and got blank tiles- I ran it after transforming the geom & bbox data and got workable tiles, but the same checkerboard issue persisted.

[ARolek]

@KCC-IT-Admin can you please post your update query?

If all your data is in 4326, try the following query:

sql = "SELECT ST_AsMVTGeom(geom, ST_Transform(!BBOX!,4326)) AS geom, gid FROM gis.landuse WHERE geom && ST_Transform(!BBOX!,4326)"

Alternatively, you can set the srid field on in the config. I left a comment next to the change:

[[providers.layers]]
name = "us_adm1"
tablename = "public.us_adm1"
geometry_type = "multipolygon"
geometry_fieldname = "geom"
id_fieldname = "gid"
srid = 4326 # this will transform the !BBOX! token to the 4326 projection
sql = 'SELECT "gid", ST_AsMVTGeom(geom, !BBOX!) AS geom, "adm0code", "adm1code", "name" FROM public.us_adm1 WHERE geom && !BBOX!'

[KCC-IT-Admin]

Correction, our data is all in 4326, but the tiles being output should be in 3857

[ARolek]

@KCC-IT-Admin ok. I think you're close. Did you find the documentation I wrote up around this situation?

https://github.com/go-spatial/tegola/tree/master/mvtprovider/postgis#example-mvt_postgis-and-map-config-for-srid-4326

[KCC-IT-Admin]

I did, yes - I was using a query based off your initial documentation before we started this. the SRID is set on the provider as 4326.

Your Sample was
sql = "SELECT ST_AsMVTGeom(ST_Transform(geom, 3857),ST_Transform(!BBOX!,3857)) AS geom, gid FROM gis.landuse WHERE geom && !BBOX!"

and I was using
sql = 'SELECT "gid", ST_AsMVTGeom(ST_Transform(geom, 3857),ST_Transform(!BBOX!, 3857)) AS geom, "adm0code", "adm1code", "name" FROM public.us_adm1 WHERE geom && !BBOX!'

I've tried it with GID before and after the geometry in case it was an issue with that.

I'm thinking there's something different with how the mvt creates tiles vs how tegola creates them that makes shapes that spread across multiple tiles sometimes style differently

[ARolek]

@KCC-IT-Admin can you confirm that you have added the srid to the provider toml config? The original toml you posted did NOT have the srid attribute set. The TOML should look like the following:

[[providers.layers]]
name = "us_adm1"
tablename = "public.us_adm1"
geometry_type = "multipolygon"
geometry_fieldname = "geom"
id_fieldname = "gid"
srid = 4326 # is this set in your config?
sql = 'SELECT "gid", ST_AsMVTGeom(ST_Transform(geom, 3857),ST_Transform(!BBOX!, 3857)) AS geom, "adm0code", "adm1code", "name" FROM public.us_adm1 WHERE geom && !BBOX!'

To break down what's happening here:

• tegola is receiving a z/x/y tile request and computing a bounding box for the tile. The resulting polygon is in the 4326 projection (because the srid is set in the provider config).
• PostGIS is using the 4326 version of the !BBOX! in the WHERE goem && !BBOX! statement to determine which geometries intersect with the tile bounds.
• PostGIS is then Transforming the geometries that intersect with the tile bounds to the 3857 projection. It's also transforming the !BBOX! from 4326 -> 3857 in the ST_AsMVTGeom() operation so the geometry and tile coordinate space line up. This makes the tiles returned from PostGIS compatible with a web mercator based UI rendering system (i.e. Mapbox, MapLibre).

It's nuanced, but if you get all these params correctly configured then it should work for you. Also, if you want to jump into our Slack, I can try to give you some support there: http://slack.go-spatial.org/