Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Paul 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 MorePaul Ramsey
Paul Ramsey
Let's put that on a map! PostGIS has so many cool features that it is possible to do full GIS analyses without ever leaving the SQL language, but... at the end of the day, you want to get those results, show that data, on the map. The Crunchy Data geospatial team has been thinking about how to bring PostGIS to the web, and we established some basic principles: • Use the database, as much as possible. • The database already has a user model and security model. • The database can already generat...
Read MorePaul Ramsey
Paul Ramsey
Where are you? Go ahead and figure out your answer, I'll wait. No matter what your answer, whether you said "sitting in my office chair" or "500 meters south-west of city hall" or "48.43° north by 123.36° west", you expressed your location relative to something else, whether that thing was your office layout, your city, or Greenwich . A geospatial database like PostGIS has to have able to convert between these different reference frames, known as "coordinate reference systems". The math for the...
Read MorePaul Ramsey
Paul Ramsey
While PostGIS includes lots of algorithms and functionality we have built ourselves, it also adds geospatial smarts to PostgreSQL by linking in specialized libraries to handle particular problems: • Proj for coordinate reference support; • GDAL for raster functions and formats; • GEOS for computational geometry (basic operations); • CGAL for more computational geometry (3D operations); and • for format support, libxml2, libjsonc, libprotobuf-c Proj for coordinate reference support; GDAL...
Read MorePaul Ramsey
Paul Ramsey
Parallel query has been a part of PostgreSQL since 2016 with the release of version 9.6 and in theory PostGIS should have been benefiting from parallelism ever since. In practice, the complex nature of PostGIS has meant that very few queries would parallelize under normal operating configurations -- they could only be forced to parallelize using oddball configurations . With PostgreSQL 12 and PostGIS 3, parallel query plans will be generated and executed far more often, because of changes t...
Read MorePaul Ramsey
Paul Ramsey
With the availability of MVT tile format in PostGIS via ST_AsMVT() , more and more people are generating tiles directly from the database. Doing so usually involves a couple common steps: • exposing a tiled web map API over HTTP • converting tile coordinates to ground coordinates to drive tile generation exposing a tiled web map API over HTTP converting tile coordinates to ground coordinates to drive tile generation Tile coordinates consist of three values: • zoom , the level of the tile...
Read MorePaul 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 More