PostGIS with MAPublisher and QGIS

, , , ,

This is a personal note-to-self more than a “how to”. Using PostGIS on a Mac seems easier than it used to. Installation of Postgres is straight forward using the installer and instructions available from postgresapp.com, and managing the data using pgAdmin works well and is well documented. Adding PostGIS extensions is straight forward.  (such as CREATE EXTENSION postgis;). Installation is shown at around minute 5 in the video.

Why I might use PostGIS

By using a postgres/postgis database, I can keep a consistent dataset that is available to both QGIS and Adobe Illustrator using MAPublisher. I can’t do this using a geopackage at the moment.

Postgres/PostGIS also offers access to geometry functions, though some of this can be done within a geopackage. GeoPackage Routing: Executing Advanced Analysis Offline | OGC.

Caveats for M1 Chip use – January 20, 2021

I’m installing on a new Macbook Pro with an M1 Processor. The installer for osm2pgsql, located at Installation – osm2pgsql, doesn’t seem to be working on an Arm Processor. But maybe it will soon. Here’s the github note. 

I’m not sure about running the TIGER Geocoder. I was thinking about this for analyzing Pennsylvania Voter Data when I feel like geocoding millions of addresses by block.

Installation

Installation is straight forward. Go to this site, postgresapp.com and download the installation file.
 
In addition, you can install a graphic interface pgAdmin – PostgreSQL Tools.

Creating a database with PostGIS using pgAdmin

CREATE DATABASE [name];
 
After creating the database, refresh the database list if the new database doesn’t show up in the list.

Adding PostGIS extensions

There will be an extensions folder in the database. Click on it so that you are in the right database.
  • CREATE EXTENSION postgis;
  • CREATE EXTENSION postgis_raster;
  • CREATE EXTENSION fuzzystrmatch; (probably not needed)
These directions do not account for TIGER geocoding.

Download and Add shapefiles

The easiest way to do this is directly in QGIS. Create a new database connection in QGIS, which will need info for the following:
  • database name,
  • the user id,
  • localhost (127.0.0.1),
  • port 5432.
And then export data from layers in QGIS into the database.

Final thoughts

I don’t have a plan for how I’ll use a local PostGIS database, but I may start by storing layers that I use more often. It’s been so easy for me to download a statewide shapefile from geofabrik directly into Illustrator that I haven’t bothered to worry about PostGIS for my projects. But I’ll definitely use it for important datasets.