Crunchy Data joins Snowflake. Read the announcement
Martin 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 MoreSteve Pousty
Steve Pousty
Today's blog post is for all those people who want to create and manage PostgreSQL or PostGIS clusters AFTER the PostgreSQL Operator has been installed on their Kubernetes/OpenShift cluster. If you need more information on the PostgreSQL Operator we have a great blog post for you and another on latest updates . If you're moving to a Kubernetes infrastructure, having access to the PostgreSQL Operator can make your life a LOT easier. There are plenty of instances in which you don't want t...
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 MoreTom Swartz
Tom Swartz
By design, the out of the box configuration for PostgreSQL is defined to be a "Jack of All Trades, Master of None". The default configuration for PostgreSQL is fairly painstakingly chosen to ensure that it will run on every environment it is installed, meeting the lowest common denominator resources across most platforms. Because of this, it's always recommended that one of the first actions performed once an install of PostgreSQL is completed, would be to tune and configure some high-level se...
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 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 MoreMartin Davis
Martin Davis
In addition to viewing PostGIS spatial data as vector tiles using pg_tileserv , it is often necessary to access data features directly. This supports use cases such as: • display features at a point or in an area of interest • query features using spatial and/or attribute filters • retrieve features for use in a web application (for tabular or map display) • download spatial data for use in applications display features at a point or in an area of interest query features using spatial and/or...
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 More