Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Jonathan S. Katz
Jonathan S. Katz
Tools like the PostgreSQL Operator make it easy to get PostgreSQL up and running on Kubernetes , but what about actually accessing your Postgres databases? pgAdmin 4 is a popular choice for managing PostgreSQL workloads as it's both accessible from a web browser and provides an intuitive interface for daily PostgreSQL tasks such as running queries, adding new tables, and much more. I've previously shown how to easily deploy pgAdmin 4 with PostgreSQL on Docker , but what about on Kubernete...
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 MoreSteve Pousty
Steve Pousty
Let's imagine a scenario in which you are dealing with JSON in your application and you want to store it in your database. You let out a heavy sigh and think, "I guess I am going to have to add something besides my favorite DB (Postgres) to my architecture . I wish I could just keep using PostgreSQL." You start thinking that instead of one problem, now you have many. You're probably going to have to learn a new data query syntax, data creation statement, install new software, and worst of all f...
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 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 More