Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
osspatial [2015/04/20 16:41] glaroc [Spatial relationships between layers] |
osspatial [2015/04/20 18:06] glaroc [Spatial relationships between layers] |
||
|---|---|---|---|
| Line 44: | Line 44: | ||
| - Repeat these steps to import the "ProtectedAreas-Quebec.shp", "Hydrography-Surface.shp" and | - Repeat these steps to import the "ProtectedAreas-Quebec.shp", "Hydrography-Surface.shp" and | ||
| ==== Basic spatial queries on single layers ==== | ==== 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 | Sum the area of all seas | ||
| Line 93: | Line 93: | ||
| 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 | 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> | ||
| + | |||
| + | |||
| + | <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> | <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 | 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 | ||
