Absolute Basics
of PostGIS
...the first few hours after installation...
Slide 2
Slide 2 text
Overview
• What is PostGIS
• Getting data into PostGIS
• Basic everyday GIS queries
• Getting data out of PostGIS
Slide 3
Slide 3 text
Disclaimer / About
• I’m a Ruby / C# / NodeJS / Java developer
• I’m interested in apps with spatial aspects
• I’m a grumpy coder and a scuba diver
Slide 4
Slide 4 text
PostWTF
A set of geographic extensions for pgSQL
• Spatial Data Types
• Spatial Functions
• Spatial Indexing
Slide 5
Slide 5 text
It’s basically a set of tools that
helps you do stuff with geo*
data in PostgreSQL...
Slide 6
Slide 6 text
Spatial Data Types
...three primitive elements...
Slide 7
Slide 7 text
Point
• Cartesian (x, y, z) coordinates
• Represent locations such as:
• Buildings, Bus Stops, Bike Stations, etc
• Commonly GPS latitude and longitude
• POINT(-6.23709945 53.3411573)
Slide 8
Slide 8 text
LineString
• Multiple points connected by straight lines
• Represent thing such as:
• Roads, Routes, Pipes, Cables, etc
• Can be used to indicate a direction
• LINESTRING(30 10, 10 30, 40 40)
Slide 9
Slide 9 text
Polygon
• Multiple points which enclose an area
• Represent boundaries such as:
• Property, Town, County, Country, etc
• POLYGON(
(30 10, 10 20, 20 40, 40 40, 30 10))
Slide 10
Slide 10 text
Multipart
Each of the three primitives may be grouped
together to form a larger collection
1.MultiPoint
2.MultiLineString
3.MultiPolygon
Slide 11
Slide 11 text
Coordinate Reference Systems
...in far too little detail...
Slide 12
Slide 12 text
The world is not flat...
Slide 13
Slide 13 text
It’s not a sphere either!
Slide 14
Slide 14 text
It’s not even an ellipsoid
Slide 15
Slide 15 text
A Best Fit Model
Slide 16
Slide 16 text
A Global Best Fit
• GPS uses an ellipsoid called WGS84
• WGS84 has a Spatial Reference ID of 4326
• It basically approximates the earths surface
as the mean sea level*
• ESRI - Mean Sea Level, GPS, and the Geoid
CREATE TABLE
CREATE TABLE hospitals
(
id serial NOT NULL,
CONSTRAINT pk_hospitals PRIMARY KEY (id),
location geography NOT NULL,
location geometry(POINT, 4326) NOT NULL,
name text NOT NULL
);
Slide 20
Slide 20 text
INSERT INTO
INSERT INTO hospitals (name, location) VALUES
('Adelaide and Meath, Tallaght',
ST_POINT(-6.3788, 53.2911)),
('Mullingar Regional',
ST_GeogFromText('POINT(-7.3497 53.5346)'));
Slide 21
Slide 21 text
From KML
INSERT INTO hospitals (name, location)
VALUES (E'St James\'s',
ST_GeomFromKML(
'
-6.2953, 53.3401
'));
Slide 22
Slide 22 text
From GeoJSON
INSERT INTO hospitals (name, location)
VALUES (E'St James\'s',
ST_GeomFromGeoJSON('{
“type”:“Point”,
“coordinates”:[-6.2953, 53.3401]
}'));
Slide 23
Slide 23 text
shp2pgsql
• ESRI is a commercial organisation which
defined a file format called Shape (.shp)
• shp files are fairly ubiquitous and lots of
boundary data, etc exists in this format
• shp2pgsql takes shape files as input and
output a pg table with data inserts
•shp2pgsql regions.shp | psql talkdb
Slide 24
Slide 24 text
ogr2ogr
• It’s kind of like a rosetta stone for GIS
• Supports KML, GeoJSON, MapInfo Tab, ...
• Part of GDAL package:
1.brew install gdal --with-postgres!
• We’re obviously interested in pg:
2.ogr2ogr -f "PostgreSQL"
PG:"dbname=talkdb” locations.kml
Slide 25
Slide 25 text
Spatial Functions
Slide 26
Slide 26 text
SELECT name, ST_Distance(location,
ST_Point(-6.23709945, 53.3411573)::geography)
FROM hospitals ORDER BY 2
ST_Distance
Na#onal
Maternity,
Holles
Street 632.4519021
Royal
Victoria
Eye
and
Ear 1,570.022099
Rotunda 2,151.375497
…
Bantry
General 286,158.2834
Slide 27
Slide 27 text
SELECT name FROM hospitals
WHERE ST_DWithin(location,
ST_Point(-6.23709945, 53.3411573)::geography,
10000)
ST_DWithin
Royal
Victoria
Eye
and
Ear
St
James's
Coombe
Women's
…
13
rows
out
of
48
returned
Slide 28
Slide 28 text
ST_Union & ST_Area
• ST_Union takes a set of polygons and
aggregates them into one large boundary
with no intersecting regions
• SELECT provence, ST_Union(county)
FROM counties GROUP BY provence
• SELECT provence, ST_Area(ST_Union(county))
FROM counties GROUP BY provence
Slide 29
Slide 29 text
Too Many to List
• ST_Intersects(a,b) - Returns true if
geometry a shares any portion of space
with geometry b
• ST_Extent(set) - Returns a bounding box
which encloses all supplied geometries
• ST_Within(a,b) - Returns true if geometry
a is completely inside geometry b
Slide 30
Slide 30 text
Function Caveats
• Watch out for the order of arguments,
especially where longitude, latitude (x, y)
are concerned
• Not all functions support the geography
data type so you many needs to
ST_Transform them to geometries
Slide 31
Slide 31 text
Spatial Indexes
Slide 32
Slide 32 text
USING GiST(geom)
• CREATE INDEX gx_regions_geom ON
regions USING GiST(geom);
• Performance gains such as the previously
mentioned ST_DWithin
• Not all functions can use indexes though
Slide 33
Slide 33 text
Output
Slide 34
Slide 34 text
SELECT FROM
• PostGIS returns GIS data in hex encoded,
Well Known Binary (WKB) format
• SELECT location, ST_AsText(location)
FROM hospitals
0101000020E6100000613D187... POINT(-‐6.3788
53.2911)
0101000020E610000054D10E6... POINT(-‐6.2559
53.3327)
Slide 35
Slide 35 text
KML and GeoJSON
• Just as you can read data in from KML and
GeoJSON, you can output to it also
• SELECT
ST_AsKML(location),
ST_AsGeoJSON(location)
FROM hospitals
-‐6.3788,53.2911
{"type":"Point","coordinates":[-‐6.3788,53.2911]}"
Slide 36
Slide 36 text
In an Application
• Various libraries exist in multiple languages
for working with hex encoded WKB data
• GeoRuby is a very useful library for
converting to and from it in Ruby
include GeoRuby::SimpleFeatures
x = Geometry.from_hex_ewkb(raw)
raw = x.as_hex_ewkb !
• See also PPyGIS (Python), GeoTools (Java)
Slide 37
Slide 37 text
pgsql2shp
• Just like the previously mentioned
shp2pgsql but going the other way
• Exports a table or query to an ESRI Shape
file complete with associated attributes
•pgsql2shp -f output.shp talkdb hospitals!
•pgsql2shp -f output.shp talkdb \
”SELECT name, location FROM hospitals”
Slide 38
Slide 38 text
Questions
...with little guarantee of a definitive answer...