PostGIS on macOS

postgre-postico

# add to your path
PATH=/Applications/Postgres.app/Contents/Versions/11/bin/:$PATH

# check that your $PATH is set correctly
which createdb
which psql

Create Database

# bash
# Check version
psql --version # psql (PostgreSQL) 11.6

export DATABASE=ne    # Natural Earth
export USER=postgres  # default
export SCHEMA=dog
export TABLE=cat

# Create Database
psql --command="create database ${DATABASE};"
createdb ${DATABASE}

# Create Schema
psql -d ${DATABASE} --command="create schema ${SCHEMA};"

# Enable PostGIS + raster + topology
psql -d ${DATABASE} --command="CREATE EXTENSION postgis;           -- Enable PostGIS (includes raster)"
psql -d ${DATABASE} --command="CREATE EXTENSION postgis_topology;  -- Enable Topology"

Add GeoJSON using ogr2ogr

INPUT_GEOJSON=test.geojson

ogr2ogr -f "PostgreSQL" \
  PG:"dbname=${DATABASE} user=${USER}" \
  -nln ${SCHEMA}.${TABLE} \
  ${INPUT_GEOJSON}

Add Shapefile using shp2pgsql

Use shp2pgsql to convert from a GeoJSON to a Shapefile. This writes a .sql file to disk. See shp2pgsql -? for command line switches.

OUTPUT_SHP=test.shp
ogr2ogr -f "ESRI Shapefile" ${OUTPUT_SHP} ${INPUT_GEOJSON}
shp2pgsql ${OUTPUT_SHP} ${SCHEMA}.${TABLE} > ${OUTPUT_SHP}.sql
psql -d ${DATABASE} -f $OUTPUT_SHP.sql

Test

# output
psql -d ${DATABASE} --command="SELECT name FROM ${SCHEMA}.${TABLE} LIMIT 10;"

Sample GeoJSON

Save this to test.geojson

{"type" : "FeatureCollection", "features" : [{"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "properties": {"id": 1, "name": "one"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[2,2]}, "properties": {"id": 2, "name": "two"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[3,3]}, "properties": {"id": 3, "name": "three"}}]}
Pull in Natural Earth Data
# navigate to
#   https://www.naturalearthdata.com/downloads/10m-cultural-vectors/
unzip ne_10m_admin_1_states_provinces.zip
shp2pgsql ne_10m_admin_1_states_provinces.shp naturalearth > naturalearth.sql

psql -d $DATABASE -f naturalearth.sql

AWS AMI Linux

Tested on AWS Lightsail

# From https://superuser.com/a/617299
sudo yum -y install postgresql postgresql-server postgresql-contrib
sudo service postgresql initdb
sudo /sbin/chkconfig --levels 235 postgresql on
sudo service postgresql start

# By default, postgres creates a user named 'postgres'
psql --version   # psql (PostgreSQL) 9.2.24