Teaching: 15 min
Exercises: 30 min
  • What are the various ways that we can represent geospatial information?

  • How does a database store spatial information?

  • Learn the difference between geographic and projected coordinate systems

  • Explore why understanding the coordinate reference frame matters when carrying out basic geospatial analysis

  • Become familiar with some of the geospatial toolkits within PostGIS

Calculating distances

SELECT latitude, longitude
FROM seattlecrimeincidents 
Latitude Longitude
47.6158384 -122.3181689
47.60087709 -122.3312162
47.59582098 -122.3175691
47.6140991 -122.3174884
47.63148825 -122.3125079

What is the straight line distance between the first two points in the table above?

DISCUSSION: Calculating distances in geographic coordinates

Map Projections

Encoding of geospatial information

How does the database currently encode the latitude and longitude information?

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'seattlecrimeincidents' AND (column_name = 'Latitude' OR column_name = 'Longitude');
column_name data_type
Longitude double precision
Latitude double precision

PostGIS: a geospatial database extension

Database Geometries

ALTER TABLE seattlecrimeincidents ADD COLUMN geom geometry(Point, 4326);
Point Latitude Longitude geom
1 47.6158384 -112.3181689  
2 47.60087709 -112.3312162  

Spatial Reference System Identifier (SRID)

UPDATE seattlecrimeincidents SET geom = ST_setSRID(ST_MakePoint(longitude, latitude), 4326);
Point Latitude Longitude geom
1 47.6158384 -112.3181689 0101000020E6100000AD0617E15C945EC07CCFEDCAD3CE4740
2 47.60087709 -112.3312162 0101000020E6100000F2B96EA532955EC09A3B5D8AE9CC4740

Geospatial functions

  1. We add a new column that has a projected geometry
    • in this case we will use ‘Universal Transverse Mercator Zone 10 North’, which has a SRID of 3717:
     ALTER TABLE seattlecrimeincidents
     ADD COLUMN geom_utm geometry(Point, 3717);
  2. Now we carry out the transformation

     UPDATE seattlecrimeincidents
     SET geom_utm = ST_Transform(geom,3717);
    • note that all PostGIS functions begin with ST which means “Spatial Tool”
  3. Now we can recalculate our distance using a built-in PostGIS distance function:

     SELECT ST_Distance(a.geom_utm,b.geom_utm)
     FROM seattlecrimeincidents AS a, seattlecrimeincidents AS b
     WHERE a.gid=1 AND b.gid=2;



Compare results

How does this result compare to the distance we calculated from the latitude/longitude data alone? What explains the difference?

What is the most common crime within 1 km of my house?

  1. Find the coordinates of your house (or some other feature in the Seattle region). HINT: you can use google maps to find the latitude and longitude of any map location
  2. Build the SQL query. You will need to nest 4 different functions to do this:
    • ST_MakePoint: to make a vector point geometry from your lat/long coordinate pair
    • ST_SetSRID: to tell the database which SRID your lat/long pair conforms to
    • ST_Transform: to transform your point geomtery to a projected coordinate system (use UTM Zone 10, ‘3717’)
    • ST_Distance: to calculate the distance between all the geometries in the database and your single point

Key Points