Wednesday, December 06. 2023
PostGIS Day 2023 videos
came out recently.
PostGIS Day conference is always my favorite conference of the year because you get to see
what people are doing all over the world, and it always has many many new tricks
for using PostgreSQL and PostGIS family of extensions you had never thought of.
Most importantly it's virtual, which makes it much easier for people to fit in their schedules
than an on site conference. We really need more virtual conferences in the PostgreSQL community.
Many many thanks to Crunchy Data for putting this together again,
in particular to Elizabeth Christensen
who did the hard behind the scenes work of corraling all the presenters and stepping in to give a talk herself,
and my PostGIS partner in development Paul Ramsey
who did the MC'ing probably with very little sleep, but still managed to be very energetic.
Check out Elizabeth's summary of the event.
Many of her highlights would have been mine too, so I'm going to skip those.
Continue reading "PostGIS Day 2023 Summary"
Saturday, September 09. 2023
Paul Ramsey and I recently had a Fireside chat with Path to Cituscon. Checkout the Podcast Why People care about PostGIS and Postgres. There were a surprising number of funny moments and very insightful stuff.
It was a great fireside chat but without the fireplace. We covered the birth and progression of PostGIS for the past 20 years and the trajectory with PostgreSQL. We also learned of Paul's plans to revolutionize PostGIS which was new to me. We covered many other side-line topics, like QGIS whose birth was inspired by PostGIS.
We covered pgRouting and mobilitydb which are two other PostgreSQL extension projects that extend PostGIS.
We also managed to fall into the Large Language Model conversation of which Paul and I are on different sides of the fence on.
Continue reading "Why People care about PostGIS and Postgres and FOSS4GNA"
Friday, June 02. 2023
I recently released PostGIS 3.3.3. bundle for Windows which is available on application stackbuilder and OSGeo download site for PostgreSQL 11 - 15.
If you are running PostgreSQL 12 or above, you get an additional bonus extension MobilityDB which is an extension that leverages PostGIS geometry and geography types and introduces several more spatial-temporal types and functions specifically targeted for managing objects in motion.
What kind of management, think of getting the average speed a train is moving at a segment in time or collisions in time, without any long SQL code. Just use a function on the trip path, and viola. Think about storing GPS data very compactly in a singe row /column with time and being able to ask very complex questions with very little SQL. True PostGIS can do some of this using geometry with Measure (geometryM) geometry types, but you have to deal with that craziness of converting M back to timestamps, which mobilitydb temporal types automatically encode as true PostgreSQL timestamp types.
Anita Graser, of QGIS and Moving Pandas fame, has written several posts about it such as: Visualizing Trajectories with QGIS and mobilitydb and Detecting close encounters using MobilityDB 1.0.
Continue reading "PostGIS Bundle 3.3.3 for Windows with MobilityDB"
Sunday, December 04. 2022
Elizabeth Chistensen already gave a succinct summary of some of the PostGIS Day 2022 presentations, which you can see here.
There were many case study presentations which involved use of PostGIS, QGIS, OpenStreetMap, and pgRouting (and other extensions that extend PostGIS) as well as many "How to" videos. There were also talks on "How PostGIS is made". I'll highlight some of these, which overlap with Elizabeth's list but different angle of view.
Continue reading "PostGIS Day 2022 Videos are out and some more highlights"
Friday, November 18. 2022
I didn't think last two years Post GIS Day conferences could be topped, but I was wrong. This year's was absolutely fabulous and better than all the others.
We had two key conferences going on this year (yesterday). The first was the Cruncy Data PostGIS Day 2022 12 hour marathon of nothing but PostGIS related talks. Many thanks to Elizabeth Christensen and Paul Ramsey for putting it all together and for 12 hrs. The PostGIS day celebrations climaxed with many of us playing capture the flag on Paul's new shiny invention until we managed to crash it.
Continue reading "Post GIS Day 2022 Celebrations"
Sunday, October 16. 2022
PostgreSQL 15 came out just last week. To celebrate the arrival of PostgreSQL 15, I will revisit the number one problem people have with PostGIS, how to upgrade your PostGIS enabled cluster, without installing an old PostGIS version.
In a previous article Using pg upgrade to upgrade PostGIS without installing older version I demonstrated a trick for upgrading to a newer PostgreSQL instance from PostGIS 2.2 - 2.whatever without having to install the older version of PostGIS in your new PostgreSQL service.
This is a revisit of that article, but with considerations for upgrading from a 2 series to a 3 series.
Fear not. Going from PostGIS 2+ to PostGIS 3+ can still be done without installing the old PostGIS 2+ in your new cluster. Additionally once you are on PostGIS 3.1+, you should never have to do symlink or copy hacks to upgrade say PostGIS 3.1 to PostGIS 3.4. At least not for the same major version.
If per chance you are on PostGIS 1 and trying to jump all the way to PostGIS 3+, then sadly you need to do a pg_dump of your old database/cluster and pg_restore into your new cluster.
Continue reading "Using pg_upgrade to upgrade PostgreSQL 9.6 PostGIS 2.4 to PostgreSQL 15 3.3 on Yum"
Saturday, December 18. 2021
This year's PostGIS Day was on November 18, 2021. I celebrated like many others in a PostGIS day virtual conference. You can find the talks and videos at PostGIS Day 2021. Others celebrated with parties, food, and spirits. This was my favorite PostGIS Day conference ever. Each year just gets better.
If I were to sum up this year's conference I would say: A generous helping of code, lots of humor, and lots of people. Thanks Elizabeth Christensen, Paul Ramsey and Crunchy Data for putting this conference together. All talks were really good so hard to isolate just a couple.
Continue reading "PostGIS Day 2021 Highlights"
Thursday, October 21. 2021
Sunday, October 25. 2020
One of the new features coming in PostGIS 3.1 is fixed precision support.
This new feature will require compilation with not yet released GEOS 3.9.
There are a couple of functions already in PostGIS 3.1 that have this new feature -- they are ST_Subdivide, ST_Union, ST_SymDifference, ST_Union, and ST_UnaryUnion as summarized in
What's new in PostGIS 3.1.
To take advantage of these new to die for features, you'll need to have compiled your PostGIS with development GEOS 3.9 which is planned for release
around the same time we get around to releasing PostGIS 3.1. Windows users can download binaries with GEOS 3.9 support from
PostGIS windows experimental binaries
The ST_Union feature should improve a lot of cases where people ran into topological exceptions. Perhaps I'll demonstrate that in a separate article once I've given it a test drive.
Continue reading "Waiting for PostGIS 3.1: ST_Subdivide and other function support with fixed precision"
Tuesday, January 01. 2019
In a previous article Using pg upgrade to upgrade PostGIS without installing older version I demonstrated a trick for upgrading to a newer PostgreSQL instance from PostGIS 2.2 - 2.whatever without having to install the older version of PostGIS in your new PostgreSQL service.
Unfortunately that trick does not work if coming from PostGIS 2.1 because in PostGIS 2.2 we renamed a c lib function that backed sql functions in 2.1.
Fear not. There is still a way to upgrade from 2.1 to 2.5 without installing an older version of PostGIS in your new PostgreSQL instance. To do so, you need to add a step and that is to remove the functions in 2.1 that are backed by this renamed lib function. In upcoming PostGIS 3.0, we've added this function back and have it throw an error so that even coming from PostGIS 2.1, you can upgrade just the same as you do from later versions.
Continue reading "Using pg_upgrade to upgrade PostgreSQL 9.3 PostGIS 2.1 to PostgreSQL 11 2.5 on Yum"
Saturday, September 08. 2018
pgAdmin4 version 3.3 released this week comes with a PostGIS geometry viewer. You will be able to see the graphical output of your query directly in pgAdmin, provided
you output a geometry or geography column.
If your column is of SRID 4326 (WGS 84 lon/lat), pgAdmin will automatically display against an OpenStreetMap background.
We have Xuri Gong to thank for working on this as a PostGIS/pgAdmin Google Summer of Code (GSOC) project. We'd like to thank Victoria Rautenbach and Frikan Erwee for mentoring.
Continue reading "pgAdmin4 now offers PostGIS geometry viewer"
Monday, June 25. 2018
We've got customers discovering PostGIS and GIS in general or migrating away from ArcGIS family of tools.
When they ask, "How do I see my data?", we often point them at QGIS
which is an open source GIS desktop with rich integration with PostGIS/PostgreSQL.
QGIS is something that is great for people who need to live in their GIS environment since it allows for easily laying on other datasources, web services and maps.
The DBManager tool allows for more advanced querying (like writing Spatial SQL queries that take advantage of the 100s of functions PostGIS has to offer) , ability to import/export data, and create PostgreSQL views.
QGIS has this thing called Projects, which allow for defining map layers and the symbology associated with them. For example what colors do you color your roads, and any extra symbols, what field attributes do you overlay - street name etc. Projects are usually saved in files with a .qgs or .qgz extension. If you spent a lot of time styling these layers, chances are you want to share them with other people in your group. This can become challenging if your group is not connected via network share.
Continue reading "New in QGIS 3.2 Save Project to PostgreSQL"
Monday, June 18. 2018
PostGIS 2.5 is just around the corner. One of the new functions coming is the ST_OrientedEnvelop. This is something I've been waiting for for years.
It is the true minimum bounding rectangle, as opposed to ST_Envelope which is an axis aligned bounding rectable.
Below is a pictorial view showing the difference between the two.
Continue reading "Coming PostGIS 2.5 ST_OrientedEnvelope"
Thursday, November 16. 2017
To commemorate PostGIS Day 2017, I put together a 3D scene listing my favorite functions.
You can see it PostGIS Day in 3D
Sunday, October 15. 2017
PostGIS releases a new minor version of PostGIS every one or two years. Each minor version of postgis has a different libname suffix.
In PostGIS 2.1 you'll find files in your PostgreSQL lib folder called postgis-2.1.*, rtpostgis-2.1.*, postgis-topology-2.1.*, address-standardizer-2.1.* etc.
and in a PostGIS 2.2 you'll find similar files but with 2.2 in the name. I believe PostGIS and pgRouting are the only extensions that stamp the lib with a version number.
Most other extensions you will find are just called extension.so e.g. hstore is always called hstore.dll /hstore.so even if the version changed from 9.6 to 10.
On the bright side this allows people to have two versions of PostGIS installed in a PostgreSQL cluster, though a database can use at most one version.
So you can have an experimental database running a very new or unreleased version of PostGIS and a production database running a more battery tested version.
On the sad side this causes a lot of PostGIS users frustration trying to use pg_upgrade to upgrade from an older version of PostGIS/PostgreSQL to a newer version of PostGIS/PostgreSQL;
as their pg_upgrade often bails with a message in the loaded_libraries.txt log file something to the affect:
could not load library "$libdir/postgis-2.2": ERROR: could not access file "$libdir/postgis-2.2": No such file or directory
could not load library "$libdir/postgis-2.3": ERROR: could not access file "$libdir/postgis-2.3": No such file or directory
This is also a hassle because we generally don't support a newer version of PostgreSQL on older PostGIS installs because the PostgreSQL major version changes tend to break our code
often and backporting those changes is both time-consuming and dangerous. For example the DatumGetJsonb change
and this PostgreSQL 11 crasher we haven't isolated the cause of yet. There are several changes like this that have already made the PostGIS 2.4.0
we released recently incompatible with the PostgreSQL 11 head development.
Continue reading "Using pg_upgrade to upgrade PostGIS without installing an older version of PostGIS"
|