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.

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:
    !
    nhallahan@spatialdev.com
    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