10 ms·
PostGIS – Spatial and Geographic Objects for PostgreSQL
- ascorbic 6y agoNext week is PostGIS's 20th birthday
- hnedeotes 6y agoUsed it a few times and it's pretty amazing what you can do with it out of the box.
- nixpulvis 6y agoI'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 most maps are in fact 2D. Anyway, my 2-cents of experience. If anyone has some good advise for > million row, 3D spacial optimizations for PostGIS, please let me know.
- jhgb 6y ago> 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.
- nixpulvis 6y agoI 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.
- estsauver 6y agoThe 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.
- tpetry 6y agoYes 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*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.
- electrum 6y agoThe Bing Maps Tile System has a similar projection system: https://docs.microsoft.com/en-us/bingmaps/articles/bing-maps-tile-system https://docs.microsoft.com/en-us/bingmaps/articles/bing-maps...
- nkozyra 6y agoMBR is a common optimization shortcut but I have to wonder if PostGIS doesn't already do that as a first pass filter anyway. It's an incredibly smart and performant extension. Sometimes the issue can be tweaked by using intersection versus overlap/contains/contained when possible.
- 7952 6y agoYou could use a clustered index
- nixpulvis 6y agoInteresting, I think I read something about this before... I'll have to make a point to actually give this a go.
- yxhuvud 6y agoHmm. That sounds like a lot of time. How does your query look like for that?
- nixpulvis 6y agoSELECT 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)
- yxhuvud 6y agoOk, I see no obvious ways that is suboptimal. I assume there are no difference if you switch places of position and $1?
- nicois 6y agoIs 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.
- tcbasche 6y agoMy 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!
- nixpulvis 6y agoI'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.
- gfiorav 6y agoYou 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.
- nixpulvis 6y agoPerhaps 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?
- cwmma 6y agothere 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
- nixpulvis 6y agoIndeed. It's definitely working, just not working well enough to satisfy my performance requirements in my current usage.
- billfruit 6y agoWhat 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?
- nixpulvis 6y agoYea 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.
- billfruit 6y ago3d 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.
- nixpulvis 6y agoI 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-geography-type-is-not-limited-to-earth.html http://www.bostongis.com/blog/index.php?/archives/266-geogra...
- fdr 6y agoHmm, 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.
- nixpulvis 6y agoOh 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.
- fdr 6y agoIf 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.
- motiejus 6y agoI 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 getting used to. But doable. Things I like so far: - geometric functions are very robust and well documented. Reference manual[3] is amazing. Creating/editing/iterating over lines and polygons, detecting their features, is a breeze. - good integration with QGIS -- I can see the result graphically on my desktop system very easily. - I can even write tests[4]. Things I am worried about or don't like as much: - The SQL/procedural mix requires a lot of getting used to, and sometimes "simple" things in a "real" programming language becomes complex here. E.g. I would love to have mutable linked-lists. Or moving data between "sql-world" (rows) to "plpgsql-world" (arrays and data structures) is non-obvious, and best references are, unfortunately, stackoverflow and scripts of others'. - My program will run on a large data set (e.g. all rivers in a country or a continent). I will want to optimize it. There is an out-of-tree profiler[5], but "unofficial, out of tree" always adds risk. - Biggest one: deep copies everywhere. Since plpgsql does not do any memory management, it is deep-copying everything. Sometimes (often in this algorithm!) I want to add a vertex to a line (=river bend); that always requires a full copy of the whole bend. When I know it will not be used and will be safe, I would like to say "I am mutating this geometry and I don't want a deep copy". In general, I like it for algorithms. Though the moment it needs to hit performance-sensitive production, I believe I will re-do it in C (which, looking at the postgis source code, is quite write-able too). [1]: https://www.tandfonline.com/doi/abs/10.1559/152304098782441750 https://www.tandfonline.com/doi/abs/10.1559/1523040987824417... [2]: https://github.com/motiejus/stud/blob/master/IV/wm.sql https://github.com/motiejus/stud/blob/master/IV/wm.sql [3]: https://postgis.net/docs/reference.html https://postgis.net/docs/reference.html [4]: https://github.com/motiejus/stud/blob/master/IV/tests.sql https://github.com/motiejus/stud/blob/master/IV/tests.sql [5]: https://github.com/okbob/plpgsql_check https://github.com/okbob/plpgsql_check Edit: formatting
- atonse 6y agoRegarding deep copies, have you considered writing it as a plugin in something like rust? https://github.com/zombodb/pgx https://github.com/zombodb/pgx
- gardaani 6y agoHere'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 https://link.springer.com/article/10.1007/s10707-020-00407-w I've decided to use PostgreSQL in my projects.
- dunefox 6y agoEverytime Postgres comes up I'm impressed.
- tcbasche 6y agoThanks 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
- simonw 6y agoMillions 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.
- nucleardog 6y agoI'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.
- jpgvm 6y agoPostGIS is one of those rare examples of highly specialised software that is both OSS and best-in-class. Usually OSS is relegated for highly commoditized software but GIS is anything but even after all these years. I can't really think of many others? Maybe OptaPlanner would be another candidate.
- leetrout 6y agoIs OptaPlanner best in class? (No snark) — It might be the best in Java but not sure more broadly.
- jpgvm 6y agoIt's definitely up there. I guess it's hard to say there is a single "best-in-class" solver because it does depend on the problem at hand but if I had to pick one it would be OptaPlanner.
- darksaints 6y agoI'd say it's not quite in the same category as Postgis, where the open source project is handily better than any closed source rivals. It competes well with some closed source competitors but not all of them.
- jandrewrogers 6y agoYes, among off-the-shelf GIS databases, PostGIS is generally as good or better than anything else on the market. More capable systems exist, particularly in terms of scale and performance, but those are all bespoke.
- Ahmed90 6y ago4 years ago one of my clients wanted to "donate" a system for the local fire department to help them do a quick proximity search to find the fire hydrants and quickly choose the healthy one near the fire. 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
- ntechau 6y agoComparatively, I've spent 4 years of my life on a CompSci degree + 2 years on a GIS Advanced Diploma achieved over 10 years ago mind you). There was a point in my life a few years ago where my study was so unnoticed and under appreciated that I gave it up and now I just do full project management - and I barely even do that. I blame the internet and the accessibility of knowledge to the point where people can go "IDK WTF this is, but lemme google it". Seriously, 6 years of my fucking life dedicated to something that is specialist knowledge and it's just now garbage. so, good on you for winging it and ruining other's careers - lol
- tomrod 6y agoNot his or her fault the tooling obseleted the specialty knowledge.
- Doctor_Fegg 6y agoI 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 academia then Esri still dominates, but there's now much more to "geo".
- stdbrouw 6y agoFWIW, 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 https://shapely.readthedocs.io/en/stable/manual.html and/or https://geopandas.org/ https://geopandas.org/.
- jimktrains2 6y agoI'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.
- stdbrouw 6y agoNot really, combining datasets in (Geo)Pandas is very straightforward, including spatial joins: https://geopandas.org/docs/user_guide/mergingdata.html#spatial-joins https://geopandas.org/docs/user_guide/mergingdata.html#spati... Of course, it's all a matter of personal preference, but I have used both PostGIS and GeoPandas extensively.
- thornygreb 6y agoYou will eventually run out of memory or some other restraint given large enough datasets. PostGIS will churn through it and not crash...
- tcbasche 6y agoI'm not sure what 'a lot faster' really means in this context. Honestly, I've found using Spatialite queries to be orders of magnitude faster for analysis than shapely or geopandas. The latter typically imply row-by-row selection and manipulation for starters. If you can wrangle the data into a geopackage first it's super easy to run queries over the data and extract what you need.
- stdbrouw 6y ago
- twelvechairs 6y agoA couple of excellent GIS related links I would recommend to anyone interested in this area. Talks and writing by PostGIS co-creator Paul Ramsey (who is incredible): http://blog.cleverelephant.ca/writings http://blog.cleverelephant.ca/writings The mapscaping podcast - a lot of intros to the complex and overlapping worlds of GIS: https://mapscaping.com/blogs/the-mapscaping-podcast https://mapscaping.com/blogs/the-mapscaping-podcast
- cwmma 6y agoTalks by Paul Ramsey are usually a highlight of any GIS conference.
- craigkerstiens 6y agoPaul also has quite a few posts on the Crunchy Data blog as well - https://blog.crunchydata.com/blog/author/paul-ramsey https://blog.crunchydata.com/blog/author/paul-ramsey
- zeeZ 6y agoI've toyed with PostGIS to make a poster from OSM data by selecting streets that intersect with one of Tokyo's special wards[0]. Sadly I haven't found any excuses to play with it more since. [0]: https://christian.rinjes.me/posts/2020-11-25-tokyo-street-art/ https://christian.rinjes.me/posts/2020-11-25-tokyo-street-ar...
- sabman 6y agohttps://getgeodb.com https://getgeodb.com is a hosted CartoDB which is an open source stack built on PostGIS but its notoriously hard to run and configure. So we decided to run it as a service for others... we find it very useful to build apps on top of PostGIS since running them requires configuring middleware like tileservices, data APIs and importing data. would love any feedback :D ... you can also hit me up on twitter https://twitter.com/sabman https://twitter.com/sabman
- thom 6y agoPostGIS is loads of fun with sports data. Being able to find similar passages of play just using ST_MakeLine and ST_FrechetDistance feels like magic when you first do it in all of five lines of code. Spatial tools are a bit like logic programming in that they’re very slightly esoteric. But once you know they’re the right hammer for some nails, they’ll save you lots of time and effort over your life, and let you express some ideas you might otherwise struggle with.
- another-dave 6y agoThis sounds really interesting. Was thinking it would be cool to build a hobby project in this space, but wasn't sure how to get started. Do you have any recommended sources for sample datasets to play around with?
- thom 6y agoWe (StatsBomb) make a bunch of stuff available at: https://github.com/statsbomb/open-data https://github.com/statsbomb/open-data Interesting stuff from Metrica too: https://github.com/metrica-sports/sample-data https://github.com/metrica-sports/sample-data There are other less legit sources that you may be able to work out for yourself. :)
- gfiorav 6y agoI probably owe my career to this library. It’s been the enabler for geospatial analysis for a lot of people.
- wdb 6y agoI have used PostGIS successfully for a charity project which helps avoiding food waste in the UK.
- ideamotor 6y ago100% Brilliant software.
- billfruit 6y agoWhat would be a good front end framework to use with PostGIS with capable rendering of GiS artifacts to make a compete desktop GIS application? The only viable open source route I am seeing is through some kind of QGIS extensions. Is there something better for that purpose?
- samstave 6y agoR seems like a great idea, with building shiny apps...
- mooneater 6y agoLets hear some love for Refractions Research out of Victoria BC, who gave us PostGIS! Great folks, very helpful, and a wonderful open source product. http://www.refractions.net/ http://www.refractions.net/
- pella 6y ago- https://planet.postgis.net/ https://planet.postgis.net/ ( postgis related posts ) - https://planet.postgresql.org/ https://planet.postgresql.org/ ( postgresql related posts ) - https://github.com/postgis/docker-postgis https://github.com/postgis/docker-postgis ( docker images for postgis ; alpine+debian)
- dbreunig 6y agoRelatedly: the appendix section "SQL Primer" in Obe and Hsu's 'PostGIS in Action' is my favorite quick intro to SQL and the first stop I give anyone looking to learn about the language. https://www.manning.com/books/postgis-in-action-second-edition https://www.manning.com/books/postgis-in-action-second-editi...
- poxrud 6y agoI've used PostGIS + maxmind datasets for my geolocation needs, instead of using SaaS based services, like Google's geolocation API.
- brianzelip 6y agoA couple of recent and insightful podcast episodes related to PostGIS: 1. https://talkpython.fm/episodes/show/295/gis-python https://talkpython.fm/episodes/show/295/gis-python 2. https://changelog.com/podcast/417 https://changelog.com/podcast/417 (postgres)
- willtemperley 6y agoIt'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. https://github.com/Esri/geometry-api-java https://github.com/Esri/geometry-api-java
- jokoon 6y agoDon't forget you can get similar results with spatialite. There also are ways to do spatial queries with a z order curve.
- ruffrey 6y agoI helped build a relatively successful SaaS offering for finance atop PostGIS with MapServer and Mapproxy+Redis. The stack ran circles around ArcGis. Not even a close comparison.