SQL Spatial with Carto.com & PostGIS

Carto.com offre un frontale très convivial pour piloter une base de données spatiale utilisant PostGIS (http://postgis.net, https://carto.com/docs/faqs/postgresql-and-postgis). Rapide découverte en deux parties.

Avant de commencer, il faut se connecter sur http://carto.com avec le utilisateur geoinf16.

On utilise la géotable earthquakes (source : http://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_month.csv)

SELECT ST_AsText(the_geom), * FROM earthquakes

• 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 (!)

SELECT ST_AsText(ST_Transform(ST_GeometryFromText('POINT(558000 137000)', 21781), 4326))

• A vous de jouer en donnant de bonnes infos

INSERT INTO earthquakes (place, mag, the_geom) 
VALUES ('SOMEWHERE, Switzerland', MMM, ST_Transform(ST_GeometryFromText('POINT(XXX YYY)', 21781), 4326));

• Exemple ST_Distance et le type geography : http://postgis.net/docs/manual-2.3/ST_Distance.html

SELECT ST_Distance(
 ST_GeomFromText('POINT(-72.1235 42.3521)',4326),
 ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.123 42.1546)', 4326)
)

• Exercice ST_Distance (1) : quelle distance sépare chaque séisme de Fukushima - POINT(141.0357 37.4217) ?

SELECT *, 
ST_Distance(
  the_geom::geography,
  ST_GeometryFromText('POINT(141.0357 37.4217)',4326)::geography
) / 1000 as d FROM earthquakes

• Exercice ST_Distance (2) : quel est le séisme de magnitude > 6 le plus proche de Fukushima ?

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

• Il serait souhaitable d'opter pour une autre approche que ce gourmand ORDER BY. Comment ?

...

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

SELECT ST_Buffer(the_geom_webmercator, 50000) as the_geom_webmercator FROM highway61

• Exemple ST_Intersects : http://postgis.net/docs/manual-2.3/ST_Intersects.html

SELECT ST_Intersects(
 ST_GeomFromText('LINESTRING(-2 -2, 2 2)', 4326),
 ST_GeomFromText('POINT(0 0)', 4326)
)

• Exercice ST_intersects : quels sont les musiciens qui sont nés dans cette zone ?

...

• 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

...