PyCon India - SQL

import sys; sys.version.split()[0]

0.2s

Python

'3.6.8'

pip install psycopg2-binary

3.6s

Bash in Python

import psycopg2
from psycopg2.extras import RealDictCursor
conn = psycopg2.connect("dbname='pyindia' user='py' host='gis.georeactor.com' password='zim'")
cursor = conn.cursor(cursor_factory=RealDictCursor)

0.4s

Python

SELECT-ing data from database

wkb_geometry column returns as a spatial data blob

cursor.execute("SELECT * from states LIMIT 1")
print(cursor.fetchall())
print("\n\n")

2.1s

Python

Use a PostGIS function such as ST_AsText or ST_AsGeoJSON to get data in the right format:

cursor.execute("SELECT ST_AsText(wkb_geometry) from states LIMIT 1")
print(cursor.fetchall())
print("\n\n")

1.5s

Python

Spatial + COUNT

cursor.execute("""SELECT COUNT(*)
	FROM cell_towers, bengaluru
  WHERE ST_Intersects(cell_towers.point, bengaluru.wkb_geometry)
""")
print(cursor.fetchall())
print("\n\n")

8.4s

Python

Spatial + JOIN

cursor.execute("""
WITH p AS (
		SELECT wkb_geometry, st_name, pc_name FROM parliament
)
SELECT ward_no, zone_no, st_name, pc_name
FROM chennai_wards
JOIN p ON ST_Intersects(chennai_wards.wkb_geometry, p.wkb_geometry)
ORDER BY ward_no""")
for row in cursor.fetchall():
  print ("Ward: {} in Parliament: {}".format(row["ward_no"], row["pc_name"]))
print("\n\n")

9.4s

Python