Paul 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 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 MorePaul Ramsey
Paul Ramsey
Generating random numbers is a surprisingly common task in programs, whether it's to create test data or to provide a user with a random entry from a list of items. PostgreSQL comes with just a few simple foundational functions that can be used to fulfill most needs for randomness. Almost all your random-ness needs will be met with the function. The function returns a double precision float in a continuous uniform distribution between 0.0 and 1.0. What does that mean? It means that you c...
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 MorePaul Ramsey
Paul Ramsey
Last week, Randall Munroe dropped his latest XKCD "Bad Map Projection", number six, " ABS(Longitude) ", which looks like this: Truly this is a bad map projection, on a par with the previous five: • Liquid Resize • Time Zones • South America • Greenland Special • Madagascator Liquid Resize Time Zones South America Greenland Special Madagascator The last two are just applications of common map projections with very uncommon projection parameters that accentuate certain areas of the globe, a c...
Read MorePaul Ramsey
Paul Ramsey
In my last blog post , I showed four ways to access a remotely hosted CSV file from inside PostgreSQL: • Using the command with the option, • Using the http extension and some post-processing, • Using a PL/Python function, and • Using the ogr_fdw foreign data wrapper. Using the command with the option, Using the http extension and some post-processing, Using a PL/Python function, and Using the ogr_fdw foreign data wrapper. In this post, we are going to explore ogr_fdw a little...
Read MorePaul Ramsey
Paul Ramsey
An extremely common problem in fast-moving data architectures is providing a way to feed ad hoc user data into an existing analytical data system. Do you have time to whip up a web app? No! You have a database to feed, and events are spiraling out of control... what to do? How about a Google Sheet? The data layout is obvious, you can even enforce things like data types and required columns using locking and protecting, and unlike an Excel or LibreOffice document, it's always online, so you can h...
Read MorePaul Ramsey
Paul Ramsey
In a previous life, I worked on a CRM system that really loved the idea of tags. Everything could be tagged, users could create new tags, tags were a key organizing principle of searching and filtering. The trouble was, modeled traditionally, tags can really make for some ugly tables and equally ugly queries. Fortunately, and as usual, Postgres has an answer. Today I’m going to walk through working with tags in Postgres with a sample database of 🐈 cats and their attributes • First, I’ll look at...
Read MorePaul Ramsey
Paul Ramsey
It's the easiest thing in the world to put a timestamp on a column and track when events like new records or recent changes happen, but what about reporting? Binning data for large data sets like time series is a great way to let you group data sets by obvious groups and then use SQL to pull out a query that easily works in a graph. Here's some PostgreSQL secrets that you can use to build up complete reports of time-based data. Earthquakes are a natural source of time-stamped data, and Crunchy B...
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 More