PostGIS – Spatial and Geographic Objects for PostgreSQL

PostGIS – Spatial and Geographic Objects for PostgreSQL
postgis.net

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

[2]: https://github.com/motiejus/stud/blob/master/IV/wm.sql

[3]: https://postgis.net/docs/reference.html

[4]: https://github.com/motiejus/stud/blob/master/IV/tests.sql

[5]: https://github.com/okbob/plpgsql_check

Edit: formatting

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.