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

81–89 of 89 posts

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#81

Post by nucleardog »

simonw wrote: post #38
tcbasche wrote: post #22

Earlier quoted context omitted.

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

Millions is a pretty small dataset these days. I'm increasingly of the opinion that the boundary for considering "big data" tools starts at about 100 million rows.

I'd put it higher, depending on your dataset, usage patterns, etc.

This is kinda the hill I keep nearly dying on at work. Team X wants to spend months investigating and deploying BigDataToolY because "we have big data". This is not our core business or core competency. I tell them to dump the data into Postgres and just see how it performs out of the box so we can get back to working on stuff that matters. They don't, I do, we end up using Postgres.

We had one team ignore the advice and go straight to Redshift (which is a warehousing product and totally inappropriate for their use case; but they ignored that advice too). When they finished and then woke up to the reality that Redshift wasn't going to work, I literally just dumped their data into a plain vanilla Postgres instance and pointed their app at it and... everything worked out of the box. That was ~1b rows in a single poorly-modeled table.

Another team was certain that Postgres could never work and was looking to build out a solution around BigQuery, Firestore, and some other utilities to pre-process a bunch of data and pre-render responses. One of our main products was operating in a several degraded state while they spent months on "research" before deciding this would take about four more months to implement. So I dumped all the data into a Postgres instance (using TimescaleDB) and it... worked out of the box. The current data is a few billion rows across half a dozen tables (the bulk of the data in a single table), but I'd tested to 4x the data without any significant performance degradation.

These are just a couple "notable" examples, but I've done this probably a dozen times now on fairly sizeable revenue-generating real-world products. Often I'm migrating this data _off_ of big data solutions which are performing poorly due to either the team's lack of knowledge and experience to use them properly or the tool having been the wrong one to use in the first place.

I've yet to have to even have the team model their data properly. Usually just a lift and shift into Postgres solves the problems.

I've told every one of these teams "We'll use Postgres until it stops working or starts getting needy, _then_ we'll look at the big data tools.". I've yet to migrate any of these datasets back _off_ of Postgres.

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#82

Post by fdr »

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, I think you are looking for SP-GiST (unbalanced space partitioned GiST), unless you meant that implicitly. It can be much faster for...space partitioning.

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#83

Post by tcbasche »

alexhutcheson wrote: post #74
tcbasche wrote: post #22

Earlier quoted context omitted.

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

Yeah at my current job we run RDS in AWS and scale it up to a m5.12xlarge when we need to get sh*t done fast. It normally sits around a 4xlarge simply because the 12 is far too expensive.

I'll check out GeoMesa though, looks interesting!

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#84

Post by simonw »

nucleardog wrote: post #81
simonw wrote: post #38

Earlier quoted context omitted.

Millions is a pretty small dataset these days. I'm increasingly of the opinion that the boundary for considering "big data" tools starts at about 100 million rows.

I'd put it higher, depending on your dataset, usage patterns, etc. This is kinda the hill I keep nearly dying on at work. Team X wants to spend months investigating and deploying BigDataToolY because "we have big data". This is not our core business or core competency. I tell them to dump the data into Postgres and just see how it performs out of the box so we can get back to working on stuff that matters. They don't…

Love this comment.

I've promoted the idea in the past that if you're going to use something other than PostgreSQL (or MySQL if that's the DB that's already embedded) you need to PROVE that what you need to build can't work with that standard relational database before adopting some new datastore.

It's surprisingly hard to prove this. The most common exception is anything involving processing logs that generate millions of new lines a day, in which case some kind of big data thing might be a better fit.

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#85

Post by nixpulvis »

fdr wrote: post #82
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, I think you are looking for SP-GiST (unbalanced space partitioned GiST), unless you meant that implicitly. It can be much faster for...space partitioning.

Oh damn, I don't know how I missed that section of the docs. Indeed, though I haven't really profiled this, an SP-GiST index does seem quite a bit faster for me.

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.

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#86

Post by motiejus »

atonse wrote: post #80
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

If it is C or plpgsql, my algorithm has a chance to be included to upstream postgis. If it's rust, not a chance.

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#87

Post by stdbrouw »

jononor wrote: post #54
stdbrouw wrote: post #18

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/ .

Do those assume/require that all the data will fit into memory?

Mostly yes, though you can wing it a little bit by relying on swap and/or doing any big filtering operations up front. In my work, it's never been a problem, because you can cram a lot of data into 16 GB, millions of big polygons and metadata. Note also that textual formats like GeoJSON or WKT are incredibly wasteful of space because all coordinates are encoded as characters instead of floats or integers, whereas the in-memory representation is much smaller, so even huge source files are likely to fit in memory just fine. But judging by the sibling comments it does look like it's a limitation for some.

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#88

Post by yxhuvud »

nixpulvis wrote: post #73
yxhuvud wrote: post #11

Earlier quoted context omitted.

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)

Ok, I see no obvious ways that is suboptimal. I assume there are no difference if you switch places of position and $1?

Re: PostGIS – Spatial and Geographic Objects for PostgreSQL

#89

Post by fdr »

nixpulvis wrote: post #85
fdr wrote: post #82

Earlier quoted context omitted.

Hmm, I think you are looking for SP-GiST (unbalanced space partitioned GiST), unless you meant that implicitly. It can be much faster for...space partitioning.

Oh damn, I don't know how I missed that section of the docs. Indeed, though I haven't really profiled this, an SP-GiST index does seem quite a bit faster for me. 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 j…

If you ever measure the improvement, a brief note/blog post/tweet might instructive to people. Would be curious what kind of different result you get.
Post reply on HN