I've used PostGIS + maxmind datasets for my geolocation needs, instead of using SaaS based services, like Google's geolocation API.
PostGIS – Spatial and Geographic Objects for PostgreSQL
61–70 of 89 posts
Re: PostGIS – Spatial and Geographic Objects for PostgreSQL
#62Earlier quoted context omitted.
Is it possible you haven't added the bounding box for your geometries? The GIST index will use your bounding box to optimise the queries, but only if it finds one. Also make sure your query is actually using the index.
assuming you're using the postgis spatial data types (and not the postgres ones) it'll take care of that automatically.
Yes, I'm currently just using a single PointZ column, which is at least making some use of the index on my relevant 3DDWithin queries.
Re: PostGIS – Spatial and Geographic Objects for PostgreSQL
#63Earlier quoted context omitted.
I think it's more that the tooling has been commoditised. Historically, professional GIS was very much about Esri's tools (and, to a lesser extent, MapInfo). Your employability was directly linked to your ArcGIS proficiency. Now, there's a massive ecosystem of open-source GIS: PostGIS is probably the standout, but also QGIS, everything around OSM, GDAL/OGR, and a hundred others. For government work and some parts of…
This right here. GIS is accessible to anyone who knows Javascript, Python, and SQL thanks to the open source GIS ecosystem. Previously it was the sole domain of ESRI priests. Now this is a net good thing, but there are downsides. Generalists wielding specialist tools means that a lot of the wonky basics aren't known until things break. But this is a blip compared to the step-change of making an entire field accessibl…
Indeed. This was my experience when a nonprofit in my area set up their geomapping for Covid-19 support. There was of course some domain growth, but I was super impressed with the tooling. Even more so now that networkx and Qneat3 support other topological use cases. All and all exciting stuff.
Re: PostGIS – Spatial and Geographic Objects for PostgreSQL
#64A couple of recent and insightful podcast episodes related to PostGIS:
1. https://talkpython.fm/episodes/show/295/gis-python
2. https://changelog.com/podcast/417 (postgres)
Re: PostGIS – Spatial and Geographic Objects for PostgreSQL
#65I'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…
What kind of front end do you use. Usually the spatial db is only a part of the solution, and the GIS information needs to displayed and functionality for interaction with maps and objects needs to provided. What framework would pair well with PostGis in the open source world?
Yea the front end is kinda an open question. I've been slowly relearning OpenGL (or maybe Vulkan) for building a little 3D scatter plot like map. 2D projections could come as a feature on top of that I guess.
Mostly I just want textual information at the moment, viz is just "cool". Like, the primary question is simply, from star A to star Z how many FSD high wake jumps will I need to perform, and how much fuel will it cost. Then the pathfinding is modified to know about star class, and find valid routes with fuel scoopable stars, then the algorithm could be modified again to account for range boosting white dwarf stars, finally, it would be really cool to incorporate the game's market data, since while other third party tools already do all these things, they do not generate good trade loops.
I've been very disappointed with what little open source 3D mapping software I've seen. Everything seems highly centered around 2D projected maps. So personally, I've just been using matplotlib and it's various plotting tool with a jupyter notenook with readonly access to my database, allowing me to write %sql ... and get a python object for the resulting rows.
Re: PostGIS – Spatial and Geographic Objects for PostgreSQL
#66I'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…
You can subdivide large geometries. You can decide how much by controlling the amount of vertices a polygon can have before it’s chopped away. Intersections with large polygons will perform faster if subdivided.
Perhaps I should have noted in my initial post that the only spacial data stored at the moment is the star system's (x, y, z) position in the game's coordinate system.
Unless I'm misunderstanding, there should be nothing to cull from a point (ignoring projections)... right?
Re: PostGIS – Spatial and Geographic Objects for PostgreSQL
#67Earlier quoted context omitted.
What kind of front end do you use. Usually the spatial db is only a part of the solution, and the GIS information needs to displayed and functionality for interaction with maps and objects needs to provided. What framework would pair well with PostGis in the open source world?
Yea the front end is kinda an open question. I've been slowly relearning OpenGL (or maybe Vulkan) for building a little 3D scatter plot like map. 2D projections could come as a feature on top of that I guess. Mostly I just want textual information at the moment, viz is just "cool". Like, the primary question is simply, from star A to star Z how many FSD high wake jumps will I need to perform, and how much fuel will i…
3d is major disappointment for me as well. Lot of entrenched mindset in GIS field against 3d, I do think there is major scope for innovation in this direction.
Re: PostGIS – Spatial and Geographic Objects for PostgreSQL
#68I'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…
My suggestion might be to a precalculated step to split "the universe" into grid areas divided by geometry complexity (i.e. population density gridding) then process these reference areas in parallel. How you do the latter is probably the interesting part!
I'm not sure this is as simple as you make it sound...
Neighbors (the one example I gave) can exist across these precomputed grids, which would need to be accounted for. This is essentially what the role of the index is. So it sounds like you have the right idea, just not fully fleshed out.
For batch processing once I have a set of independent systems, I actually don't think that's the interesting part of this thread, since I could just package up the needed inputs and ship them off to their own cores for all I care. Most of the questions I care about require the relationships between these positions, i.e. Distance and derived metrics.
Re: PostGIS – Spatial and Geographic Objects for PostgreSQL
#69It's good software (I've used it more than a decade), however I found GEOS to be a sticking point. When using it on very large polygons, e.g. 10k to 1 million vertices, memory leaks are not uncommon and performance drops off considerably. Debugging SQL -> C -> C++ is not fun and hacking C++ geometry code when it's not part of your normal work is nigh on impossible. I've found the ESRI geometry API for Java to be by far the best geometry API out there. Harder to use initially and obviously JVM specific but faster and more reliable. It's a very good fit for Hadoop / Spark or other JVM applications. Ignore the brand name, I'm not affiliated and it's FOSS with an Apache license.
Re: PostGIS – Spatial and Geographic Objects for PostgreSQL
#70I'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…
there is a specific command you need to use to create a 3d index CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd); by default postgis just creates 2d ones
Indeed. It's definitely working, just not working well enough to satisfy my performance requirements in my current usage.