PostGIS – Spatial and Geographic Objects for PostgreSQL

That being said, you may not need it, because Postgres/PostGIS can scale vertically to handle larger datasets than most people realize. I recommend loading your intended data (or your best simulation of it) into a Postgres instance running on one of the extremely large VMs available on your cloud provider, and running a load test with a distribution of the queries you'd expect. Assuming the deliberately over-provisioned instance is able to handle the queries, you can then run some experiments to "right-size" the instance to find the right balance of compute, memory, SSD, etc. If it can handle the queries but not at the QPS you need, then read replicas may also be a good solution.

[1] https://github.com/locationtech/geomesa

In the end we used this simplified calculation with added „regions“. So the world was split into 15km*15km squares, so any square being more than x apart could never be in the result set. This could maybe be used with modern postgresql partitioning and partition elimination in a clever way.

And without partitioning maybe clever z-ordering the entries physically in the database (clustering) could reduce a lot of random i/o.

Sometimes the issue can be tweaked by using intersection versus overlap/contains/contained when possible.

    SELECT
        address,
        name,
        position,
        population,
        security,
        government,
        allegiance,
        primary_economy,
        secondary_economy,
        updated_at
    FROM systems
    WHERE ST_3DDWithin(position, $1, $2);

    "systems_position_idx" gist ("position" gist_geometry_ops_nd)

I was hoping that the performance of PostGIS's features could be improved with some know how.

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.

Unless I'm misunderstanding, there should be nothing to cull from a point (ignoring projections)... right?

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.

Thanks for the tip!

Still can't plot from Sol to Colonia with a range of 100 Ly (near the very dense center of the galaxy) in under 10 seconds though. But I'm aware of some larger issues in the search algorithm itself that are probably my next task on this journey. The performance of this index feels closer to what I think I was expecting to see on it's own.

https://github.com/statsbomb/open-data

Interesting stuff from Metrica too:

https://github.com/metrica-sports/sample-data

There are other less legit sources that you may be able to work out for yourself. :)

Of course, happy to acknowledge that different tools might work better in different scenarios. For example, I suspect that speed of querying is really just due to the data being in memory so if you can configure Spatialite or PostGIS to do the same, I certainly wouldn't be surprised if you say you can do even better.

But for one-off analyses, it's common to spend a lot of time just getting your data into the right shape, doing various manipulations, perhaps even wrangling the geometries. For that, working entirely within SQL is frustrating as heck. For example, I did an analysis on flight paths over heavily populated areas once, which involved turning infrequent point locations with gaps in the data into a smooth interpolated flight path. That's easy if you have numpy and scipy at your disposal, otherwise it's not. Another analysis involved estimating housing prices in neighborhoods without any recent sales, from prices in adjacent neighborhoods with sales, and again it's easy to code up an algorithm to fill the gaps or to run a geostatistical analysis that can impute the missing values, but not if all you have is SQL, or if you have to constantly do roundtrips between database and code.

I mention all this not to start an argument, but simply because when I first started doing GIS work, I was very confused about what the right tools and workflow were, and once I embraced projections (vs. working directly with spheroids) and in-memory analysis in Python, my productivity went way up. If other people find themselves in the same scenario, they owe it to themselves to try out both approaches to see what works best for them.