Latest posts from Paul Ramsey
Extracting and Substituting Text with Regular Expressions in PostgreSQL
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.
5 min read
Tricks for Faster Spatial Indexes
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.
3 min read
Fast, Flexible Summaries with Aggregate Filters and Windows
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.
3 min read
Waiting for PostGIS 3.2: Secure Cloud Raster Access
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.
6 min read
Waiting for PostGIS 3.2: ST_Contour and ST_SetZ
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.
6 min read
Waiting for PostGIS 3.2: ST_MakeValid
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.
4 min read
Cut Out the Middle Tier: Generating JSON Directly from Postgres
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.
4 min read
Waiting for PostGIS 3.2: ST_InterpolateRaster
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?
4 min read
(The Many) Spatial Indexes of PostGIS
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.
6 min read
Performance Improvements in GEOS
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.
6 min read
Fuzzy Name Matching in Postgres
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.
6 min read
Production PostGIS Vector Tiles: Caching
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.
3 min read
PostGIS Raster and Crunchy Bridge
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.
11 min read
Waiting for PostGIS 3.1: GEOS 3.9
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.
4 min read
Waiting for PostGIS 3.1: Grid Generators
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.
2 min read
Waiting for PostGIS 3.1: Performance
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.
3 min read
PostGIS and the Geography Type
The PostGIS geography type is a geospatial type that understands coordinates as spherical coordinates, in latitude and longitude.
4 min read
Spatial Constraints with PostGIS in PostgreSQL- Part 3
Constraints trigger basic relationships and a look at more complex use cases like deferred constraints, and full table-level data structures.
6 min read
Spatial Constraints with PostGIS in PostgreSQL- Part 2
How to guard spatial data quality using constraint triggers and spatial relationships.
5 min read
Routing with PostgreSQL and Crunchy Spatial
Here's a simple and practical example of backing a web map with PgRouting.
6 min read
