Latest posts from Paul Ramsey
Postgres Indexes, Selectivity, and Statistics
Paul looks under the covers at Postgres query plans, indexes, filters, and statistics.
6 min readInstant Heatmap with pg_featureserv
Use your spatial data with this lightweight web server to make an instant heatmap! Paul sets up a demo using locations, name data, and our new container apps feature to run the pg_featureserv right inside the database.
5 min readParquet and Postgres in the Data Lake
Have too much static data? Paul has a idea of moving some of it to a datalake. He provides a walk-through of setting up Parquet with Postgres using the parquet_fdw.
8 min readPostGIS vs GPU: Performance and Spatial Joins
Paul takes a recent example of GPU-assisted spatial joins project to see how PostGIS stacks up in the same situation. He goes through the steps of joining a large record set of parking data with neighborhood data.
5 min readElevation Profiles and Flightlines with PostGIS
Paul walks through spatial indexes and queries to find a solution to a recent postgis-user query.
5 min read
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.jpg)
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.png)
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-1.png)
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-2.png)
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.png)
(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.png)
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
