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!
–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.”
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.
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