#Add Postgis extension:
create extension postgis
- 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))')));
What to polygons and linestrings look like?
- 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);
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
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)
- WKT
- WKB
- GeoJSON
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
select count(*)
from nyc_homicides h
inner join nyc_neighborhoods n
on ST_Contains(n.geom, h.geom)
where n.boroname = 'Brooklyn';
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?