Latest posts from Paul Ramsey
Six Degrees of Kevin Bacon - Postgres Style
Paul Ramsey has some great examples of Postgres network analysis and graph theory in this sample code for playing the Kevin Bacon game. Both pgRouting and recursive CTE are used to solve graphing relationships.
10 min readInside PostGIS: Calculating Distance
Paul dives inside the PostGIS distance calculations to show how efficient and robust these functions are.
5 min readPostGIS Clustering with K-Means
Paul has a primer on using clustering in PostGIS with the K-means algorithm. He uses a global population density map and offers some tips for geocentric and weighted clustering.
3 min readPostGIS 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 read
