search

chargetrip / clusterbuster

A Mapbox Vector Tile (MVT) map tiling server with built-in clustering and filtering
MIT License
92 stars 18 forks source link

Update documentation to include instructions for spatial indexing of points in srid 3857 #82

Open chriszrc opened 2 years ago

chriszrc commented 2 years ago

Given that all the point intersection queries are executed with srid of 3857:

https://github.com/chargetrip/clusterbuster/blob/a47d2469e208a730b8e06dd4d189cce3a1b9059a/lib/queries/base.ts#L16-L24

A standard spatial index on the point geometry column won't be used (unless maybe the points are stored in 3857, but most I suspect are probably 4326). However regardless of srid on the geometry column, we can always add an expression (functional) index:

CREATE INDEX mytable_geom_3857_idx on mytable USING gist (ST_Transform(geom, 3857));

Which will of course speed up ST_INTERSECTS queries for all the tile requests.

PS - Was https://github.com/datalanche/node-pg-format or similar considered to more safely create parameterized queries?

cosmin-petrescu commented 2 years ago

Hello @chriszrc . Do you want to create a PR for this issue. We are more than happy to review it and merge it.