A presentation introducing the basics of PostGIS, the beginnings of postgis.cugos.org, and further topics on the subject to discuss in the future.
PostGIS @ CUGOS
Wed May 21 2014
What is PostGIS
"It is a Relational Database
System that in addition to text
and numbers and dates, it can
also index spatial objects -
points, lines, and polygons.”
-Paul Ramsey, Chair of PostGIS
The Gold Standard Base for
• PostGIS: Est. April 19, 2005
• PostgreSQL: Est. May 1, 1995
• Very well tested, massively used in production
• Industry Standard RDBMS
• An established, performant base system is a great
thing to have underneath progressive, bleeding edge
Why not just Use Files?
• GeoJSON / TopoJSON / KML do
not provide indexing - the whole
ﬁle must be read to ﬁnd something.
• Shapeﬁles do provide some
indexing, but do not allow more
than one simultaneous user. It also
has a vast list of limitations - we
won’t go into that.
• An Open Format
• Good place for a cohesive,
organized, single store
An Extension Integrating
• Indexable Geometry Types: for points,
linestrings, polygons, multipoints,
multilinestrings, multipolygons and
• Spatial Functions: area, distance, length
and perimeter, union, difference, symmetric
difference and buffers.
• PostGIS WKT Raster
R-Tree Spatial Index
“The key idea of the data structure is to group
nearby objects and represent them with their
minimum bounding rectangle in the next higher
level of the tree; the "R" in R-tree is for
A Spatial SQL Query
• ST_Buffer(A, radius)
SELECT f_table_name AS name, srid
name | srid
nyc_census_blocks | 26918
nyc_neighborhoods | 26918
nyc_streets | 26918
nyc_subway_stations | 26918
geometries | 0
SELECT * from geometry_columns;
Installation (The Easy Way)
• OpenGeoSuite (Recommended)
• Includes pgShapeLoader, pgAdmin3, QGIS,
GeoServer, and more…
OpenGeo Suite 4.0.2
Provides everyone who attends CUGOS
free, unrestricted access to a PostGIS
database where we can experiment and
exchange spatial data.
Share Spatial Data You Care
• You will be given write access to your database at
• All data will be readable by everyone
• Use this to experiment with PostGIS and share data
that may be useful to other CUGOSians.
• Ask me if it’s over a few GB. Don’t do the Planet of
OSM — we will do this for everyone later.
Connect to Database
• Use pgAdminIII - part of OpenGeo Suite
Fill in the
Sub in your
Ignore the other
Import a Shapeﬁle
Use pgShapeLoader ( comes with OpenGeo Suite )
Connect to DB in
Sometimes it does not automatically recognize
the SRID of the shapeﬁle.
Open .prg and paste it.
Look at it in pgAdmin III
Right click to view data.
Take a look in QGIS
Add PostGIS Table
Here it is!
We really want everything to be WGS84…
– Someone who does GIS
“WGS84 is EPSG4326, which is SRID 4326
which is Geographic, which is Lat/Lng, which is
Plate Carrée, which is Equirectangular.”
Reproject this data
… so that someone who does not
work for a state agency will want
to use it!
ALTER TABLE mytable
ALTER COLUMN geom TYPE geometry(MultiPolygon,4326)
However, what we really want is another
column with the new projection.
Create another column with
the same geometry reprojected
ALTER TABLE wa_lrg_ﬁres
ADD COLUMN geom_wgs84 geometry;
UPDATE wa_lrg_ﬁres SET geom_wgs84 =
Possible Workshop Topics
• Architecture of the PostGIS extension to PostgreSQL and How it
• Simplifying Adjacent Polygons Using PostGIS Topology.
• The Basics of SQL in the World of Postgres (Not GIS Per Se)
• Creating a Portable PostGIS on a USB Stick
• Putting OSM on PostGIS and Actually Creating Tiles
• Step By Step Guide: PostGIS -> Vector Tiles -> CartoCSS -> Map
• Useful Data Sources for PostGIS (Not Just OSM)
• PostGIS Raster
• Sanitizing Data for PostGIS