Latest posts from Paul Ramsey
PostGIS Clustering with DBSCAN
Paul walks through creating cluster maps with ST_ClusterDBScan. He grabs some geographic name data, creates the clustered data, and retrieves a population density map for the U.S.
4 min readRolling the Dice with the PostgreSQL Random Functions
Paul shows examples of generating random numbers, random integers, random text values, and random groups. He also shows off the new random_normal function which just came out with Postgres 16.
6 min readRandom Geometry Generation with PostGIS
Did you know you can use random() with spatial data types? Paul has sample code for generating random points, lines, polygons, hulls, and even some shapes using a Voronoi diagram. Bonus examples of random_normal(), coming in Postgres 16.
6 min read
XKCD Bad Map Projection with PostGIS
In honor of the beloved XKCD comic, Paul recreates some bad maps using PostGIS functions. Check out these funky cartographic contortions and learn a little spatial sql along the way.
4 min readRemote Access Anything from Postgres
ogr_fdw is a powerhouse of options for accessing data from within Postgres. Paul digs into samples with CSV, Excel from S3, and SQLite.
5 min readHoly Sheet! Remote Access CSV Files from Postgres
Want to get CSV data into your database? Paul has some great tools to help you use CSV and Google Sheets. Like remote copy, http access, using Python for materialized views, and ogr_fdw. You heard that right, there's a foreign data wrapper for CSV to Postgres!
7 min readTags and Postgres Arrays, a Purrrfect Combination
Are you using tags in your database with some of your main database properties? Paul reviews some of the ways to store tags in a database from basic relational models to text arrays. He provides some performance tests, sample queries, and guidance on choosing the best path.
7 min readEasy PostgreSQL Time Bins
Paul has some fast and easy tricks to show you how to get time series data into nice reportable data charts. Using functions like floor(), generate_series(), width_bucket(), and date_bin() you can bin your data in groups any way you like and retrieve charts in a flash.
7 min readPostgres Raster Query Basics
Paul takes us through how rasters are stored and queried in Postgres/PostGIS. He gives us the must have tips for dealing with rasters inside a database using digital elevation mode (DEM) examples.
6 min readSQL Tricks for More Effective CRUD
Level up your SQL skills with this quick tutorial of some simple and not-so-simple create, read, update, and delete (CRUD) statements.
7 min readPostgres Strings to Arrays and Back Again
Time to have some fun with strings and arrays. Learn how to separate strings into arrays, find array size, and turn comma separated lists into individual rows.
4 min readPercentage Calculations Using Postgres Window Functions
Learn how to do percentage calculations in one-pass. Sample data and queries for basic use of window functions.
3 min readPostgres Insider Terminology
If you've ever wondered - What is a tuple? What is a Postgres page? What's the difference between a record and a row? What is a relation? What's an array? What is TOAST? This post is for you! These are terms you hear all the time in the Postgres world. This post breaks down what these terms mean and how they are used.
7 min readMoving Objects and Geofencing with Postgres & PostGIS
A hands on demonstration for building real-time maps and geofences for moving objects. We will use the newly announced pg_enventserv for eventing along with pg_featureserv for a web API to build a fully functional web application.
5 min readBe Ready! Public schema changes in Postgres 15
Big changes are coming to the public schema settings in Postgres 15. There is no more global write privilege to public. Paul breaks down best practices on how to easily give permissions by role.
4 min readReal-time Database Events with pg_eventserv
Introducing our newest open-source project, pg_eventserv! pg_eventserv takes events generated by the PostgreSQL NOTIFY command and passes the payload along to waiting WebSockets clients.
5 min readGenerate Unlimited Crypto Using Postgres!
Just kidding. This is not crypto mining tutorial! It is a very practical description of using pgcrypto for encrypting and decrypting data inside your Postgres database.
9 min readRise of the Anti-Join
Find me all the things in set "A" that are not in set "B". Paul has some suggestions of when to use the anti-join pattern in queries with some impressive results.
5 min readPostgres Indexing: When Does BRIN Win?
Wondering about how to choose between BRIN and BTree indexes? Read about the best cases for BRIN indexes with some testing against BTree.
6 min readChoosing a PostgreSQL Number Format
Paul does a deep dive on number data types. Read about how to choose a number type that's most efficient for your data and storage requirements.
5 min read
