Mapbox vector tiles from 1GB table rendering very slowly

[iskandari]

I am connecting a table in my PostgreSQL database, which was created from a shapefile that is almost 1 GB in size (containing detailed land parcel polygons). When I use the sample code provided in the tutorial requiring tilesplash and redis, the tiles render slowly, and at lower zoom levels most tiles do not render at all. Is there a way to render mvt tiles more efficiently?

app.layer('parcel06_proj', userMiddleware, function(tile, render){
  if (tile.z < 5 || tile.z > 20) {
    render.empty();
  } else {
  render(
    {      
      pol : "SELECT ST_AsGeoJSON(geom) as the_geom_geojson, parcel06_proj.* FROM parcel06_proj WHERE ST_Intersects(geom, !bbox_4326!)"
    });
}
});
<img width="1177" alt="screen shot 2016-11-18 at 5 36 46 pm" src="https://cloud.githubusercontent.com/assets/8308461/20449185/dc181d98-adb5-11e6-9f7e-a0a6905dc78d.png">
<img width="1177" alt="screen shot 2016-11-18 at 5 36 46 pm" src="https://cloud.githubusercontent.com/assets/8308461/20449196/ea6e9278-adb5-11e6-8e6e-ef4c12b46594.png">

app.server.listen(3000);

var map = new mapboxgl.Map({
    container: 'map', // container id
    style: 'mapbox://styles/mapbox/dark-v9',
    center: [-96.80011, 32.7817035], 
    zoom: 14 
});

map.on('load', function() {
    map.addSource('dallas', {
        type: 'vector',
        tiles: ['http://localhost:3000/parcel06_proj/{z}/{x}/{y}.mvt'],
        "minzoom": 5,
        "maxzoom": 19
    });
    map.addLayer({
        "id": "polygons",
        "type": "fill",
        "source": "dallas",
        "source-layer": "pol",
        "paint": {
            "fill-color": "#3887be"
        }
    });
});

[iskandari]

[fxi]

I'm not an expert, but here is some starting points:

• You can use app.logLevel("debug") to get some info about the process.
• What does userMiddleware ? There is maybe an issue in that step.
• Maybe adding every columns to your tiles is not a good idea. If there is a huge number of variables, it could be slow to process somewhere in postgis / mapnik / network / mapboxgl. If you need them client side, you could do something server side on demand.
• Maybe some optimisation in postgis could do the trick:

SELECT ST_AsGeoJSON(
ST_SimplifyPreserveTopology( geom, (SELECT (1000/(512*2^!z!+1)))), 7 ) AS the_geom_geojson,
FROM parcel06_proj
WHERE geom && !bbox_4326!
  AND ST_Intersects(geom, !bbox_4326 !);

[summer4096]

Hey.

This bit right here

ST_Intersects(geom, !bbox_4326!)

should be replaced with

geom && !bbox_4326!

These two lines will do the same thing, but ST_Intersects adds additional overhead on each row for fancy geospatial math, which isn't actually necessary here since the bbox is just a rectangle. && is a bounding box comparison and an index operator.

Next, make sure you have a gist index on geom. You can create one like so:

CREATE INDEX ON parcel06_proj USING gist (geom);

Based on your bad performance I think this is probably the missing piece. Indexes are everything.

If you still have poor performance after this, I'd go with @fxi's suggestions: don't pull so many variables, and simplify your geometry if it's complex.

[fxi]

In the example below, the difference between ST_intersects and && is that ST_intersects will return false but && true. This is why I've put both in my suggested query : first quick bbox search using &&, then exact intersects. But maybe I'm wrong and && is enough. In all case, the result is an empty tile. What do you think ?

[summer4096]

Ah, you're right. In my case, the additional overhead of having the occasional polygon sent down twice is generally worth it, as those are stripped out on the client side. ST_Intersects might be fine, or even better, for your use case. The speed of it really depends on how many vertices these polygons have. My hope is that adding the correct index should solve your problem, and switching to && wouldn't really be necessary in that case. Using && is an optimization we run because we're querying a very large table.

[iskandari]

Thanks for these helpful tips. How would one go about writing a query that would pull only the geometry data (in this case, wkb_geometry) and one or two of the columns, so as to minimize the overhead? The unique id column in my table is called RELKEY

My aim is to speed up the rendering by pulling only geometry and a unique id column, then once a specific parcel is selected, to look up the other data using the unique key from the PostGIS table when it is needed and display it in a panel

The geojson file that I am working with is here.

Thanks again for your time

[summer4096]

-- turn this
SELECT ST_AsGeoJSON(geom) as the_geom_geojson, parcel06_proj.*

-- into this
SELECT ST_AsGeoJSON(geom) as the_geom_geojson, parcel06_proj.RELKEY