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:42] glaroc [Basic spatial queries on single layers] |
osspatial [2015/04/20 18:06] (current) glaroc [Spatial relationships between layers] |
||
|---|---|---|---|
| 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 | ||
