Paul 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 MorePaul Ramsey
Paul Ramsey
Over and over when I look at applications for performance, the lesson I learn and re-learn is, do more things right inside the database . Create, read, update, delete! All the things you do to a table or collection of tables to work with your ever-changing data. Most CRUD examples, and most CRUD thinking, tend to focus on one table at a time. That's easy to understand. It's also unrealistic. Even the simplest application will be working with several interlinked normalized tables. Here's our wor...
Read MorePaul Ramsey
Paul Ramsey
One of my favourite (in an ironic sense) data formats is the "CSV in the CSV", a CSV file in which one or more of the column is itself structured as CSV. Putting CSV-formatted columns in your CSV file is a low tech approach to shipping a multi-table relational data structure in a single file. The file can be read by anything that can read CSV (which is everything?) and ships around the related data in a very readable form. But how can we interact with that extra data? If you want to try this blo...
Read MorePaul Ramsey
Paul Ramsey
Back when I first learned SQL, calculating percentages over a set of individual contributions was an ungainly business: • First calculate the denominator of the percentage, • Then join that denominator back to the original table to calculate the percentage. First calculate the denominator of the percentage, Then join that denominator back to the original table to calculate the percentage. This requires two passes of the table: once for the denominator and once for the percentage. For BI queries...
Read More