Differences

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

--- osspatial [2015/04/20 16:41]
glaroc [Basic spatial queries on single layers]
+++ osspatial [2015/04/20 18:06] (current)
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 ====
-sum(expression) aggregate to return a sum for a set of records
+**sum**(expression) aggregate to return a sum for a set of records\\
-count(expression) aggregate to return the size of a set of records
+**count**(expression) aggregate to return the size of a set of records\\
-ST_GeometryType(geometry) returns the type of the geometry
+**ST_GeometryType**(geometry) returns the type of the geometry\\
-ST_SRID(geometry) returns the spatial reference identifier number of the geometry
+**ST_SRID**(geometry) returns the spatial reference identifier number of the geometry\\
-ST_X(point) returns the X ordinate
+**ST_X**(point) returns the X ordinate\\
-ST_Y(point) returns the Y ordinate
+**ST_Y**(point) returns the Y ordinate\\
-ST_Length(linestring) returns the length of the linestring
+**ST_Length**(linestring) returns the length of the linestring\\
-ST_StartPoint(geometry) returns the first coordinate as a point
+**ST_StartPoint**(geometry) returns the first coordinate as a point\\
-ST_EndPoint(geometry) returns the last coordinate as a point
+**ST_EndPoint**(geometry) returns the last coordinate as a point\\
-ST_Area(geometry) returns the area of the polygons
+**ST_Area**(geometry) returns the area of the polygons\\
-ST_Perimeter(geometry) returns the length of all the rings
+**ST_Perimeter**(geometry) returns the length of all the rings\\
-ST_AsSVG(geometry) returns SVG text
+**ST_AsSVG**(geometry) returns SVG text\\
 Sum the area of all seas
@@ Line 69: / Line 69: @@
 ==== 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 a distance in meters |
+<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