Upgrade to Pro — share decks privately, control downloads, hide ads and more …



A presentation introducing the basics of PostGIS, the beginnings of postgis.cugos.org, and further topics on the subject to discuss in the future.

Nicholas Hallahan

May 22, 2014

More Decks by Nicholas Hallahan

Other Decks in Programming


  1. 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
  2. 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.
  3. 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!
  4. 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…
  5. –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.”
  6. A Spatial SQL Query SELECT census.*, customer.* FROM census JOIN

    customer ON ST_Contains( census.geom, customers.geom );
  7. 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
  8. Installation (The Easy Way) • OpenGeoSuite (Recommended) • http://boundlessgeo.com/ •

    Includes pgShapeLoader, pgAdmin3, QGIS, GeoServer, and more… OpenGeo Suite 4.0.2
  9. 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:
  10. 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.
  11. Connection Credentials Fill in the following fields. ! Sub in

    your username. ! Ignore the other tabs.
  12. – 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.”
  13. 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.
  14. 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);
  15. 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