3 ms·

OK holy jeez I think I got the server back up. We now have 8X the ram and I'm not sure how, but we broke postgres....

ppbjj 2y ago
godspeed, soldier
et-al 2y ago
Not to sure how your backend is set up, but it looks like you're generating GeoJSON on the fly and the JSON serialization for this amount of data can be slow. In typical HN fashion, I would suggest looking into using Tippecanoe to generate some vector tiles of the data and host that on S3. Then the DB can fall back to performing simple lookups via the accident ID. Filtering by time will need to move to the frontend, but that should be fine (if not, look into clustering the data at further zoom levels). https://github.com/mapbox/tippecanoe https://github.com/mapbox/tippecanoe
- Bencarneiro 2y ago
  This is very helpful
  - wcedmisten 2y ago
    I've built quite a few open source mapping projects myself using Tippecanoe and other tools. Feel free to email me if you want help setting this up!
  - woudsma 2y ago
    You could also take a look at PostGIS to do geospatial queries (if needed). And in the past I’ve used node-mapnik to render vector/image map tiles. You can indeed host and cache them effectively on S3.
  - et-al 2y ago
    Hi Ben, briefly took another look this morning and I'm sorry for the hasty rec because I forgot Leaflet doesn't support vector tiles as easily as MapLibre/Mapbox. If the bottle-neck is the DB and you haven't enabled the PostGIS extension, I'd look into that as the other commenter mentioned. If it's your server, then yes look into pre-generating all the tiles and hosting them elsewhere, but this might then mean switching over to MapLibre/Mapbox (which is what most companies end up using because of Leaflet's limitations). For filtering on the frontend, I'd look into Expressions so you're not regenerating the GeoJSON collection every time (this causes a significant delay and flicker). - https://maplibre.org/maplibre-style-spec/expressions/ https://maplibre.org/maplibre-style-spec/expressions/ - https://maplibre.org/maplibre-gl-js/docs/examples/hover-styles/ https://maplibre.org/maplibre-gl-js/docs/examples/hover-styl...
NwtnsMthd 2y ago
> I mapped almost every USA traffic death in the 21st century Is your server on that list? j/k, I’m sorry, I’ll see myself out XD
- deleted 2y ago
  [deleted]
- mastersummoner 2y ago
  I'm not a fan of cheap puns, but that was excellent.
kazinator 2y ago
Postgres didn't look both ways before stepping off the curb.
winrid 2y ago
Remember to increase the buffer pool size to use that ram!
munch117 2y ago
I was trying out different time periods (comparing 2003-2013 to 2013-2023, to see a rough trend), and I noticed that every click to change the year by one seemed to generate a refresh. Perhaps it would help to lower the load if you change the date selector widget to only refresh after the date selector is closed.
carderne 2y ago
You’ve had some other recommendations already but I’d suggest also looking into FlatGeobuf [0] for this use case. Have a look at the MapLibre example with a 12GB example [1]. You don’t need a server at all (unlike MBTiles) and will be able to load far more points at once than your current solution. Can easily generate it with QGIS/GDAL/PostGIS. Not sure what your plans are for the project but would be happy to donate some time to get something like that working. [0] https://github.com/flatgeobuf/flatgeobuf https://github.com/flatgeobuf/flatgeobuf [1] https://flatgeobuf.org/examples/maplibre/large.html https://flatgeobuf.org/examples/maplibre/large.html
sabman 2y ago
Apologies for the shameless plug but do checkout https://geobase.app https://geobase.app We built it with such use cases in mind and solves the scaling issues with geo data driven apps. We are in private beta but happy to give you early access if you hit us up. We were also recently featured on the motherduck blog https://motherduck.com/blog/pushing-geo-boundaries-with-motherduck-geobase/ https://motherduck.com/blog/pushing-geo-boundaries-with-moth...

godspeed, soldier

Not to sure how your backend is set up, but it looks like you're generating GeoJSON on the fly and the JSON serialization for this amount of data can be slow. In typical HN fashion, I would suggest looking into using Tippecanoe to generate some vector tiles of the data and host that on S3. Then the DB can fall back to performing simple lookups via the accident ID. Filtering by time will need to move to the frontend, but that should be fine (if not, look into clustering the data at further zoom levels). https://github.com/mapbox/tippecanoe https://github.com/mapbox/tippecanoe

Bencarneiro 2y ago
This is very helpful
- wcedmisten 2y ago
  I've built quite a few open source mapping projects myself using Tippecanoe and other tools. Feel free to email me if you want help setting this up!
- woudsma 2y ago
  You could also take a look at PostGIS to do geospatial queries (if needed). And in the past I’ve used node-mapnik to render vector/image map tiles. You can indeed host and cache them effectively on S3.
- et-al 2y ago
  Hi Ben, briefly took another look this morning and I'm sorry for the hasty rec because I forgot Leaflet doesn't support vector tiles as easily as MapLibre/Mapbox. If the bottle-neck is the DB and you haven't enabled the PostGIS extension, I'd look into that as the other commenter mentioned. If it's your server, then yes look into pre-generating all the tiles and hosting them elsewhere, but this might then mean switching over to MapLibre/Mapbox (which is what most companies end up using because of Leaflet's limitations). For filtering on the frontend, I'd look into Expressions so you're not regenerating the GeoJSON collection every time (this causes a significant delay and flicker). - https://maplibre.org/maplibre-style-spec/expressions/ https://maplibre.org/maplibre-style-spec/expressions/ - https://maplibre.org/maplibre-gl-js/docs/examples/hover-styles/ https://maplibre.org/maplibre-gl-js/docs/examples/hover-styl...

NwtnsMthd 2y ago

> I mapped almost every USA traffic death in the 21st century Is your server on that list? j/k, I’m sorry, I’ll see myself out XD

deleted 2y ago
[deleted]
mastersummoner 2y ago
I'm not a fan of cheap puns, but that was excellent.

kazinator 2y ago

Postgres didn't look both ways before stepping off the curb.

winrid 2y ago

Remember to increase the buffer pool size to use that ram!

munch117 2y ago

I was trying out different time periods (comparing 2003-2013 to 2013-2023, to see a rough trend), and I noticed that every click to change the year by one seemed to generate a refresh. Perhaps it would help to lower the load if you change the date selector widget to only refresh after the date selector is closed.

carderne 2y ago

You’ve had some other recommendations already but I’d suggest also looking into FlatGeobuf [0] for this use case. Have a look at the MapLibre example with a 12GB example [1]. You don’t need a server at all (unlike MBTiles) and will be able to load far more points at once than your current solution. Can easily generate it with QGIS/GDAL/PostGIS. Not sure what your plans are for the project but would be happy to donate some time to get something like that working. [0] https://github.com/flatgeobuf/flatgeobuf https://github.com/flatgeobuf/flatgeobuf [1] https://flatgeobuf.org/examples/maplibre/large.html https://flatgeobuf.org/examples/maplibre/large.html

sabman 2y ago

Apologies for the shameless plug but do checkout https://geobase.app https://geobase.app We built it with such use cases in mind and solves the scaling issues with geo data driven apps. We are in private beta but happy to give you early access if you hit us up. We were also recently featured on the motherduck blog https://motherduck.com/blog/pushing-geo-boundaries-with-motherduck-geobase/ https://motherduck.com/blog/pushing-geo-boundaries-with-moth...