Posts about Spatial

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 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

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))
);

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

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

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 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

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