This PR Improves the efficiency of the bounding box and vector tile queries:
the tileid in the vector tile route now references the original query, not a list of projectids. This avoids the very long WHERE projectid IN (...) queries that we were using before in the vector tiles
the tileid query is only cached and the bounds are only calculated if page === 1, so we are not wasting resources making duplicative tilesets on subsequent pages of the same query.
the vector tile query itself now uses a Common Table Expression for calculating the tile bound geometry using ST_MakeEnvelope(). This allows postGIS to take advantage of the spatial index on project_centroids, reducing tile generation for the full 28k projects query from ~17 seconds to ~1 second!
This PR Improves the efficiency of the bounding box and vector tile queries:
tileidin the vector tile route now references the original query, not a list of projectids. This avoids the very longWHERE projectid IN (...)queries that we were using before in the vector tilestileidquery is only cached and the bounds are only calculated ifpage === 1, so we are not wasting resources making duplicative tilesets on subsequent pages of the same query.ST_MakeEnvelope(). This allows postGIS to take advantage of the spatial index onproject_centroids, reducing tile generation for the full 28k projects query from ~17 seconds to ~1 second!