Week 3 - Spatial Databases, Part II

• Homework (not graded)
• Lecture
  • Slide Deck
  • Code snippets
• Lab
  • Lab without answers
  • Lab with answers - includes a new question (#6)
• Recommended Reading

Homework

All homework items are optional but strongly encouraged.

• SQL practice at codecademy:
  • Aggregate Functions
  • Multiple Tables
• PostGIS tutorials
  • Import the datasets into your Carto account. See Getting Started section for data download. You can just import this zip file into your carto account.
  • Spatial Relationships: reading and exercises
  • Spatial Joins: reading and exercises

Lecture

PostGIS Examples

You can reproduce the maps shown in lecture using the following queries. Be sure to change your schema/user name.

ST_Centroid

SELECT ST_Transform(g, 3857) as the_geom_webmercator, g as the_geom, cartodb_id
FROM (
  SELECT ST_Centroid(the_geom) as g, cartodb_id
  FROM andyepenn.university_city_osm_buildings
) as _w

ST_PointOnSurface

SELECT ST_Transform(g, 3857) as the_geom_webmercator, g as the_geom, cartodb_id
FROM (
  SELECT ST_PointOnSurface(the_geom) as g, cartodb_id
  FROM andyepenn.university_city_osm_buildings
) as _w

ST_GeneratePoints

SELECT ST_Transform(g, 3857) as the_geom_webmercator, g as the_geom, cartodb_id
FROM (
  SELECT ST_GeneratePoints(the_geom, 15) as g, cartodb_id
  FROM andyepenn.university_city_osm_buildings
) as _w

ST_Buffer

SELECT ST_Transform(g, 3857) as the_geom_webmercator, g as the_geom, cartodb_id
FROM (
  SELECT ST_Buffer(the_geom::geography, 25)::geometry as g, cartodb_id
  FROM andyepenn.university_city_osm_buildings
) _w

ST_Intersection

SELECT the_geom, ST_Transform(the_geom, 3857) as the_geom_webmercator, row_number() over() as cartodb_id
FROM (
  SELECT ST_Intersection(o.the_geom, b.g) as the_geom
  FROM (
      SELECT ST_Buffer(the_geom::geography, 25)::geometry as g, cartodb_id
      FROM andyepenn.university_city_osm_buildings
  ) as b
  JOIN andyepenn.university_city_osm_buildings as o
  ON ST_Intersects(o.the_geom, b.g) and o.cartodb_id <> b.cartodb_id
) as _abc

JOIN query to try out

I used the following query to generate the data in the lecture

Trip Data

SELECT start_station, duration, start_time, end_time
FROM andyepenn.indego_trips_2020_q2
where start_station in (3007, 3125, 3052)
LIMIT 5

Station data

SELECT ST_AsText(the_geom) as geom, id, name
FROM andyepenn.indego_station_status
where id in (3007, 3125, 3052)

Joined data

SELECT start_station, duration, start_time, end_time, geom, id, name
FROM (
  SELECT start_station, duration, start_time, end_time
  FROM andyepenn.indego_trips_2020_q2
  where start_station in (3007, 3125, 3052)
  LIMIT 5
  ) as trips
JOIN (
  SELECT ST_AsText(the_geom) as geom, id, name
  FROM andyepenn.indego_station_status
  where id in (3007, 3125, 3052)
 ) AS stations
 ON trips.start_station = stations.id

Cover Slide

SELECT the_geom, ST_Transform(the_geom, 3857) as the_geom_webmercator, start_station, end_station, count(*) as num_trips, row_number() over () as cartodb_id
FROM (
  SELECT ST_MakeLine(ST_SetSRID(ST_MakePoint(start_lon, start_lat), 4326), ST_SetSRID(ST_MakePoint(end_lon, end_lat), 4326)) as the_geom, cartodb_id, start_station, end_station
  FROM andyepenn.indego_trips_2019_q2
  ) as _w
GROUP BY 1, 2, 3, 4

Recommended Reading

• Geography type
• PostGIS Geometry Returning Functions
• SafeGraph Patterns blog post -- skip the first couple of paragraphs ;)