Spatial Data Processing with
PostgreSQL & PostGIS

Işıl Demir
VisioThink Inc.

May 2012

What is spatial data?

Geospatial Data is information with geographic attributes, describing features and boundaries on Earth, usually stored as coordinates and topology.

Nowadays, any data can be geospatial, thanks to Facebook, Twitter, Foursqaure, Google Places etc.

What is a geospatial database?

Well where else would we store all this spatial data?

A spatial database is a database that is optimized to store and query data that is related to objects in space, including points, lines and polygons.

Spatial Measurements
Spatial Functions
Spatial Predicates
Constructor Functions
Observer Functions

What does spatial data look like?

PostGIS spatially enables the PostgreSQL server

Why spatial databases?

Maps are cool
They are even cooler when used for data visualization purposes

Population distribution across departments in France

http://kartograph.org

Crime data for some US cities

http://kartograph.org

Unemployment rates in Spain

http://kartograph.org

Why spatial databases?

Location Intelligence
The capacity to organize and understand complex phenomena through the use of geographic relationships by combining geographic- and location-related data with other business data

Finance
Healthcare
Media: Target Market

Why PostgreSQL & PostGIS?

Free, Open source
Big, live community
Seamless integration with PHP, MapServer, OpenLayers
PostGIS follows the Simple Features for SQL specification from the Open Geospatial Consortium (OGC)
Handles big data, no problem

Loading spatial data

OpenStreetMap - Daily updates, big shape files
shp2pgsql
CSV

Talking GIS: Projections, spatial references

Once you install PostGIS, you will have two very important tables:

geometry_columns
spatial_ref_sys

spatial_ref_sys

This tables lists over 3000 known spatial reference systems and details needed to transform/reproject between them

srid	spatial reference string
4326	GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84", 6378137, 298.257223563, AUTHORITY["EPSG","7030"]], TOWGS84[0,0,0,0,0,0,0], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]], UNIT["degree",0.01745329251994328, AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4326"]]

The spatial reference system you use to project your geographic data is very important!
Every spatial measurement and function depends on it!

Turkey with UTM Zone 35N and WGS84 projections

spatial_ref_sys

// WGS84 (World Geodetic System; standard in cartography, navigation)
SELECT * FROM public.spatial_ref_sys WHERE auth_srid = 4326;
// UTM35N (Transverse Mercator projection for Turkey)
SELECT * FROM public.spatial_ref_sys WHERE auth_srid = 32635;

srid

spatial reference string

4326

GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84", 6378137, 298.257223563, AUTHORITY["EPSG","7030"]], TOWGS84[0,0,0,0,0,0,0], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]], UNIT["degree",0.01745329251994328, AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4326"]]

32635

"PROJCS["WGS 84 / UTM zone 35N", GEOGCS["WGS 84", DATUM["WGS_1984",SPHEROID["WGS 84", 6378137, 298.257223563, AUTHORITY["EPSG","7030"]], TOWGS84[0,0,0,0,0,0,0], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]], UNIT["degree",0.01745329251994328, AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4326"]], PROJECTION["Transverse_Mercator"], PARAMETER["latitude_of_origin",0], PARAMETER["central_meridian",27], PARAMETER["scale_factor",0.9996], PARAMETER["false_easting",500000], PARAMETER["false_northing",0], UNIT["metre",1,AUTHORITY["EPSG","9001"]], AUTHORITY["EPSG","32635"]]"

Transverse Mercator Projection of the World

The true size of Africa

The coolness of PostGIS?

You can project one coordinate to another spatial reference by using ST_Transform.

Simple spatial queries

Create a point

SELECT st_geomfromtext('POINT(29.009399 41.043045)', 4326);

st_geomfromtext (geometry)
0101000020E6100000779D0DF967023D40CFA0A17F82854440

Same point, different projection

SELECT st_astext(
 st_transform(st_geomfromtext('POINT(29.009399 41.043045)', 4326), 32635));

st_astext (text)
POINT(668888.377502433 4545480.51687201)

Some spatial functions

st_within
st_buffer
st_distance
st_union
st_intersect
st_centroid

http://postgis.refractions.net/docs/reference.html

ST_Distance: Why projections are important

Let's calculate the distance between Istanbul and Ankara

SELECT st_distance(
  st_geomfromtext('POINT(28.983307 41.017211)', 4326), 
  st_geomfromtext('POINT(32.852554 39.930274)', 4326));

st_distance (double precision)
4.01901783884794

SELECT st_distance_sphere(
  st_geomfromtext('POINT(28.983307 41.017211)', 4326), 
  st_geomfromtext('POINT(32.852554 39.930274)', 4326));

st_distance_sphere (double precision)
348848.891192477 ~ 350 km

ST_Distance: Why projections are important

Let's calculate the distance between Istanbul and Ankara

SELECT st_distance(
 st_transform(st_geomfromtext('POINT(28.983307 41.017211)', 4326), 32635), 
 st_transform(st_geomfromtext('POINT(32.852554 39.930274)', 4326), 32635));

st_distance (double precision)
349949.722459394

But we know for sure the distance is actually 453km!

pgRouting project: extends the PostGIS/PostgreSQL geospatial database to provide geospatial routing functionality

Online maps

Now that we can store and query spatial objects, how do we display the results on a map?

OpenStreetMap: The data provider
MapServer: Map tile renderer
OpenLayers: Stitch it together with some magical JavaScript

In a nutshell; use PostgreSQL+PostGIS to store the geographic data provided by OpenStreetMap, build your own map using MapServer and display it with OpenLayers