gis_workshop.org

PGIS Intro

Spatial Stuff!

Postgres Extensions

Installing Postgis (Postgres Extension)

#Add Postgis extension:

create extension postgis

Postgis Basics

Data Types

• Points
• Polygons
• LineStrings
• Multi-things

Examples

-- Turing Point
select ST_Point(-104.996604, 39.750837);

-- Turing Polygon
select ST_GeometryType(ST_GeomFromText('POLYGON((-104.9964988231659 39.75115187915411,-104.99618232250214 39.75088379783092,-104.99657392501831 39.75055797451056,-104.99691188335419 39.7508260571017,-104.9964988231659 39.75115187915411))'));

-- How many points in the polygon?
select ST_NumPoints(ST_ExteriorRing(ST_GeomFromText('POLYGON((-104.9964988231659 39.75115187915411,-104.99618232250214 39.75088379783092,-104.99657392501831 39.75055797451056,-104.99691188335419 39.7508260571017,-104.9964988231659 39.75115187915411))')));

GIS Geometry Representations

What to polygons and linestrings look like?

Predicates / Query Functions

• ST_Area
• ST_Perimeter
• ST_Distance
• ST_Contains
• ST_Intersects
• ST_DWithin

select ST_Contains(ST_GeomFromText('POLYGON((-104.9964988231659 39.75115187915411,-104.99618232250214 39.75088379783092,-104.99657392501831 39.75055797451056,-104.99691188335419 39.7508260571017,-104.9964988231659 39.75115187915411))'),
                   ST_Point(-104.996604, 39.750837));

-- Union Station Point
select ST_Point(-105.000125, 39.753163);

select ST_Distance(ST_Point(-105.000125, 39.753163), ST_Point(-104.996604, 39.750837));
-- ASIDE: Units and Geography vs Geometry!?!?

select ST_Distance(ST_Point(-105.000125, 39.753163)::geography, ST_Point(-104.996604, 39.750837)::geography);

See the docs: https://postgis.net/docs/reference.html#Geometry_Constructors

Working with More Data

Data borrowed from this great tutorial: https://postgis.net/workshops/postgis-intro/index.html

postgis_workshop.zip

https://postgis.net/workshops/postgis-intro/creating_db.html

https://postgis.net/workshops/postgis-intro/loading_data.html

Examining Data

Tables:

               List of relations
 Schema |        Name         | Type  | Owner
--------+---------------------+-------+--------
 public | nyc_census_blocks   | table | 
 public | nyc_homicides       | table | 
 public | nyc_neighborhoods   | table | 
 public | nyc_streets         | table | 
 public | nyc_subway_stations | table | 
 public | spatial_ref_sys     | table | 

                                     Table "public.nyc_neighborhoods"
  Column  |             Type             |                            Modifiers
----------+------------------------------+-----------------------------------------------------------------
 gid      | integer                      | not null default nextval('nyc_neighborhoods_gid_seq'::regclass)
 boroname | character varying(43)        |
 name     | character varying(64)        |
 geom     | geometry(MultiPolygon,26918) |
Indexes:
    "nyc_neighborhoods_pkey" PRIMARY KEY, btree (gid)
    "nyc_neighborhoods_geom_idx" gist (geom)

Crude Visualization

Aside: GIS Serialization Formats

• WKT
• WKB
• GeoJSON

Aside: SRID’s

Consider:

select ST_AsGeoJSON(ST_SetSRID(geom, 4326)::geography) from nyc_neighborhoods limit 1;

Versus:

select ST_AsGeoJSON(ST_Transform(geom, 4326)::geography) from nyc_neighborhoods limit 1 offset 1;

Getting All of them:

psql -d gis_workshop -c "COPY (select ST_AsGeoJSON(ST_Transform(ST_Collect(ARRAY(select geom from nyc_neighborhoods limit 5)), 4326))) to STDOUT;" | pbcopy

Analysis

Aside: Power of GIS Joins

select count(*)
from nyc_homicides h
inner join nyc_neighborhoods n
on ST_Contains(n.geom, h.geom)
where n.boroname = 'Brooklyn';

What do you wanna know!?

Possible Q’s

• Most populous borough?
• Most populous borough by race?
• # of homicides in most populous census district?
• # of subway stations per borough? Per neighborhood?