RustProof Labs: blogging for education (logo)

Use PostgreSQL file_fdw to Access External Data

By Ryan Lambert -- Published March 27, 2020

Loading external data is a core task for many database administrators. With Postgres we have the powerful option of using Foreign Data Wrappers (FDW) to bring in external data sources. FDWs allow us to access data that is external to the database by using SQL from within the database. These external data sources can be in a number of formats, including other relational databases (Postgres, Oracle, MySQL), NoSQL sources (MongoDB, Redis), raw data files (csv, JSON) and many more.

This post shows how to use file_fdw to load remote data from CSV files available from GitHub. Sharing data via public source control tools like GitHub has become a common way to make data sets widely available. Other public data is available from various government and non-profit sites, so this is a handy tool to have available and reuse.

External data source

For this post I am using the COVID-19 data John Hopkins University is curating. See the main GitHub page for full attribution and meta-details about the data.

Continue Reading

Pi 4 Performance: PostgreSQL and PostGIS

By Ryan Lambert -- Published March 14, 2020

Happy Pi (π) Day! I decided to celebrate with another post looking at the Raspberry Pi 4's performance running Postgres and PostGIS. A few months ago I added another Raspberry Pi to my collection, the new Model 4 with 4 GB RAM. My initial review focused on pgbench results comparing the Pi 4 against the previous 3B models as well as the even lower-powered Raspberry Pi Zero W. This post continues testing the Rasperry Pi 4's performance with PostgreSQL and PostGIS, this time with a look at a more suitable setup for production use cases. The main difference are the use of an external SSD drive and full-disk encryption.

Raspberry Pi Logo (Small)

Hardware and Configuration

The Raspberry Pi 4 is mounted in an enclosed Cana Kit case with a small fan in the top running on the 3.3 V rail (lower power), powered by a dedicated 3.5A power supply.

Continue Reading

Webinar: PostGIS Advanced Features (6/6)

By Ryan Lambert -- Published February 29, 2020

This page has the resources for the PostGIS Advanced Features webinar from Tuesday March 4, 2020. This session is the sixth and final (!) in a series of six covering how I work with PostGIS and OpenStreetMap. See the intro page for links to the other videos in the series.


Continue Reading

Webinar: Postgres 12 and PostGIS 3 (5/6)

By Ryan Lambert -- Published February 26, 2020

Below is the recording of my PostgreSQL 12 and PostGIS 3 webinar from February 26, 2020. This is the fifth in a series of six covering how I work with PostGIS and OpenStreetMap.

I had a great time presenting this session, I hope having this recording available is helpful. Below the video is a list the links showing throughout the slides.


Continue Reading

qgis2web to export and share interactive maps

By Ryan Lambert -- Published February 20, 2020

QGIS is an open-source GUI for working with GIS data, both for spatial analysis and creating beautiful maps. One of my favorite components of QGIS is its qgis2web plugin that makes it quick and easy to convert a styled QGIS map into a browser-based slippy map that can be easily shared. qgis2web currently supports generating output in three (3) popular formats: OpenLayers, Leaflet, and MapBox GL JS. This post covers the basic process of preparing a QGIS project to generate polished interactive maps via qgis2web.

I discovered QGIS a few years ago. Over time it has become my go-to desktop GUI for GIS work.

Click here for the interactive map.

QGIS Project

I setup an example project in QGIS that uses an OpenStreetMap base layer (from QuickMapServices plug-in) plus two layers of U.S. Census Bureau data loaded from a PostGIS database I had handy. One layer provides boundaries and labels for the counties in Colorado, the other visualizes population density by block group.

Continue Reading

<-- Older Posts          Newer Posts -->