Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostGIS – Spatial and Geographic Objects for PostgreSQL

postgis.net

Post reply on HN
Print view View original

71–80 of 89 posts

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#71

Post by nixpulvis »

jhgb wrote: post #14
nixpulvis wrote: post #4

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…

> 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. Doesn't that sound a bit pathological? It should be nowhere near that slow. Try implementing a VP tree over that data.

I mean, I'm using the provided GiST-based R-Tree spatial index... Otherwise, I'm not really making any use of PostGIS and might as well look for other spacial DBs or write my own little data-structure like you mention (thanks for the link to VP-trees btw).

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

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#72

Post by nixpulvis »

7952 wrote: post #10
nixpulvis wrote: post #4

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…

You could use a clustered index

Interesting, I think I read something about this before... I'll have to make a point to actually give this a go.

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#73

Post by nixpulvis »

yxhuvud wrote: post #11
nixpulvis wrote: post #4

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…

Hmm. That sounds like a lot of time. How does your query look like for that?

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

    "systems_position_idx" gist ("position" gist_geometry_ops_nd)

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#74

Post by alexhutcheson »

tcbasche wrote: post #22
gardaani wrote: post #6

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.

Thanks for sharing this! I've been thinking about alternatives to PostGIS to handle larger datasets (millions) and nothing seems to come close to the level of functionality, performance and community support of Postgres

If you really need to scale beyond what Postgres/PostGIS can handle, then you might want to check out GeoMesa[1], which is (very loosely) "PostGIS for HBase, Cassandra, or Google BigTable".

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

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#75

Post by nixpulvis »

billfruit wrote: post #67
nixpulvis wrote: post #65

Earlier quoted context omitted.

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.

I find that somewhat hilarious actually... Like what do GIS people think, we live on a flat earth?

Seriously though, if all you ever want to do is draw 2D maps, I can see optimizing these cases. I can even maybe understand how it's best to nail these features down first... However, it just seems like so many of the tools data models are corrupted by a fundamentally mishandling of 2D vs 3D.

For example, a library I'm using for WKB encoding/decoding pollutes my 3D points with a bunch of functions for dealing with 2D points that I frankly want nothing to do with. Why should I ever want a function on a 3D point to return a 2D point with an optional third member. I can see how this might help you embed a 2D point inside a 3D space, by treating the null value as 0, or filtering it, or some other user defined or standard logic... but if I have a 3D point, why on earth am I casting it to a partially optional 3D point.

That was just an example that's been really bothering me... there's lots of other examples of 2D driven features looking a bit strange in 3D.

Not to mention adding an M dimension, or outside PostGIS, any others. The difficulty with this problem is the immense, vast, epic scale of the issue. I want to say you could probably just define the 3D metric space and then build the 2D one of that, but then why stop there. Perhaps I want a 5D space with distance measured as some similarity metric... is this not starting to sound like a more general problem than GIS?

And another thing! It would be very interesting to think about what aspects of the spacial reference are useful in 3D. I'm still learning about how the SRIDs are used in PostGIS, but this [1] example makes a lot of sense for lat/long references.

[1]: http://www.bostongis.com/blog/index.php?/archives/266-geogra...

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#76

Post by Sn0wCoder »

dbreunig wrote: post #59
Doctor_Fegg wrote: post #53

Earlier 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…

I was doing ArcGis about 10 years ago and it was amazing the quality of the free maps MN data deli provided. Your comment makes me excited to try out these new tools even tho I mainly do front end work now. It’s a great time to be a programmer!

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#77

Post by dbreunig »

Sn0wCoder wrote: post #76
dbreunig wrote: post #59

Earlier quoted context omitted.

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…

I was doing ArcGis about 10 years ago and it was amazing the quality of the free maps MN data deli provided. Your comment makes me excited to try out these new tools even tho I mainly do front end work now. It’s a great time to be a programmer!

Lucky for you, it's also a golden age for frontend GIS. I'm a big fan of https://vis.gl/

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#80

Post by atonse »

motiejus wrote: post #5

I am writing an open-source line simplification algorithm[1] with PostGIS. So far it's a few hundred lines of SQL-like code[2], but it will grow. I have never written anything serious in plpgsql, so this is my first non-trivial project. Writing in PostGIS is a strange mix of SQL (fully declarative) and a "real" programming language with variables, arrays, functions and loops. The mix is very interesting and requires…

Regarding deep copies, have you considered writing it as a plugin in something like rust?

https://github.com/zombodb/pgx

Post reply on HN