Latest posts from Paul Ramsey

  • 4 min read

    PostGIS 3.2 New and Improved

    Paul Ramsey

    Last month, just under the wire for a 2021 release, the 3.2 version of PostGIS hit the streets! This new PostGIS also supports the latest 3.10 release of GEOS, which underpins a few of the new features.

    Raster Algorithms

    Read More
  • 5 min read

    Extracting and Substituting Text with Regular Expressions in PostgreSQL

    Paul Ramsey

    While supporting Crunchy Spatial and Crunchy Bridge clients, I’ve been thinking about how I usually clean messy data. I wanted to talk about regular expressions (regex

    Read More
  • 3 min read

    Tricks for Faster Spatial Indexes

    Paul Ramsey

    One of the curious aspects of spatial indexes is that the nodes of the tree can overlap, because the objects being indexed themselves also overlap.

    paulblog1That means that if you're searching an area in which two nodes overlap, you'll have to scan the contents of both nodes. For a trivial example above, that's not a big deal, but if an index has a lot of overlap, the extra work can add up to a measurable query time difference.

    The PostGIS spatial index is based on a R-tree

    Read More
  • 3 min read

    Fast, Flexible Summaries with Aggregate Filters and Windows

    Paul Ramsey

    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.

    A huge amount of reporting is about generating percentages: for a particular condition, what is a value relative to a baseline.

    Some Sample Data

    Read More
  • 6 min read

    Waiting for PostGIS 3.2: Secure Cloud Raster Access

    Paul Ramsey

    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.

    Previous versions could access rasters in public buckets, which is fine for writing blog posts

    Read More
  • 6 min read

    Waiting for PostGIS 3.2: ST_Contour and ST_SetZ

    Paul Ramsey

    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.

    OpenTopography

    Read More
  • 4 min read

    Waiting for PostGIS 3.2: ST_MakeValid

    Paul Ramsey

    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.

    Algorithms like polygon intersection, union and difference rely on guarantees that the structure of inputs follows certain rules. We call geometries that follow those rules "valid" and those that do not "invalid".

    The rules are things like:

    • Polygon rings should not cross themselves
    • Polygon rings should not cross other rings
    • Multipolygon components should neither touch nor overlap
    Read More
  • 4 min read

    Cut Out the Middle Tier: Generating JSON Directly from Postgres

    Paul Ramsey

    Too often, web tiers are full of boilerplate that does nothing except convert a result set into JSON. A middle tier could be as simple as a function call that returns JSON. All we need is an easy way to convert result sets into JSON in the database.

    PostgreSQL has built-in JSON generators

    Read More
  • 4 min read

    Waiting for PostGIS 3.2: ST_InterpolateRaster

    Paul Ramsey

    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?

    For many use cases, the right way to model a continuous spatial variable is a raster: a regularly spaced grid where each square in the grid contains a value of the variable. This works for temperature and precipitation; it works for elevation and slope; it even works for travel times and wayfinding.

    For this blog post, we will build up a temperature surface for Washington State, using the discrete temperature measurements of a set of Department of Transportation (WSDoT) weather stations.

    Getting the Data

    Read More
  • 6 min read

    (The Many) Spatial Indexes of PostGIS

    Paul Ramsey

    Spatial indexes are used in PostGIS to quickly search for objects in space. Practically, this means very quickly answering questions of the form:

    • "all the things inside this this" or
    • "all the things near this other thing"

    Because spatial objects are often quite large and complex (for example, coastlines commonly are defined with thousands of points), spatial indexes use "bounding boxes" as index and search keys:

    • Bounding boxes are of a small, fixed size, only 4 floats for a 2D box; and,
    • Bounding boxes are very inexpensive to compare to test things like containment.
    Read More