Use PostgreSQL file_fdw to Access External Data
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.
Pi 4 Performance: PostgreSQL and PostGIS
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.
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.
Webinar: PostGIS Advanced Features (6/6)
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.
Recording
Webinar: Postgres 12 and PostGIS 3 (5/6)
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.
Recording
qgis2web to export and share interactive maps
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.