Differences

This shows you the differences between two versions of the page.

--- osspatial [2015/04/17 18:07]
glaroc [Exercise]
+++ osspatial [2015/04/20 18:05]
glaroc [Spatial relationships between layers]
@@ Line 44: / Line 44: @@
   - Repeat these steps to import the "ProtectedAreas-Quebec.shp", "Hydrography-Surface.shp" and
 ==== Basic spatial queries on single layers ====
-ST_Length
+**sum**(expression) aggregate to return a sum for a set of records\\
-ST_Area
+**count**(expression) aggregate to return the size of a set of records\\
-ST_
+**ST_GeometryType**(geometry) returns the type of the geometry\\
+**ST_SRID**(geometry) returns the spatial reference identifier number of the geometry\\
+**ST_X**(point) returns the X ordinate\\
+**ST_Y**(point) returns the Y ordinate\\
+**ST_Length**(linestring) returns the length of the linestring\\
+**ST_StartPoint**(geometry) returns the first coordinate as a point\\
+**ST_EndPoint**(geometry) returns the last coordinate as a point\\
+**ST_Area**(geometry) returns the area of the polygons\\
+**ST_Perimeter**(geometry) returns the length of all the rings\\
+**ST_AsSVG**(geometry) returns SVG text\\
-Sum the area of all districts
+Sum the area of all seas
 <file postgresql>
-SELECT tri_cep, sum(ST_Area(geom)) FROM section_vote_31h5_utm GROUP BY tri_cep
+SELECT iho_sea, sum(ST_Area(eez_iho.geom)) as area FROM eez_iho GROUP BY iho_sea ORDER BY area DESC
+</file>
+Extract latitudes and longitudes of each occurrence for "Delpinus delphis". Note: we have to use a subquery and the ST_Dump function in this case because the whale occurrence data was imported as a MULTIPOINT data and ST_X/ST_Y expects POINT data.
+<file postgresql>
+SELECT ST_X(geom), ST_Y(geom) FROM (SELECT (ST_Dump(geom)).geom  FROM obis_whales WHERE tname='Delphinus delphis') a
 </file>
 ==== Spatial relationships between layers ====
+ST_Contains(geometry A, geometry B): Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.
+ST_Crosses(geometry A, geometry B): Returns TRUE if the supplied geometries have some, but not all, interior points in common.
+ST_Disjoint(geometry A , geometry B): Returns TRUE if the Geometries do not “spatially intersect” - if they do not share any space together.
+ST_Distance(geometry A, geometry B): Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units.
+ST_DWithin(geometry A, geometry B, radius): Returns true if the geometries are within the specified distance (radius) of one another.
+ST_Equals(geometry A, geometry B): Returns true if the given geometries represent the same geometry. Directionality is ignored.
+ST_Intersects(geometry A, geometry B): Returns TRUE if the Geometries/Geography “spatially intersect” - (share any portion of space) and FALSE if they don’t (they are Disjoint).
+ST_Overlaps(geometry A, geometry B): Returns TRUE if the Geometries share space, are of the same dimension, but are not completely contained by each other.
+ST_Touches(geometry A, geometry B): Returns TRUE if the geometries have at least one point in common, but their interiors do not intersect.
+ST_Within(geometry A , geometry B): Returns true if the geometry A is completely inside geometry B
+Count the occurrences of each species of whales found in Canadian waters.
+<file postgresql>
+SELECT a.tname, count(*) as num FROM obis_whales a, eez_iho b WHERE ST_Within(a.geom,b.geom) AND b.country='Canada' GROUP BY a.tname ORDER BY num DESC
+</file>
+<file postgresql>
+SELECT a.* FROM obis_whales a, tm_world b WHERE ST_DWithin(a.geom,b.geom,1) AND b.name='Canada
+</file>
+++++ Doing it with distance calculations |
+<file postgresql>
+ALTER TABLE obis_whales ADD column geog GEOGRAPHY(MULTIPOINT,4326)
+</file>
+<file postgresql>
+UPDATE obis_whales SET geog=geography(geom)
+</file>
+<file postgresql>
+ALTER TABLE tm_world ADD column geog GEOGRAPHY(MULTIPOINT,4326)
+</file>
+<file postgresql>
+UPDATE tm_world SET geog=geography(geom)
+</file>
+<file postgresql>
+SELECT a.* FROM obis_whales a, tm_world b WHERE ST_DWithin(a.geog,b.geog,1) AND b.name='Canada
+</file>
+++++
 <file postgresql>
 CREATE TABLE ce03_gbif_occ AS SELECT a.*, count(b.geom) as num_occ FROM cer03 a LEFT JOIN gbif_mammals b ON (ST_Within(a.geom,b.geom)) GROUP BY a.id