Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Elizabeth Christensen
Elizabeth Christensen
QGIS, the Quantum Geographic Information System, is an open-source graphical user interface for map making. QGIS works with a wide variety of file types and has robust support for integrating with Postgres and PostGIS. Today I just wanted to step through getting QGIS connected to a Postgres database and the basic operations that let you connect the two systems. Connecting QGIS to Postgres is very similar to any other GUI or application, you’ll need the database host, login, and password details....
Read MorePaul Ramsey
Paul Ramsey
Clustering points is a common task for geospatial data analysis, and PostGIS provides several functions for clustering. • ST_ClusterDBSCAN • ST_ClusterKMeans • ST_ClusterIntersectingWin • ST_ClusterWithinWin ST_ClusterDBSCAN ST_ClusterKMeans ST_ClusterIntersectingWin ST_ClusterWithinWin We previously looked at the popular DBSCAN spatial clustering algorithm, that builds clusters off of spatial density. This post explores the features of the PostGIS ST_ClusterKMeans function. K-means cluste...
Read MoreElizabeth Christensen
Elizabeth Christensen
At PostGIS Day 2023 , one of our speakers showed off a really cool demo for getting JSON and SVGs in and out of Postgres / PostGIS and into Google Sheets. Brian Timoney put together several open source projects in such a cool way that I just had to try it myself. If you want to see his demo video, it is on YouTube . With Brian’s blessing, I’m writing up some additional details with a few of the sample code bits for those of you that want to try this or something similar for your own projects....
Read MorePaul Ramsey
Paul Ramsey
A common problem in geospatial analysis is extracting areas of density from point fields. PostGIS has four window clustering functions that take in geometries and return cluster numbers (or NULL for unclustered inputs), which apply different algorithms to the problem of grouping the geometries in the input partitions. • ST_ClusterDBSCAN • ST_ClusterKMeans • ST_ClusterIntersectingWin • ST_ClusterWithinWin ST_ClusterDBSCAN ST_ClusterKMeans ST_ClusterIntersectingWin ST_ClusterWithinWin The ST_Clus...
Read MoreElizabeth Christensen
Elizabeth Christensen
We hosted our annual PostGIS day a couple weeks ago with some great talks on a big variety of topics within open-source GIS. Here is a summary of the themes I saw take shape across the day’s events that will point you towards the recordings, depending on your interests. A full playlist of PostGIS Day 2023 is available on our YouTube channel. If you’ve spent time with developers this year you know that folks love to tell you the details and reasoning behind their tech stack and the GIS communit...
Read MorePaul Ramsey
Paul Ramsey
A user on the postgis-users had an interesting question today: how to generate a geometry column in PostGIS with random points, linestrings, or polygons? Random data is important for validating processing chains, analyses and reports. The best way to test a process is to feed it inputs! Random points is pretty easy -- define an area of interest and then use the PostgreSQL function to create the X and Y values in that area. Filling a target shape with random points is a common use case, and...
Read MoreMartin Davis
Martin Davis
PostGIS excels at storing, manipulating and analyzing geospatial data. At some point it's usually desired to convert raw spatial data into a two-dimensional representation to utilize the integrative capabilities of the human visual cortex. In other words, to see things on a map. PostGIS is a popular backend for mapping technology, so there are many options to choose from to create maps. Data can be rendered to a raster image using a web map server like GeoServer or MapServer ; it can be conv...
Read MoreJacob Coblentz
Jacob Coblentz
Geocoding is the process of taking addresses or location information and getting the coordinates for that location. Anytime you route a new location or look up a zip code, the back end is geocoding the location and then using the geocode inside other PostGIS functions to give you the routes, locations, and other data you asked for. PostGIS comes equipped with an easy way to use the US Census data with the Tiger geocoder . Using the Tiger geocoder requires downloading large amounts of census d...
Read MorePaul Ramsey
Paul Ramsey
In geospatial terminology, a "raster" is a cover of an area divided into a uniform gridding, with one or more values assigned to each grid cell. A "raster" in which the values are associated with red, green and blue bands might be a visual image. The rasters that come off the Landsat 7 earth observation satellite have eight bands: red, green, blue, near infrared, shortwave infrared, thermal, mid-infrared and panchromatic. Working with raster data via SQL is a little counter-intuitive: rasters...
Read MoreMartin Davis
Martin Davis
In a previous post we announced the CQL filtering capability in . It provides powerful functionality for attribute and spatial querying of data in PostgreSQL and PostGIS. Another important datatype which is often present in datasets is temporal . Temporal datasets contain attributes which are dates or timestamps. The CQL standard defines some special-purpose syntax to support temporal filtering. This allows to take advantage of the extensive capabilities of PostgreSQL for specifying qu...
Read More