Data is offsetting in low zoom levels

[mapsgeek]

i wanted to give the mvt endpoint a shot after the last query optimization video, the performance is great i barley notice any CPU usage ! but there's a problem in rendering data at zoom levels before 7
tried with multiple data sources imported using db manager in qgis, targeted SRID 4326.
data is rendered offset-ed and distorted at zoom level 0,1,2 with each zoom level you hit the offset goes back to right place until zoom level 7
can you please see if you can replicate this ?

*** system running postgresql 10.6 and postgis 2.5

[tobinbradley]

I can simulate this behavior (red is the geojson route, showing up where it should be, for natural earth countries).

This is a really weird bug that seems to resolve completely at zoom 10 and gets progressively worse as you zoom out. I don't think it's anything I'm doing in the code - using PostGIS's sample code with a simple shape shows the same problem:

return `SELECT ST_AsMVT(q, '${params.table}', 4096, 'geom') FROM (
    SELECT 
    ST_AsMVTGeom(
      ST_GeomFromText('POLYGON ((35 10, 45 45, 15 40, 10 20, 35 10), (20 30, 35 35, 30 20, 20 30))'),
      ST_MakeBox2D(ST_Point(${bounds[0]}, ${bounds[1]}), ST_Point(${bounds[2]}, ${bounds[3]})), 
      4096, 
      0, 
      false
    ) AS geom
) AS q;
  `
// same problem - makes shape that starts moving/distorting North @ zoom < 10

After zoom level 10 and higher things seem ok. It also happens to points (natural earth populated places) in the exact same way, which means it isn't a feature complexity problem.

This smells like a PostGIS bug (I'm on Postgres 11/PostGIS 2.5), but I'll dig more.

[mapsgeek]

thanks for taking the time to look at this, sorry i don't have enough knowledge to debug this further maybe one day i will be able to make a better contribution than just reporting using issues

[tobinbradley]

Letting me know about these things is an invaluable service. As I work with a local municipality, I never tested on the scales you are, so I never saw this bug.

I think I have it fixed, though with a caveat. I found a post where somebody had the same problem. It appears to be an issue with converting to 4326 instead of 3857. Now there's no jumping about.

It can, however, create another problem. 3857 doesn't like features with latitude 0 see this issue, as in 3857 0 latitude seems to be like infinity places or something. I noticed my Natural Earth countries would error on the tiles that would hit the bottom of the map as I had them stored in 4326. I projected them to 3857 in QGIS, and immediately noticed the bottom of the map was shortened. Loading that into PostGIS and drawing from there seemed to do the trick.

So...better-ish.

[tobinbradley]

I confirmed what the other problem was.

If you try to create a tile with features that sit outside of the extent of web mercator (-180.0 -85.06 180.0 85.06 - see projection info), the tile won't render. This makes sense - it's trying to do a ST_Transform to EPSG:3857, and the coordinates you are passing don't exist in EPSG:3857. You'll get an error message to that effect.

You may not run in to that issue at all. If you do, you have two options: you can project the data to 3857, which should take care of things, or you can clip the data to the boundaries of 3857, ala something like:

update countries
set geom = st_multi(
	st_intersection(
		geom, 
		ST_MakeEnvelope(-180.0, -85.06, 180.0, 85.06, 4326)
	)
)

Note either of those are destructive changes, so don't try it on data you don't have backups of. I ran the SQL on Natural Earth countries, and it fixed the issue of tiles that touch the south pole not rendering.

Thanks for letting me know about the problem!

[mapsgeek]

i can confirm that you will run into rendering issues with 3857 and that boundaries clip is necessary to fixes all rendering issues but now performance hits are back

If your data is in 4326 and the query do transform to 3857, clip has executed as you suggested data renders fine but performance is bad like before, very high CPU usage with 500ms ~ 1.5 second response time on localhost ( tested in chrome ) with multi-string geom and multipolygon, depends on the geometry complexity, fine with points and natural earth 110m it's alright, try with countries 10m and you will see the hit !
which i thought that the query optimization has fixed, and wasn't there actually before your last commit to fix the jumping issue.

[tobinbradley]

I don't think that's a projection issue - seems the same with that shape file in it's native 4269 or projected to 3857. That's just a lot of fairly complex data to try and draw at that scale on the fly. It goes fast zoom > 9 - tiles render in <200ms (that's projecting to 3857 on the fly). As you support lower zoom levels, the tiles get much bigger, and it naturally slows down. More data in a tile will directly translate in to more time making the tile. The SQL query still shows as using the spatial index.

If you really want to draw that at very low zoom levels, you could try to simplify it (greatly) beforehand. This seemed to speed it up a lot, and I couldn't tell much of a difference at that scale:

  update census_urban_3857
  set geom = ST_Multi(ST_SimplifyPreserveTopology(geom, 10000))

At zoom 4 this has the slowest tile coming in ~700ms for me.

But for something that changes so infrequently, I'd stick with tippecanoe. No sense thrashing your database if you don't need to. I'd reserve direct database feature calls for things that change often enough to make tippecanoe a pain, and then tune that data for performance accordingly.

[mapsgeek]

yeah that's what it's all about our data changes frequently and it's global data so low zoom levels are crucial and i wanted to get ride of the Tippecanoe process but seems like i will have to stick with it then, thank you