OverviewTeaching: 15 min Exercises: 30 minQuestions
What are the various ways that we can represent geospatial information?
How does a database store spatial information?Objectives
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
What is the most common crime within 5 km of my house?
SELECT latitude, longitude FROM seattlecrimeincidents LIMIT 5;
What is the straight line distance between the first two points in the table above?
DISCUSSION: Calculating distances in geographic coordinates
- is it possible for us to calculate straight line distances directly from latitude and longitude data?
- Why or why not?
- Are distances between lines of latitude always the same? Between lines of longitude?
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');
PostGIS: a geospatial database extension
- many databases, have extensions that allow for encoding of geospatial information
- in the case of PostgreSQL this extension is called PostGIS
- our sample database already has the PostGIS extension installed and enabled
ALTER TABLE seattlecrimeincidents ADD COLUMN geom geometry(Point, 4326);
Spatial Reference System Identifier (SRID)
4326is the SRID for the geographic (latitude/longitude) coordinate system
- SRIDs are a convenient way to keep track of the hundreds of different reference systems
- another label for these is
EPSGand you can access the list of codes here
UPDATE seattlecrimeincidents SET geom = ST_setSRID(ST_MakePoint(longitude, latitude), 4326);
ALTER TABLE seattlecrimeincidents ADD COLUMN geom_utm geometry(Point, 3717);
Now we carry out the transformation
UPDATE seattlecrimeincidents SET geom_utm = ST_Transform(geom,3717);
STwhich means “Spatial Tool”
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;
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?
- 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
- 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
All geospatial analysis requires a knowledge of reference frames and coordinate systems
Be sure to use the correct coordinate system to match your analysis, otherwise you can get unexpected results
many databases have extensions that encode geospatial information (e.g. PostGIS)
PostGIS functions provide a wide range of tools to incorporate spatial analysis into your workflow