Latest posts from 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.

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 GEOS library is a core piece of PostGIS. It is the library that provides all the "hard" computational geometry functionality:

• Intersections of geometry
• Unions of geometry
• Differences of geometry
• Buffers of geometry
• Geometry relationship evaluation

A surprisingly common problem in both application development and analysis is: given an input name, find the database record it most likely refers to. It's common because databases of names and people are common, and it's a problem because names are a very irregular identifying token.

The page "Falsehoods Programmers Believe About 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.

However, the day comes when it's time to move your application from development to production, what kinds of things should you be thinking about?

Let's start with load

The PostGIS raster extension 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.

• PostGIS provides core functionality
  • bindings to PostgreSQL, the types and indexes

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:

• No appeal to higher authorities
• Equal unit areas
• Equal distances between cells
• Good for passing data from the "spatial" computational realm to a "non-spatial" realm

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

PostGIS is a "geospatial database" and the "geo" in "geospatial" is an important qualifier: it means that all the coordinates in PostGIS point, lines, and polygons can be located somewhere on the earth.

As we all know (except for a few of us

In our last installment, we covered the use of a constraint trigger to enforce data quality by looking at geometry spatial relationships.

For this installment, we'll start with basic relationships and then look at more complex use cases: deferred constraints, and full table-level data structures.

Linear Network Constraints

Let's start with a simple road network.

CREATE TABLE roads (
  pk bigint PRIMARY KEY,
  name text NOT NULL,
  geom geometry(LineString, 3005) NOT NULL
    CONSTRAINT geom_no_zero_length CHECK (ST_Length(geom) > 0)
    CONSTRAINT geom_no_self_intersection CHECK (ST_IsSimple(geom))
);