Regular expressions? Exceptional expressions according to Paul! Some nice examples and tips for using regrex inside Postgres. Topics include true/false regex, text extraction, text substitutions, and using regex flags.
Learn about how structuring an index based on the inputs it receives and the order of inputs can have a strong effect on the quality of the final index. This post includes a lot of example code and geometry.
PostgreSQL can provide high performance summaries over multi-million record tables, and supports some great SQL sugar to make it concise and readable, in particular aggregate filtering, a feature unique to PostgreSQL and SQLite.
Raster data access from the spatial database is an important feature, and the coming release of PostGIS will make remote access more practical, by allowing access to private cloud storage.
One theme of the 3.2 release is new analytical functionality in the raster module, and access to cloud-based rasters via the "out-db" option for rasters. Let's explore two new functions and exercise cloud raster support at the same time.
One of the less visible improvements coming in PostGIS 3.2 (via the GEOS 3.10 release) is a new algorithm for repairing invalid polygons and multipolygons.
PostgreSQL has built-in JSON generators that can be used to create structured JSON output right in the database, upping performance and radically simplifying web tiers.
A common situation in the spatial data world is having discrete measurements of a continuous variable. Every place in the world has a temperature, but there are only a finite number of thermometers - how should we reason about places without thermometers and how should we model temperature?
The simple story of spatial indexes is - if you are planning to do spatial queries (which, if you are storing spatial objects, you probably are) you should create a spatial index for your table.
We at Crunchy Data put as much development effort into improving GEOS as we do improving PostGIS proper, because the GEOS library is so central to much geospatial processing.
The page "Falsehoods Programmers Believe About Names" covers some of the ways names are hard to deal with in programming. This post will ignore most of those complexities, and deal with the problem of matching up loose user input to a database of names.
Building maps that use dynamic tiles from the database is a lot of fun. You get the freshest data, you don't have to think about generating a static tile set, and you can do it with very minimal middleware, using pg_tileserv.
The PostGIS raster has a steep learning curve, but it opens up some unique possibilities for data analysis and accessing non-standard data from within PostgreSQL. Here's an example that shows how to access raster data from PostGIS running on Crunchy Bridge.
While we talk about "PostGIS" like it's one thing, it's actually the collection of a number of specialized geospatial libraries, along with a bunch of code of its own.
Summarizing data against a fixed grid is a common way of preparing data for analysis. Fixed grids have some advantages over natural and administrative boundaries.
Open source developers sometimes have a hard time figuring out what feature to focus on to generate the greatest value for end users. As a result, they will often default to performance. Performance is the one feature that every user approves of. The software will keep on doing all the same cool stuff, only faster.
The PostGIS geography type is a geospatial type that understands coordinates as spherical coordinates, in latitude and longitude.
Constraints trigger basic relationships and a look at more complex use cases like deferred constraints, and full table-level data structures.
How to guard spatial data quality using constraint triggers and spatial relationships.
Here's a simple and practical example of backing a web map with PgRouting.