Paul Ramsey
Paul Ramsey
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 ) the earth is not flat, it's round . It's almost a sphere, an "oblate spheroid", slightly wider than it is tall. Because it is (mostly) spherical, we don't use cartesian coordinates (x, y) to describe locations on the earth, we use spherical coordinates...
Read MorePaul Ramsey
Paul Ramsey
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. We've added a couple simple check constraints in the table definition: • we want our road segments to have a non-zero length; and, •...
Read MorePaul Ramsey
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 , we covered the use of CHECK constraints to enforce data quality at an object level. However, spatial data quality usually involves higher order relationships between geometries. Just as a strong non-spatial model will enforce foreign key relationships, spatial constraints can be used to enforce s...
Read MorePaul Ramsey
Paul Ramsey
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 ), PgRouting depends on the PostGIS extension, but there's no reason it could not be used for graph analysis for any number of other graph problems. Here's a simple and practical example of backing a web map with PgRouting . Thanks to the magic of packaging, PgRouting is...
Read MorePaul Ramsey
Paul Ramsey
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? ( ) • Are mandatory columns filled in? ( ) • Are unique values columns in fact unique? ( ) • Do other data quality rules pass? ( ) Do foreign keys match up to corresponding keys? ( ) Are mandatory columns filled in? ( ) Are unique values columns in fact unique? ( ) Do other data quality rules pass? ( ) Why enforce data quality rules in the databas...
Read MorePaul Ramsey
Paul Ramsey
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 : Let's say we received polygons from 10 users. If we used ST_Intersection on those polygons, the remaining polygon would only represent the points included in all 10 polygons. If we used ST_Union, the output would represent the points included in at least 1 polygon. Can anyone recommend a way to output a polygon that represents th...
Read MorePaul Ramsey
Paul Ramsey
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. A filling of the plane with hexagons is a hexagonal grid. Curiously, it's possible to add...
Read MorePaul Ramsey
Paul Ramsey
In my previous posting on tile serving , I demonstrated how pg_tileserv can publish spatial tables as dynamic vector tiles. Dynamic tiles , available as part of Crunchy Spatial , are generated in the database, on-the-fly, in response to a tile request. This allows for two kinds of dynamism to show up in the end user applications: • Changes in the underlying data show up in the generated tiles. • Changes in the tile request can show up in the generated tiles. Changes in the underlying da...
Read MorePaul Ramsey
Paul Ramsey
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 . It's possible to write your own HTTP wrapper for the PostGIS vector tile generator, but you don't need to! pg_tileserv is a lightweight vector tile server s...
Read MorePaul Ramsey
Paul Ramsey
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 generat...
Read More