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

PostGIS @ CUGOS

PostGIS @ CUGOS

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
Tweet

More Decks by Nicholas Hallahan

Other Decks in Programming

Transcript

  1. PostGIS @ CUGOS
    Wed May 21 2014

    View Slide

  2. 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

    View Slide

  3. 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.

    View Slide

  4. 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!

    View Slide

  5. 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…

    View Slide

  6. R-Tree Spatial Index

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  10. Formats
    • ST_AsGeoJSON()
    • ST_AsGML()
    • ST_AsKML()

    View Slide

  11. 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

    View Slide

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

    View Slide

  13. 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:

    View Slide

  14. 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.

    View Slide

  15. Email me:
    !
    [email protected]
    Access

    View Slide

  16. Connect to Database
    • Use pgAdminIII - part of OpenGeo Suite

    View Slide

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

    View Slide

  18. Import a Shapefile
    Use pgShapeLoader ( comes with OpenGeo Suite )

    View Slide

  19. Connect to DB in
    pgShapeLoader

    View Slide

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

    View Slide

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

    View Slide

  22. View Slide

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

    View Slide

  24. Take a look in QGIS
    Add PostGIS Table

    View Slide

  25. View Slide

  26. Here it is!
    We really want everything to be WGS84…

    View Slide

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

    View Slide

  28. 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.

    View Slide

  29. 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);

    View Slide

  30. 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

    View Slide