Transcript - Rensselaer Polytechnic Institute

GIS in the Sciences
ERTH 49xx
PostGIS and Spatial Queries
Peter Fox (thanks to Steve Signell)
Rensselaer Polytechnic Institute
October, 2016
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.
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
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)
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_joi
ns.html
WHERE…
WHERE….
Operator
Description
<
less than
>
greater than
<=
less than or equal to
>=
greater than or equal
to
=
equal
<> or !=
not equal
ALSO:
BETWEEN x AND y
Mathematical functions (+ - * /, etc.)
See http://www.postgresql.org/docs/9.2/static/functionsmath.html
Aggregate Functions
AGGREGATE functions: compute a single result from a set
of input values
http://www.postgresql.org/docs/9.2/static/functionsaggregate.html
avg(), sum(), min(), count()
Also statistics: corr(), regr_slope(), stdev()
Always have a ‘GROUP BY’ in the SQL statement
Spatial Databases
1. Spatial data types refer to shapes such as point, line,
and polygon;
2. Multi-dimensional spatial indexing is used for efficient
processing of spatial operations;
3. Spatial functions, posed in SQL, are for querying of
spatial properties and relationships.
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.
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.
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
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
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.
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?”
Comparison Functions
Comparison Functions
Comparison Functions
Comparison Functions
Comparison Functions
Comparison Functions
Part II: Demos
Trail Registers
Parking Areas
Campsites
Aquatic Invasives
Trail Registers
SELECT yr, SUM(people) FROM dec.trailregisterdata
WHERE asset_uid = '$regid' GROUP BY yr ORDER BY
yr ASC
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
Parking Areas
SELECT a.name, a.geom
FROM dec.asset a, dec.slt b
WHERE a.asset like '%PARKING%'
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'
Aquatic Invasive Species
This script runs on the 1st of every Month:
wget http://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;
Questions?
• Group Project status?
• Look at - Sections 1-9, Boundless PostGIS
Tutorial
http://workshops.boundlessgeo.com/postgis-intro/
26