"The explosive growth of the GeoWeb and geographic information has made GIS powerful media for the general public to communicate, but perhaps more importantly, GIS have also become media for constructive dialogs and interactions about social issues." - Sui & Goodchild
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
geoinf16:sqlcartodb [2016/11/25 08:16] oertz |
geoinf16:sqlcartodb [2018/05/16 10:05] (current) |
||
---|---|---|---|
Line 17: | Line 17: | ||
* A vous de jouer en donnant de bonnes infos | * A vous de jouer en donnant de bonnes infos | ||
<code> | <code> | ||
- | INSERT INTO earthquakes (place, mag, the_geom) VALUES ('SOMEWHERE, Switzerland', MMM, ST_Transform(ST_GeometryFromText('POINT(XXX YYY)', 21781), 4326)); | + | INSERT INTO earthquakes (place, mag, the_geom) |
+ | VALUES ('SOMEWHERE, Switzerland', MMM, ST_Transform(ST_GeometryFromText('POINT(XXX YYY)', 21781), 4326)); | ||
</code> | </code> | ||
Line 29: | Line 30: | ||
* Exercice ST_Distance (1) : quelle distance sépare chaque séisme de Fukushima - POINT(141.0357 37.4217) ? | * Exercice ST_Distance (1) : quelle distance sépare chaque séisme de Fukushima - POINT(141.0357 37.4217) ? | ||
<code> | <code> | ||
- | ... | + | SELECT *, |
+ | ST_Distance( | ||
+ | the_geom::geography, | ||
+ | ST_GeometryFromText('POINT(141.0357 37.4217)',4326)::geography | ||
+ | ) / 1000 as d FROM earthquakes | ||
</code> | </code> | ||
- | * Adapter le style (plus on s'éloigne de Fukushima, plus la couleur du point représenté s'éclaircie) | ||
* Exercice ST_Distance (2) : quel est le séisme de magnitude > 6 le plus proche de Fukushima ? | * Exercice ST_Distance (2) : quel est le séisme de magnitude > 6 le plus proche de Fukushima ? | ||
+ | <code> | ||
+ | SELECT *, | ||
+ | ST_Distance( | ||
+ | the_geom::geography, | ||
+ | ST_GeometryFromText('POINT(141.0357 37.4217)',4326)::geography | ||
+ | ) / 1000 as d FROM earthquakes WHERE mag > 6 ORDER BY d LIMIT 1 | ||
+ | </code> | ||
+ | * Il serait souhaitable d'opter pour une autre approche que ce gourmand ORDER BY. Comment ? | ||
+ | <code> | ||
+ | ... | ||
+ | </code> | ||
===== Partie 2 ===== | ===== Partie 2 ===== | ||
Line 53: | Line 68: | ||
</code> | </code> | ||
* Comme on sait comment cette zone est configurée, on peut aussi utiliser simplement ST_Distance, voire ST_DWithin http://postgis.net/docs/manual-2.3/ST_DWithin.html | * Comme on sait comment cette zone est configurée, on peut aussi utiliser simplement ST_Distance, voire ST_DWithin http://postgis.net/docs/manual-2.3/ST_DWithin.html | ||
+ | <code> | ||
+ | ... | ||
+ | </code> | ||
+ | |||