Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
John Porvaznik
John Porvaznik
pgRouting is a powerful routing tool, usually used for pathfinding/mapping/direction applications. (See Paul Ramsey's introduction to pgRouting here ). It is, however, also a robust graph db implementation, and can be used for much more than just finding the directions to your great aunt Tildy’s. Yacht Rock (as if you didn’t know) is a music genre created well after its active era. It’s characterized by smooth dulcet sounds that bring to mind wavy blond-haired waspy men in boat shoes, and ult...
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 MoreSteve Pousty
Steve Pousty
In today's blog post I am going to show you how to easily spin up PostGIS in your Kubernetes or OpenShift cluster using the PostgreSQL Operator . You may have already started to investigate how to take advantage of all the Kubernetes goodness, especially combining it with your favorite spatial database, PostGIS. Well have no fear, after a few introductory steps I will show you how one command can bring up PostGIS and then with another command, make it a replicated cluster. This replicated...
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 MoreMartin Davis
Martin Davis
A classic spatial query is to find the nearest neighbours of a spatial feature. Our previous post " Won’t You Be My Neighbor? Quickly Finding Who is Nearby " discussed this capability from a PostgreSQL perspective. The PostGIS spatial data extension to Postgres can also execute nearest neighbour queries with remarkable efficiency. In this post, we’re going to take a deeper dive into the Postgres and PostGIS internals to find out how this actually works. By the time we surface you will have...
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 MoreKat Batuigas
Kat Batuigas
In our last blog post about pg_featureserv , we showed how it can publish spatial datasets and access them via simple web requests. In this post, we’re going to discuss how publishing PostgreSQL / PostGIS functions via pg_featureserv provides even more flexible access to your data. A powerful feature of PostgreSQL is the ability to create user-defined functions . Functions let you wrap complex logic within a simple interface: passing in arguments, and getting back a set of values as output...
Read More