useful notes, commands, queries for PostGIS

PostGIS_cheatsheet.md

https://www.codementor.io/devops/tutorial/getting-started-postgresql-server-mac-osx

Installation (macOs)

Use Homebrew to install Postgres and PostGIS. If Homebrew is not installed, run:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Install PostgresL:

brew install postgresql

Install PostGIS:

brew install postgis

Postgres

Start Postgres server and make sure it starts everytime the computer starts:

pg_ctl -D /usr/local/var/postgres start && brew services start postgresql

Make sure Postgres is installed and running:

postgres -V

Install pgAdmin4:

brew cask install pgadmin4

Force restart:

rm -rf /usr/local/var/postgres && initdb /usr/local/var/postgres -E utf8
pg_ctl -D /usr/local/var/postgres -l logfile start

psql Command Line

psql postgres

Documentation

Create a database and grant privileges to a user

From psql command line:

CREATE DATABASE <DATABASE_NAME>;
GRANT ALL PRIVILEGES ON DATABASE <DATABASE_NAME> TO <USERNAME>;

Connect to database:

\connect <DATABASE_NAME>

PostGIS:

CREATE EXTENSION postgis;

Check to see that a PostGIS database is running

psql -U <USERNAME> -d <DATABASE_NAME> -c "SELECT postgis_version();"

Use shp2pgsql to load a shapefile into PostGIS

shp2pgsql -I -s <SRID> <PATH/TO/SHP> <NEW_TABLE_NAME> | psql -U <USERNAME> -d <DATABASE_NAME>

Load all shapefiles in a directory into PostGIS

#!/bin/bash

for f in *.shp
do
    shp2pgsql -I -s <SRID> $f `basename $f .shp` > `basename $f .shp`.sql
done

for f in *.sql
do
    psql -d <DATABASE_NAME> -f $f
done