Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more

Posts about Spatial

  • 6 min read

    Connecting QGIS to Postgres and PostGIS

    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 More
  • 3 min read

    PostGIS Clustering with K-Means

    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 More
  • 9 min read

    JSON and SVG from PostGIS into Google Sheets

    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 More
  • 4 min read

    PostGIS Clustering with DBSCAN

    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 More
  • 6 min read

    PostGIS Day 2023 Summary

    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 More
  • 6 min read

    Random Geometry Generation with PostGIS

    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 More
  • 9 min read

    SVG Images from Postgres

    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 More
  • Geocoding with Web APIs in Postgres

    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 More
  • Postgres Raster Query Basics

    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 More
  • 6 min read

    Temporal Filtering in pg_featureserv with CQL

    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