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 Steering Committee http://postgis.net 

The Gold Standard Base for Your Data • 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 software stacks. 

Why not just Use Files? • GeoJSON / TopoJSON / KML do not provide indexing - the whole file must be read to find something. • Shapefiles 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 Concurrency! Queries! 

An Extension Integrating with PostgreSQL • Indexable Geometry Types: for points, linestrings, polygons, multipoints, multilinestrings, multipolygons and geometrycollections. • Spatial Functions: area, distance, length and perimeter, union, difference, symmetric difference and buffers. • PostGIS WKT Raster Consists of: Most importantly… 

R-Tree Spatial Index 

–Wikipedia “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 rectangle.” 

A Spatial SQL Query SELECT census.*, customer.* FROM census JOIN customer ON ST_Contains( census.geom, customers.geom ); 

Basic Functions • ST_Length(A) • ST_Distance(A,B) • ST_Area(A) • ST_Intersects(A,B) • ST_Buffer(A, radius) • ST_Union() • ST_Contains() 

Formats • ST_AsGeoJSON() • ST_AsGML() • ST_AsKML() 

Projection SELECT f_table_name AS name, srid FROM geometry_columns; ! name | srid --------------------- +------- nyc_census_blocks | 26918 nyc_neighborhoods | 26918 nyc_streets | 26918 nyc_subway_stations | 26918 geometries | 0 SELECT * from geometry_columns; or 

Installation (The Easy Way) • OpenGeoSuite (Recommended) • http://boundlessgeo.com/ • Includes pgShapeLoader, pgAdmin3, QGIS, GeoServer, and more… OpenGeo Suite 4.0.2 

psql://postgis.cugos.org Provides everyone who attends CUGOS free, unrestricted access to a PostGIS database where we can experiment and exchange spatial data. Sponsored by: 

Share Spatial Data You Care About • You will be given write access to your database at postgis.cugos.org • 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. 

Email me: ! [email protected] Access 

Connect to Database • Use pgAdminIII - part of OpenGeo Suite 

Connection Credentials Fill in the following fields. ! Sub in your username. ! Ignore the other tabs. 

Import a Shapefile Use pgShapeLoader ( comes with OpenGeo Suite ) 

Connect to DB in pgShapeLoader 

Validate SRID Sometimes it does not automatically recognize the SRID of the shapefile. 

Open .prg and paste it. http://prj2epsg.org 

No content

Look at it in pgAdmin III Right click to view data. 

Take a look in QGIS Add PostGIS Table 

No content

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) USING ST_Transform(geom,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_fires ADD COLUMN geom_wgs84 geometry; ! UPDATE wa_lrg_fires SET geom_wgs84 = ST_Transform(geom,4326); 

Possible Workshop Topics • Architecture of the PostGIS extension to PostgreSQL and How it Relates. • 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