PostGIS in a Notebook

A notebook is useful for adding sections of text, command-line / bash commands, and Python code, all in one place. If we use a graphing library we can show that on this screen too.

The server behind this notebook already has Python 3 installed.

First step is to install any Python modules, in this case psycopg2 to connect to PostgreSQL databases. Click the play button at right:

pip install psycopg2-binary

2.8s

Bash in Python

If everything goes well, you should be able to import that module and connect to this database which I set up. This user has only read-only privileges. I add a Python code block here:

import psycopg2
from psycopg2.extras import RealDictCursor

try:
    # use this read-only user to connect to the DB - it works on your computer too!
    # you can also run ```pip install pgcli``` to run SQL in your console
    conn = psycopg2.connect("dbname='jserb' user='js' host='gis.georeactor.com' password='erb'") # port=####
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    print("Connected to the database!")
except:
    print("I am unable to connect to the database")

0.8s

Python

Variables from the last code block are still available in future blocks.

# here we select all rows and columns from a 'trains' table
# you will see that each train has an id, start, finish, price, and number of seats
# if this fails, try running the previous code block!
cursor.execute('SELECT * FROM planes')
result = cursor.fetchall()
print(result)

0.7s

Python

Here's an example of how I could SELECT GeoJSON data from a table called 'health'

cursor.execute("SELECT ST_ASGeoJSON(point) AS point, name FROM health")
result = cursor.fetchone()
print("\n")
print(result)
print("\n")

1.1s

Python

With a geo query (how many health points within 3km of a point)

lng = 44.6169996
lat = 32.7530979
dist = 3000
cursor.execute("SELECT COUNT(*) FROM health \
	WHERE ST_Distance(point, ST_SetSRID(ST_MakePoint(%s, %s), 4326)) < %s",
               (lng, lat, dist))
result = cursor.fetchall()
print(result)

0.8s

Python