Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Rekha Khandhadia
Rekha Khandhadia
Imagine your system captures event data from all over the world but the data all comes in UTC time giving no information about the local timing of an event. How can we quickly convert between the UTC timestamp and local time zone using GPS location? We can quickly solve this problem using PostgreSQL and PostGIS. This example assumes you have a Postgres database running with PostGIS. If you’re new to PostGIS, see PostGIS for Newbies . 1. Timezone Shape file Overview: For World Timezone shape fil...
Read MoreElizabeth Christensen
Elizabeth Christensen
Crunchy Data hosted the 4th annual PostGIS Day on November 17, 2022. PostGIS Day always comes a day after GIS Day which occurs annually on the 3rd Wednesday of November. We had speakers from 10 different countries and attendees from more than 70 countries. PostGIS is the most popular spatial relational database worldwide with: • An extensive catalog of spatial functions • Rich ecosystem of in-db extensions for routing, event management, external database linkage, point clouds, rasters and m...
Read MorePaul Ramsey
Paul Ramsey
In a recent post , we introduced pg_eventserv and the real-time web notifications from database actions. In this post, we will dive into a practical use case: displaying state, calculating events, and tracking historical location for a set of moving objects . This demonstration uses pg_eventserv for eventing, and pg_featureserv for external web API, and OpenLayers as the map API, to build a small example application that shows off the common features of moving objects systems. Try it...
Read MorePaul Ramsey
Paul Ramsey
Find me all the things in set "A" that are not in set "B". This is a pretty common query pattern, and it occurs in both non-spatial and spatial situations. As usual, there are multiple ways to express this query in SQL, but only a couple queries will result in the best possible performance. The non-spatial setup starts with two tables with the numbers 1 to 1,000,000 in them, then deletes two records from one of the tables. The spatial setup is a 2M record table of geographic names, and a 3K rec...
Read MorePaul Ramsey
Paul Ramsey
The sheer cleverness of relational databases is often discounted because we so frequently use them for very simple data management tasks. Serialize an object into a row, store with unique key. yawwwn Search for unique key, deserialize row into an object. yawwwwwwn The real power of relational databases is juggling "relations" (aka tables) in large numbers and figuring out on-the-fly the most effective way to filter out rows and find an answer. PostgreSQL has an undeniably clever query plannin...
Read MorePaul Ramsey
Paul Ramsey
The pg_featureserv micro-service is a thin middleware that binds tables and functions in a PostgreSQL database to a JSON collections API, accessible over HTTP. Using the Crunchy Bridge container apps , I'm going to give a quick overview of how to set up a web based spatial heatmap from Postgres. The application uses PostgreSQL to store and search 2.2M geographic names in the USA. Type in the search box and the auto-fill form will find candidate words. Select a word, and the database will pe...
Read MoreElizabeth Christensen
Elizabeth Christensen
PostGIS is one of the most awesome extensions for PostgreSQL and can turn a relational database into a really powerful GIS ( Geographic Information System ). The PostGIS community is really great about documentation and training and this post is aimed at getting you some resources on how to get started with the major components of using PostGIS as a super beginner. I’ll help you get a sample dataset up, import a shape file, and get that all published to a web browser. PostGIS is a Postgres...
Read MoreMartin Davis
Martin Davis
provides access to the powerful spatial database capabilities of PostGIS and PostgreSQL via a lightweight web service. To do this, it implements the OGC API for Features ( OAPIF ) RESTful protocol. OAPIF is part of the Open Geospatial Consortium ( OGC ) OGC API suite of standards. In a previous post, we announced an exciting new capability for : support for CQL filters . CQL ( Common Query Language ) is another OGC standard that provides the equivalent of SQL clauses for web q...
Read MorePaul Ramsey
Paul Ramsey
Every once in a while, a post shows up online about someone using GPUs for common spatial analysis tasks, and I get a burst of techno-enthusiasm. Maybe this is truly the new way! This week it was a post on GPU-assisted spatial joins that caught my eye. In summary, the author took a 9M record set of parking infractions data from Philadelphia and joined it to a 150 record set of Philadelphia neighborhoods . The process involved building up a little execution engine in Python. It was pretty ma...
Read MoreMartin Davis
Martin Davis
The goal of is to provide easy and efficient access to PostGIS from web clients. To do this, it uses the emerging OGC API for Features ( OAPIF ) RESTful protocol. This is a natural fit for systems which need to query and communicate spatial data. The core OAPIF specification provides a basic framework for querying spatial datasets, but it has only limited capability to express filtering subsets of spatial tables. In particular, it only allows filtering on single attribute values, and it...
Read More