11–20 of 89 posts
I've been using PostGIS a bit for a toy project with Elite: Dangerous star system data. It's been a hoot, but I do worry that I'm going to start having trouble optimizing my queries. Finding all the systems within say 20 Ly of our sun can take upwards of a few seconds, and I've already added a GIST index on the positions. I also find it a bit strange how 3D feels kinda tacked on, but it makes sense when you realize m…
And since it was charity and had a bunch of private data Google was not an option ($$$$), so I (just a full-stack developer back then) was like "listen I have no idea what is this GIS stuff, but I'll give it a try", after a quick research boom PostGIS, reading the docs and testing things, plus QGIS to visualize and to help understand it better 10/10!
That was my unintentional "career" shift, thanks to PostGIS I'm now a senior dev at the largest food delivery company (local), specialized in GIS and realtime data driven systems using PostGIS everyday lol
Earlier quoted context omitted.
The only thing I can recommend off hand is to try and simplify your geometries a bit for searching if they’re complex. Searching along the border of a complicated shape is much harder than searching around a square, it has to do a lot more calculations.
Yes had the same solution ~15 years ago when location based search got popular. Searching for anything within some distance to a defined location with perfect earth projection and a circle radius „did not scale“. Ignoring the earth‘s projection and pretending a flat earth with a rectangle search was much much faster. In the end we used this simplified calculation with added „regions“. So the world was split into 15km…
I've been using PostGIS a bit for a toy project with Elite: Dangerous star system data. It's been a hoot, but I do worry that I'm going to start having trouble optimizing my queries. Finding all the systems within say 20 Ly of our sun can take upwards of a few seconds, and I've already added a GIST index on the positions. I also find it a bit strange how 3D feels kinda tacked on, but it makes sense when you realize m…
Doesn't that sound a bit pathological? It should be nowhere near that slow. Try implementing a VP tree over that data.
I've been using PostGIS a bit for a toy project with Elite: Dangerous star system data. It's been a hoot, but I do worry that I'm going to start having trouble optimizing my queries. Finding all the systems within say 20 Ly of our sun can take upwards of a few seconds, and I've already added a GIST index on the positions. I also find it a bit strange how 3D feels kinda tacked on, but it makes sense when you realize m…
The only thing I can recommend off hand is to try and simplify your geometries a bit for searching if they’re complex. Searching along the border of a complicated shape is much harder than searching around a square, it has to do a lot more calculations.
Sometimes the issue can be tweaked by using intersection versus overlap/contains/contained when possible.
I've been using PostGIS a bit for a toy project with Elite: Dangerous star system data. It's been a hoot, but I do worry that I'm going to start having trouble optimizing my queries. Finding all the systems within say 20 Ly of our sun can take upwards of a few seconds, and I've already added a GIST index on the positions. I also find it a bit strange how 3D feels kinda tacked on, but it makes sense when you realize m…
Here's an interesting performance comparison of PostgreSQL with PostGIS and MongoDB. PostgreSQL outperformed MongoDB in almost all their cases. https://link.springer.com/article/10.1007/s10707-020-00407-w I've decided to use PostgreSQL in my projects.
FWIW, even though PostGIS is pretty great, if your use-case is primarily offline analysis and you don't need the data to be permanently accessible or writable, consider not using a database at all. You can do a lot, a lot faster with e.g. https://shapely.readthedocs.io/en/stable/manual.html and/or https://geopandas.org/ .
FWIW, even though PostGIS is pretty great, if your use-case is primarily offline analysis and you don't need the data to be permanently accessible or writable, consider not using a database at all. You can do a lot, a lot faster with e.g. https://shapely.readthedocs.io/en/stable/manual.html and/or https://geopandas.org/ .
I'd argue that being able to use sql to more easily combine ans filter datasets, including non-geospatial ones is still very useful in the circumstances you described.