Following up on my previous post, I decided to attempt the same analysis in PostgreSQL. The analysis doesn’t make use of any spatial logic itself (yet), but I consider this a PostGIS post because it is using PostGIS geometries.
In the past, I have noticed that BigQuery SQL is very reminiscent of that of PostgreSQL, which has helped me ramp my productivity with BigQuery. In the case of the LAG function as used here, that reminiscence is an exact copy. So, again, PostGIS comes through.
As can be seen, the identical query syntax from the previous post produced the same results in PostgreSQL.
In the process of migrating my sample data from BigQuery to PostGIS (thanks FME), I changed the name of the geometry column, but there was no other change. Viewing the results in the DBeaver shows the expected tabular and spatial output.
So, if BigQuery isn’t your thing, or (like me) you have a sizable investment of time and expertise in PostGIS, you can utilize it perform the same kind of change analysis.
Header image basemap © OpenStreetMap contributors.
