Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Paul Ramsey
Paul Ramsey
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 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...
Read MorePaul Ramsey
Paul Ramsey
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 . For PostGIS 3.1, there have been several performance improvements that, taken together, might add up to a large performance gain for your workloads. Spatial joins have slowed down...
Read MorePaul 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 More