PostGIS and Spatial Queries

1. GIS in the Sciences ERTH 49xx PostGIS and Spatial Queries Peter Fox (thanks to Steve Signell) Rensselaer Polytechnic Institute October, 2016

2. Spatial Databases PostGIS is a spatial database. Oracle Spatial and SQL Server 2008 are also spatial databases. But what does that mean; what is it that makes an ordinary database a spatial database? The short answer, is. . . Spatial databases store and manipulate spatial objects like any other object in the database.

3. Databases & SQL: Review The four “verbs” of SQL (Structured Query Language) SELECT, returns rows in response to a query INSERT, adds new rows to a table UPDATE, alters existing rows in a table DELETE, removes rows from a table

4. Databases & SQL: Review SELECT QUERIES Required: SELECT (field(s)) FROM (tables) Optional: JOIN (combines two FROM items) WHERE (conditions) GROUP BY (fields– used for AGGREGATE functions) ORDER BY (field(s)) LIMIT (# rows returned)

5. JOIN Combines two FROM items (tables) using a common identifier. Purpose: http://www.wiki.gis.com/wiki/index.php/Spatial_Join Usage example: http://www.qgistutorials.com/en/docs/performing_spatial_joins.html

6. WHERE… WHERE…. ALSO: BETWEEN x AND y Mathematical functions (+ - * /, etc.) See http://www.postgresql.org/docs/9.2/static/functions-math.html

7. Aggregate Functions AGGREGATE functions: compute a single result from a set of input values http://www.postgresql.org/docs/9.2/static/functions-aggregate.html avg(), sum(), min(), count() Also statistics: corr(), regr_slope(), stdev() Always have a ‘GROUP BY’ in the SQL statement

8. Spatial Databases 1. Spatial data types refer to shapes such as point, line, and polygon; 2. Multi-dimensionalspatial indexing is used for efficient processing of spatial operations; 3. Spatial functions, posed in SQL, are for querying of spatial properties and relationships.

9. Spatial Indexing Spatial indexing & Bounding Boxes Answering the question “is A inside B?” is very computationally intensive for polygons but very fast in the case of rectangles. Even the most complex polygons and linestrings can be represented by a simple bounding box.

10. Spatial Functions Conversion: Functions that convert between geometries and external data formats. Management: Functions that manage information about spatial tables and PostGIS administration. Retrieval: Functions that retrieve properties and measurements of a Geometry. Comparison: Functions that compare two geometries with respect to their spatial relation. Generation: Functions that generate new geometries from others.

11. Conversion Functions • Well-known text (WKT) • ST_GeomFromText(text, srid) returns geometry • ST_AsText(geometry) returns text • ST_AsEWKT(geometry) returns text • Well-known binary (WKB) • ST_GeomFromWKB(bytea) returns geometry • ST_AsBinary(geometry) returns bytea • ST_AsEWKB(geometry) returns bytea • Geographic Mark-up Language (GML) • ST_GeomFromGML(text) returns geometry • ST_AsGML(geometry) returns text • Keyhole Mark-up Language (KML) • ST_GeomFromKML(text) returns geometry • ST_AsKML(geometry) returns text • GeoJSON • ST_AsGeoJSON(geometry) returns text • Scalable Vector Graphics (SVG) • ST_AsSVG(geometry) returns text

12. Management Functions AddGeometryColumn - Adds a geometry column to an existing table of attributes. DropGeometryColumn - Removes a geometry column from a spatial table. DropGeometryTable - Drops a table and all its references in geometry_columns. PostGIS_Version - Returns PostGIS version number and compile-time options. Populate_Geometry_Columns - Ensures geometry columns have appropriate spatial constraints and exist in the geometry_columns table. Probe_Geometry_Columns - Scans all tables with PostGIS geometry constraints and adds them to the geometry_columns table if they are not there. UpdateGeometrySRID - Updates the SRID of all features in a geometry column, geometry_columns metadata and srid table constraint

13. Retrieval Functions ST_Area: Returns the area of the surface if it is a polygon or multi-polygon. For “geometry” type area is in SRID units. For “geography” area is in square meters. ST_Length: Returns the 2d length of the geometry if it is a linestring or multilinestring. geometry are in units of spatial reference and geography are in meters (default spheroid) ST_NPoints: Returns the number of points (vertexes) in a geometry. ST_NumGeometries: If geometry is a GEOMETRYCOLLECTION (or MULTI*) returns the number of geometries, otherwise return NULL. ST_Perimeter: Returns the length measurement of the boundary of an ST_Surface or ST_MultiSurface value. (Polygon, Multipolygon) ST_StartPoint: Returns the first point of a LINESTRING geometry as a POINT. ST_X: Returns the X coordinate of the point, or NULL if not available. Input must be a point. ST_Y: Returns the Y coordinate of the point, or NULL if not available. Input must be a point.

14. Comparison Functions Questions like “Which are the closet bike racks to a park?” or “Where are the intersections of subway lines and streets?” can only be answered by comparing geometries representing the bike racks, streets, and subway lines. Other Questions: “What neighborhood and borough is Atlantic Commons in?” “What streets does Atlantic Commons join with?” “Approximately how many people live on (within 50 meters of) Atlantic Commons?”

15. Comparison Functions

16. Comparison Functions

17. Comparison Functions

18. Comparison Functions

19. Comparison Functions

20. Comparison Functions

21. Part II: Demos Trail Registers Parking Areas Campsites Aquatic Invasives

22. Trail Registers SELECT yr, SUM(people) FROM dec.trailregisterdata WHERE asset_uid = '$regid' GROUP BY yr ORDER BY yrASC SELECT a.asset_uid, b.mo, b.month, AVG(a.people)::integer FROM dec.trailregisterdata a INNER JOIN argis.month_lookup b ON a.mo=b.mo WHERE asset_uid = $regid GROUP BY b.mo, a.asset_uid, b.month ORDER BY b.mo ASC 21800

23. Parking Areas SELECT a.name, a.geom FROM dec.asset a, dec.slt b WHERE a.asset like '%PARKING%'

24. Campsites near Trails SELECT DISTINCT a.name, ST_AsGeoJSON(ST_Transform(a.geom,4326),6) as geojson FROM dec.asset a, dec.slt b WHERE a.asset = 'PRIMITIVE CAMPSITE' AND st_dwithin(a.geom,b.geom,200) AND a.name != 'NULL'

25. Aquatic Invasive Species This script runs on the 1st of every Month: wgethttp://www.adkinvasives.com/database/apippexport.asp -O /tmp/apipp.csv psql-d argis < /home/steve/bin/import_apipp.sql Import_apipp.sql: DELETE FROM apipp.aquatic COPY apipp.aquatic FROM '/tmp/apipp.csv' CSV HEADER ; UPDATE argis.metadata SET last_updated = CURRENT_DATE WHERE tablename='apipp.aquaticinvasives'; CREATE OR REPLACE VIEW apipp.aquaticinvasives AS SELECT DISTINCT a.pond, a.station_area, a.abundance, a.surveydate, a.species, b.geom, b.gnis_name FROM apipp.aquatic a LEFT JOIN apipp.lake b ON a.pond = b.pond_num::bpchar ORDER BY a.pond, a.surveydate;

26. Questions? • Group Project status? • Look at - Sections 1-9, Boundless PostGIS Tutorial http://workshops.boundlessgeo.com/postgis-intro/