Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Paul Ramsey
Paul Ramsey
The raster functionality in PostGIS has been part of the main extension since it was introduced. When PostGIS 3 is released, if you want raster functionality you will need to install both the core extension, and also the extension. Breaking out the raster functionality allows packagers to more easily build stripped down "just the basics" PostGIS without also building the raster dependencies, which include the somewhat heavy GDAL library. The raster functionality remains intact however, a...
Read MorePaul Ramsey
Paul Ramsey
Vector tiles are the new hotness , allowing large amounts of dynamic data to be sent for rendering right on web clients and mobile devices, and making very beautiful and highly interactive maps possible. Since the introduction of ST_AsMVT() , people have been generating their tiles directly in the database more and more, and as a result wanting tile generation to go faster and faster. Every tile generation query has to carry out the following steps: • Gather all the relevant rows for the tile...
Read MorePaul Ramsey
Paul Ramsey
With the release of PostGIS 3.0 , queries that geometry columns will return rows using a Hilbert curve ordering, and do so about twice as fast. Whuuuut!?! The history of "ordering by geometry" in PostGIS is mostly pretty bad. Up until version 2.4 (2017), if you did on a geometry column, your rows would be returned using the ordering of the minimum X coordinate value in the geometry. One of the things users expect of "ordering" is that items that are "close" to each other in the ordered li...
Read MorePaul Ramsey
Paul Ramsey
With PostGIS 3.0, it is now possible to generate GeoJSON features directly without any intermediate code, using the new function. The GeoJSON format is a common transport format, between servers and web clients, and even between components of processing chains. Being able to create useful GeoJSON is important for integrating different parts in a modern geoprocessing application. PostGIS has had an for forever, but it does slightly less than most users really need: it takes in a PostGIS geo...
Read MorePaul Ramsey
Paul Ramsey
One of the most popular features of PostGIS 2.5 was the introduction of the "vector tile" output format, via the ST_AsMVT() function. Vector tiles are a transport format for efficiently sending map data from a server to a client for rendering. The vector tile specification describes how raw data are quantized to a grid and then compressed using delta-encoding to make a very small package. Prior to ST_AsMVT() , if you wanted to produce vector tiles from PostGIS you would use a rendering prog...
Read MorePaul Ramsey
Paul Ramsey
New features and better performance get a lot of attention, but one of the relatively unsung improvements in PostGIS over the past ten years has been inclusion in standard software repositories, making installation of this fairly complex extension a "one click" affair. Once you've got PostgreSQL/PostGIS installed though, how are upgrades handled? The key is having the right versions in place, at the right time, for the right scenario and knowing a little bit about how PostGIS works. To exercise...
Read MoreJonathan S. Katz
Jonathan S. Katz
Many applications these days want us to know how close we are to things: • What are the three closest coffee shops to my current location? • Which is the nearest airport to the office? • What are the two closest subway stops to the restaurant? What are the three closest coffee shops to my current location? Which is the nearest airport to the office? What are the two closest subway stops to the restaurant? and countless more examples. Another way of asking these questions is to say “who are my ne...
Read MoreJoe Conway
Joe Conway
This is the third and final post of the series intended to introduce PostgreSQL users to PL/R, a loadable procedural language that enables a user to write user-defined SQL functions in the R programming language . The information below provides sample use of R Functions against the NDVI dataset. As introduced in the previous posts, the combination of PostgreSQL and R provides users with the ability to leverage the power and efficiency of PostgreSQL and the rich analytic functionality of R. When...
Read MoreJoe Conway
Joe Conway
This is the second in a series of posts intended to introduce PostgreSQL users to PL/R , a loadable procedural language that enables a user to write user-defined SQL functions in the R programming language . This post builds on the example introduced in the initial post by demonstrating the steps associated with preprocessing the Normalized Difference Vegetation Index ( NDVI ) satellite raster data in preparation for spatial analytics. The first post in this series provided users with an...
Read MoreJoe Conway
Joe Conway
This is the first in a series of posts intended to introduce PostgreSQL users to PL/R, a loadable procedural language that enables a user to write user-defined SQL functions in the R programming language . When further combined with PostGIS, the geospatial extender for PostgreSQL, users can perform powerful spatial analytics within the PostgreSQL database. This initial post introduces PL/R and R, provides set up instructions for following the Spatial Analytics example to be used in this series...
Read More