GIS and Media fusion
"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
User Tools
geoinf16:sqlcartodb
Differences
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 07:55] oertz |
geoinf16:sqlcartodb [2018/05/16 10:05] (current) |
||
|---|---|---|---|
| Line 7: | Line 7: | ||
| On utilise la géotable **earthquakes** (source : http://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_month.csv) | On utilise la géotable **earthquakes** (source : http://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_month.csv) | ||
| <code> | <code> | ||
| - | SELECT ST_AsText(the_geom), * FROM earthquake | + | SELECT ST_AsText(the_geom), * FROM earthquakes |
| </code> | </code> | ||
| - | * le frontale offre une preview cartographique et permet de créer une carte sur la base de ce jeu de géodonnées | + | * Le frontale offre une preview cartographique et permet de créer une carte sur la base de ce jeu de géodonnées |
| - | * créer votre séisme quelque-part en Suisse (!) | + | * Créer votre séisme quelque-part en Suisse (!) |
| <code> | <code> | ||
| SELECT ST_AsText(ST_Transform(ST_GeometryFromText('POINT(558000 137000)', 21781), 4326)) | SELECT ST_AsText(ST_Transform(ST_GeometryFromText('POINT(558000 137000)', 21781), 4326)) | ||
| </code> | </code> | ||
| - | * à 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 27: | Line 28: | ||
| ) | ) | ||
| </code> | </code> | ||
| - | - 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> | ||
| SELECT *, | SELECT *, | ||
| ST_Distance( | ST_Distance( | ||
| the_geom::geography, | the_geom::geography, | ||
| ST_GeometryFromText('POINT(141.0357 37.4217)',4326)::geography | ST_GeometryFromText('POINT(141.0357 37.4217)',4326)::geography | ||
| - | ) / 1000 as d FROM all_month | + | ) / 1000 as d FROM earthquakes |
| - | - adapter le style | + | </code> |
| - | - Exercice ST_Distance (2) : nearest of Fukishima of mag > 6 | + | * Exercice ST_Distance (2) : quel est le séisme de magnitude > 6 le plus proche de Fukushima ? |
| + | <code> | ||
| SELECT *, | SELECT *, | ||
| ST_Distance( | ST_Distance( | ||
| the_geom::geography, | the_geom::geography, | ||
| ST_GeometryFromText('POINT(141.0357 37.4217)',4326)::geography | ST_GeometryFromText('POINT(141.0357 37.4217)',4326)::geography | ||
| - | ) / 1000 as d FROM all_month WHERE mag > 6 ORDER BY d LIMIT 1 | + | ) / 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 ===== | ||
| + | On utilise les deux géotables **highway61** et **musicians** (sources : https://carto.com/academy/d/highway_61.geojson, https://carto.com/academy/d/mississippi_blues_musicians.geojson). | ||
| + | |||
| + | * ST_Buffer et the_geom_webmercator : http://postgis.net/docs/manual-2.3/ST_Buffer.html | ||
| + | <code> | ||
| + | SELECT ST_Buffer(the_geom_webmercator, 50000) as the_geom_webmercator FROM highway61 | ||
| + | </code> | ||
| + | * Exemple ST_Intersects : http://postgis.net/docs/manual-2.3/ST_Intersects.html | ||
| + | <code> | ||
| + | SELECT ST_Intersects( | ||
| + | ST_GeomFromText('LINESTRING(-2 -2, 2 2)', 4326), | ||
| + | ST_GeomFromText('POINT(0 0)', 4326) | ||
| + | ) | ||
| + | </code> | ||
| + | * Exercice ST_intersects : quels sont les musiciens qui sont nés dans cette zone ? | ||
| + | <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 | ||
| + | <code> | ||
| + | ... | ||
| + | </code> | ||
| + | |||
geoinf16/sqlcartodb.1480056929.txt.gz · Last modified: 2018/05/16 10:05 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International
