The State of PostGIS
pramsey@.org

G EO S ERVERG EO E XT {G EO W EB C ACHEP OST GIS

Whole product
Adding value w/ bundling, ease-of-use,
integration testing,support for all components‣ Types

Database‣ string, float, date

Spatial Database?

‣ Indexes‣ b-tree, hash

‣ Functions‣ strlen(string), pow(float, float), now()

PostGIS is a spatial database,
but what is a spatial database?

database provides
random access data storage system
types, indexes, functions for STANDARD TYPES‣ Spatial Types

Database‣ geometry, geography

Open Geospatial
Consortium (OGC)‣ Spatial Indexes‣ r-tree, quad-tree, kd-tree‣ Spatial Functions‣ ST_Length(geometry), ST_X(geometry)spatial database provides
random access data storage system
SPATIAL types, SPATIAL indexes, SPATIAL
functions

Simple Features for SQL(SFSQL)spatial databases follow international standard
SFSQL = Guideline for function names/behaviors
PostGIS is certified compliant, has been throughtestingSFSQL standard defines relationships between
geometries like intersection, union, difference,symmetric difference

spatial databases your likely to run into are
PostGIS, Oracle and SQL Server.
Others include
DB2 Spatial, Informix Spatial, Netezza, Teradata,
SpatialLite.
Spatial functionality has gone from being a rarity
to being a standard feature.

Is MySQL a spatial database?
Not yet. (Still)
There are spatial types and a spatial index.
The spatial types are defined in MyISAM tables
only, no transactions
The functions only work against bounding boxes.
There are patches to add wider support for truegeometry tests (http://forge.mysql.com/wiki/
GIS_Functions) but they have not been added toeither the general releases (v5.1) or to thedevelopment releases (v5.5) thus far.

Is SDE a spatial database?
No, it is spatial “middleware”. It sits on top of adatabase and interprets requests. Depending on
the database it sits on top of, it might passqueries back to a native spatial database (Oracle,
PostGIS, ST_Geometry) but SDE itself is not a
spatial database.

‣ Open Source (BSD)‣ “Enterprise” Database‣ ACID, hot backup, replication, partitioning

extends‣ triggers, constraints, foreign keys, userfunctions‣ PL/PGSQL, PL/Perl, PL/TCL, PL/Java, PL/R

‣ Corporate support‣ Enterprise DB‣ SRA

PostGIS is great spatial database,and it is build on top of a great standard
database,
PostgreSQL.
The core database provides transaction
management,
disk storage routines, SQL processing andplanning.
PostGIS provides spatial types, functions andindexes

By building on PostgreSQL, we get these goodthings!

What does
PostGIS do?

That is interesting, but... what does it do?
PostGIS answers questions that have a spatial
component.
It can answer questions on very large collectionsof spatial data,
and against very complex data models.

“What parcels arewithin 1km of thisfire?”

That sounds like a GIS question!
How many lines of code should it take tosolve it?

SELECT owner_phone
FROM parcels
WHERE ST_DWithin(
geom,‘POINT()’,
1000 );
One line!
Using one spatial function, a coordinate,
and a table of parcel data, we can generatea classic GIS “alert list” ofpeople to phone about the fire.

“How far did the bus
travel last week?”

SELECT
Sum(ST_Length(geom))
FROM
vehicle_paths
WHERE(v_id = 12)AND(v_date > Now() - ‘7d’);
One SQL statement to answer a location services
query.
SQL in a database is very powerful, more
powerful thandesktop GIS in terms of amount of code required
and the size ofdatasets than can be queried.

History!

“Managing changing
data in shape files is apain in the _____!”

I didn’t write PostGIS. I’ve been a major developer
since 2008.
The first versions (0.1 to 0.7) were done by Dave
Blasby on my left.
I did the build system, documentation and Javacomponents.
This picture is actually a little too new, at the time of
PostGIS 0.1(around 2001) there were only 5 people in company.

PostGIS was the answer to the question “how are we goingto manage this changing data?!”Database Advantages

Table

Date

Geometry

Haida

19991213

POLYGON()

Haida

20000213

POLYGON()

Haida

20000219

POLYGON()• Publish in one step

Carrier

20010412

POLYGON()

Carrier

20010421

POLYGON()• Manage in one place

Klahoose

20011023

POLYGON()

Klahoose

20011203

POLYGON()

We had a collection of “areas of interest” for treaty
negotiations that were passed through modelingsoftware. The negotiators would provide ever changing
versions they wanted analyses on, so knowing what thehistory was, was important.
<X>
Database model could provide history, easy publication
distribution, and automation of analysis runs.
(That was the theory, in fact we never got around to using
the database for this purpose.)• Query all areas at once

Type and
index

WKT
WKB

OGC
standards

0.1

0.5

0.6

History!!!
2001

2002
Distance

That was the beginning, but there is more.

Point-inpolygon

PgSQL
7.2

Reprojection

S

0.7
2003
Index
selectivity

20
JTS

Basic functionality all done in year one (2001).
Mapserver! P-i-P!
Started to implement SFSQL standards in 2002.
Complete full SFSQL in 2004.
Changed to a new binary format in 2005 andcomplete the transition and released 1.0 in 2006.

GEO

Regression
testsLinemerge

1.1
2006

ISO
SQL/MM

1.2
2007

LRS

SRS
performance

Cascaded
union

Performance

1.3
2008
Curves1.4
2009

Stability

2006 and 2007 added more functions,both ISO standard ones and speciality ones like
LineMerge and Polygonize.
2008 things stabilized and performance was the
key focus
2009 more performance with prepared geometry
anddeveloper improvements like code reorganization2010 and 1.5 added geography and GUI tools forloading

New
3D/4D
docs
Indexing

20112010

Preparedgeometry

Typemod for
GEOGRAPHYGEOMETRY

1.5

Achieves
sentience

Raster2.0
20122011

XP
2013

GUI
Developer
PolyhedralSurface loaderTopology
improvements

This year and most of last year has been about
2.0.
Changing the binary format again,
adding multi-dimensional indexes, rasters,
topology, 3D objects and more.
We’re looking forward to XP, which will be theultimate release,including a sentient interface.
Which may lead to some problems with the Vista
release, but we remain optimistic.

2014

More History!!!

More to history than just software features.
PostGIS has gone from curiosity to industry standard.

2001

2002

2003

2004

OSSIM

ArcGIS
Server
2006

2007

2008

2009

FDO

lots of proprietary companies at the end of thistimeline!

2010“Why are thesecompanies supporting
PostGIS?”2011because it makes them money

GeoConcept and CadCorp both supported PostGIS
because IGN (national mapping agency of France)
wanted PostGIS-enabled tools.
It gave them access to more business.

We want PostGIS!

Even the big three
ESRI, MapInfo and InterGraph have come on board
because their customers have been saying
they want it.

Who is using
PostGIS?

And who are those customers?
They are legion.

Corporations

On the private sector side,
WSI also known as the Weather channel
The New York Times, running PostGIS behind
their Django instance.
InfoTerra in the UK, managing all of Ordnance
Survey in their PostGISDigitalGlobe, managing their web-based image
delivery
SAIC, BAE, Ball Systems, all doing defence
systems.
RedFin and Zonar, startup companies,

Governments

On the government side,
National levels in France and Portugal and Canada
Regional levels like MN, and Quebec
Local levels like Pierce County, the City of St Paul,and many moreand quasi-governmental outfits like NREL and
NavCanada all use PostGIS.

Institut Geographique National

Database
Evaluation• PostGIS? DB2? Oracle?
• Can it handle 100M spatial

features?
• Can it do spatial transactions?
• Yes! Yes! Yes!

So, I mentioned (twice)that IGN is using PostGIS...what is IGN?
...what is BDuni?

Scalability
1 Dual-Core

2 Quad-Core

Oracle$40,000$160,000IBM DB2$36,400$145,600

MS SQL Server$25,000$50,000

IBM Informix$50,000$200,000

PostGIS

$0

$0

Price

“Enterprise”

VALUE

Functionality
Vendors like to talk about “scalability” as if customers have infinite money to address their problems. With
modern hardware, the main cost driver in scaling up an installation is no longer hardware, it’s software.
You can buy a dual-quad server with 24Gb of memory from Dell for less than $10K. Your software licensing
for that server will be many times your hardware cost, and that is not a good scalability situation.
Oracle Enterprise Edition pricing 2007-07: $40K * NCORES * 0.5 (for Standard Edition, use $15K instead of$40K)
IBM (2007-07) uses “value unit” now, https://www-112.ibm.com/software/howtobuy/passportadvantage/valueunitcalculator/vucalc.wss
100 value units for Intel dual core chip, 400 value units for dual Intel quads
DB2 Enterprise = $364 / value unit
DB2 Workgroup = $100 / value unit
Informix Enterprise = $500 / value unit
Microsoft (page updated 2005) SQLServer http://www.microsoft.com/sql/howtobuy/default.mspx
Only multiply by processor, not cores! Nice! http://www.microsoft.com/sql/howtobuy/multicore.mspx

other databases have good featuresor good prices
postgis has the best price/functionality

Cool Features!

OK, so that business talk is fine, but talk sometech to me.

L s!
Q
S nes
od
o
G

You have heard of the “NoSQL” movement,
perhaps?
The number one feature of PostGIS is that it
puts the power of hundreds of complex,
performance optimized
spatial functions at your fingertips
through powerful declarative language we callSQL,ed d s!
ts ee
Co ann ueri
Pl l Q
tiaSELECT ...
a
Sp FROM geotable_a aTryMy this
SQ on
L!

JOIN geotable_b b
ON ST_Intersects(b.geo,a.geo)
JOIN attrtable_c c
ON (b.id = c.id)
JOIN attrtable_d d
ON (a.id = d.id)

The spatial type is fully integrated into the query
planner.
Even the PostgreSQL gurus at PgCon didn’t know
this.
(So I added this slide)
That means that complex multi-table queries likethis one
execute efficiently.

n !
Fu atsrm
Fo

ST_AsGeoJSON()
ST_AsGML()
ST_AsKML()
ST_GeomFromGML()
ST_GeomFromKML()

Thanks to the work of Oslandia, PostGIS supports
a veritablezoo of XML and other hipster formats like JSONfor both output and input.

ry !
et ionom ucte
G strn
ST_Polygonize()Coar ng!
ein
Li encerf
ST_LocateAlong()
Re

ST_LineMerge()

ST_LocateBetween()

ST_Union()

ST_AddMeasure()

ST_Collect()

ST_Line_Locate_Point()

ST_BuildArea()
We have exposed the geometry processing
capabilitiesof the GEOS C++ library to allow fancy
constructionof geometries(come to my Friday talk for more on these)

There is a set of linear referencing functions so
you can build data models that include bothmeasuredand proportional linear references.

ed
d
ca n!
s
Ca Unio

And our standard functions keep getting faster.
The cascaded union improvement came in version
1.4
The picture is the example we were sent:“why is this operation so slow” he asked
Cascaded union merges polygons in the optimalorder.
It made this example 40 times faster.

ed
d
ca n!
s
Ca Unio

Cascaded union does that melting process in aparticular order
Merging neighbors first
This example became 5 times faster.

d
re y!
a
ep etrr
P om
SELECT ...
Ge

FROM points, polygons
WHERE ST_Intersects (polygons.geom,
points.geom)

Similarly, prepared geometries make standard
spatial join queries faster.

d
re y!
a
ep etrr
P om
Ge

Calculating (for example) a point-in-polygon isquite expensive.

d
re y!
a
ep etrr
P om
Ge

Point in Polygon

First you draw a stab line.
Then you check every edge to see if it hits theline.
Then you count up the number of hits.
Odd => inside.
Even => outside.

d
re y!
a
ep etrr
P om
Ge

Point in Polygon = O(n)This point has three hits, so it’s inside.
Calculate cost is proportional to the number ofedges.

dre y!
aep etrr
P om
Ge

Build spatial index on edges!
Point-in-polygon == O(log(n))
Line-in-polygon == O(m•log(n))

d
re y!
a
ep etrr
P om
Ge Prepared geometry makes

repeated tests on large
geometries very fast.
But,
Building an index takes O(n)So,
Cache index and re-use it!

But, if we index the edges, we can get the costdown to log(N)!
But indexing the edges takes O(n) time!
So we temporarily cache the index the first time
we build it
and use it on subsequent queries that hit thesame polygon.

(ST_Intersects,
ST_Contains)
that’s fun geek talk
important to remember...
between 2 and 5 times faster depending on
complexity of inputs(more complex, means better improvement)

s
ve

!

r
Cu • CURVESTRING

y!
h
ap
r
og
e
G

• COMPOUNDCURVE• CURVEPOLYGON• ST_CurveToLine()• ST_LineToCurve()

Since 1.2, we’ve had curve types
which are part of ISO SQL/MM standard
And our curve support has been getting more
complete with each release.
You can convert curves to linestrings, and evenconvert linestrings to curves!
Curve types are useful for storing CAD data,
which uses curves

In 1.5, we added the geography typemodels data on a sphere

y!
h
ap
r
og
e
G

“geometry” type models data on a plane
but that Simple Plate-Carre view, has lots ofproblem cases.

y!
h
ap
r
og
e
G

a polygon that covers the pol

y!
h
ap
r
og
e
G

is wrong if you interpret it on the plane

y!
h
ap
r
og
e
G

a polygon that crosses the dateline

y!
h
ap
r
og
e
G

thinks it crosses the whole world if you interpret iton the plane
this wasn’t just a problem for postgis

y!
h
ap
r
og
e
G

even google earth has a hard time with thedateline and polygons
also the poles

y!
h
ap
r
og
GeoNewbieseG

Who isgeographyfor?

“I want to find all the address
points within one mile!
My data is in lat/lon!
Google Maps rocks!”

There two kinds of users who find GEOGRAPHY
usefulthe first is “geonewbies”, users who do not know
any GIS oranything about map projections
GEOGRAPHY lets them work with lat/lon data
without knowing about projections

y!
h
ap
r
og
e
G

GeoHugies

Who isgeography
for?

“Yeah, I own a freaking
satellite, you got a problem
with that?”

The “geohugies” are really big organizations thathave
truly global data, that covers the poles anddatelines
and everything in between there is no map
projectionthat works for them

y!
h
ap
r
GeographygoGe • Indexes spherical data functions?
• ST_Intersects()• ST_Distance()• ST_DWithin()• ST_Area()• Casts to/from GEOMETRY

the implementation of GEOGRAPHY currently only
has support for a few functions
but you can use casts to convert to GEOMETRY
and access all the geometry function

s

2
hp

p

q
gs

l

PostGIS 1.5 added a GUI loader tool
The 2.0 version includes the ability to load
multiplefiles in a batch!

!
.0
2
!
W
E
N

TypMod

N

!
.0
2
!
EW

3D/4D Index

CREATE TABLE
my_spatial_table(
id INTEGER,
name VARCHAR(64),geo GEOMETRY(Point,26910));

CREATE INDEX my_index
ON my_spatial_table
USING GIST (geom,
gist_nd_geometry_ops);

In 2.0 (come on Friday to see Regina and Leo’s
talk)about the 2.0 “good stuff” (don’t know if thiscounts)“TypMod” support will let you declare the typeand srid and dimsionalityof a geometry right at table CREATE time.

Yes, in 2.0 you will be able to create indexes andsearch in 3D and 4D!

!
.0
2
!
W
E
N

Topology

In 2.0 you’ll be able to create topologies!

N

!
.0
2
! Raster for Analysis
EW

raster is not for image storage
for analysis
vector->raster->vector
map algebra, operations in raster space

!
.0
2
!
W
E
N

3D Objects

N

!
.0
2
!
EW

IndexedNearest-Neighbor
Searchsponsored bypolyhedral objects are good for modellingbuildings
3D and 4D indexes will be good for fast retrievalof those objects

And (this hasn’t been coded yet, but before
October!)
index-based nearest-neighbor searching!
Find me the closest X to Y.

The State of PostGIS
pramsey@

2.0!?!

.org

When is 2.0 coming?!!?!
We have to stabilize the features we have, andclose tickets
Realistically, not before early 2012.
Yes, we want it to be done too!

Thanks!