Latest posts from Paul Ramsey

If constraints in general have caught your interest, our interactive learning portal has a whole section on the use of non-spatial constraints, even a video walkthrough!

In our last installment

One of the least-appreciated PostgreSQL extensions is the powerful PgRouting extension, which allows routing on dynamically generated graphs. Because it's often used for geographic routing (and is a part of Crunchy Spatial

Constraints are used to ensure that data in the database reflects the assumptions of the data model.

• Do foreign keys match up to corresponding keys? (REFERENCES)
• Are mandatory columns filled in? (NOT NULL)
• Are unique values columns in fact unique? (UNIQUE

The GIS Stack Exchange is a great repository of interesting questions and answers about how to work with spatial data, and with PostGIS.

For example, this question

In our previous posting on tile serving, we showed off how pg_tileserv can use database functions to generate tiles by querying existing tables with user parameters.

We can also use functions to build geometry on the fly without input from tables. For example, hexagons!

Hexagons are a favourite input for visualizations, because they have a nice symmetric shape and provide equal areas for summarization.

Hexagonal Grid

In my previous posting on tile serving, I demonstrated how pg_tileserv can publish spatial tables as dynamic vector tiles.

Dynamic tiles

Beautiful, responsive maps are best built using vector tiles, and PostgreSQL with PostGIS can produce vector tiles on-the-fly.

However, to use vector tiles in a beautiful, responsive map, you need to be able to access those tiles over the HTTP web protocol, and you need to be able to request them using a standard XYZ tiled map URL

Let's put that on a map!

PostGIS has so many cool features that it is possible to do full GIS analyses without ever leaving the SQL language, but... at the end of the day, you want to get those results, show that data, on the map.

The Crunchy Data geospatial team has been thinking about how to bring PostGIS to the web, and we established some basic principles:

• Use the database, as much as possible.

• The database already has a user model and security model.

• The database can already generate output formats, like MVT

Where are you? Go ahead and figure out your answer, I'll wait.

No matter what your answer, whether you said "sitting in my office chair" or "500 meters south-west of city hall" or "48.43° north by 123.36° west", you expressed your location relative to something else, whether that thing was your office layout, your city, or Greenwich

While PostGIS includes lots of algorithms and functionality we have built ourselves, it also adds geospatial smarts to PostgreSQL by linking in specialized libraries to handle particular problems:

• Proj for coordinate reference support;
• GDAL