Base de données des villes de france + formule de Haversine dans multiple langages

[Maz]

Bonjour,

Je poste quelque chose dont je ne suis pas l'auteur, mais que je trouves fort utile (peut-être pas dans un projet web, quoi que pour des stats géolocalisé  :heu: ) que je pense devrait avoir tout programmeur dans sa toolbox:

Avec la collaboration de Seishin, qui as déniché une belle base de données des villes de France très complète avec long/lat, département, code postal, etc: ici.

Et petit bonus avec la formule de Haversine qui permet de calculer sur un globe, avec la longitude/latitude une distance d'un point A à B, et ceux dans de multiples langages: SQL, Ruby, PHP, Python, Java, etc...: ici

[Xenos]

Salut,

Intéressant comme base de données

En revanche, pour faire mon ch*eur, je recommande plutôt d'utiliser le type GEOMETRY en SQL pour ce genre de calcul, avec le système de coordonnées 4326:

SELECT f.nm AS p1, t.nm AS p2, ST_DISTANCE(f.loc, t.loc)

FROM (

	SELECT ST_SRID(POINT(2.3488000, 48.8534100), 4326) AS loc, 'Paris' AS nm

	) AS f

INNER JOIN (

	SELECT ST_SRID(POINT(139.6917100, 35.6895000), 4326) AS loc, 'Tokyo' AS nm

	) AS t ON TRUE

;

Les positions des villes viennent de ces sources (attention, c'est logitude puis latitude dans l'ordre des paramètres de POINT):
https://dateandtime.info/fr/citycoordina...id=1850147
https://dateandtime.info/fr/citycoordina...id=2988507

Quelques infos supplémentaires sur ces fonctions de géométrie:
https://www.dataiku.com/learn/guide/othe...stGIS.html

Et la liste des systèmes de coordonnées, si jamais ça vous amuse:
https://spatialreference.org/ref/

La distance trouvée par Mysql:

Code :

9736091.5997 (mètres) = 9736 km

Google propose 9710km
https://www.google.com/search?source=hp&...aris+tokyo

Je pense qu'on peut considérer que c'est juste
Voilà

PS: Nécessite MySQL 8

[Maz]

@Xenos> merci pour l'info. Effectivement donc en SQL il est peut être préférable de choisir la simplicité (ce que je ferais puisque c'est ce que je cherchais lorsque je suis tombé dessus). Les calculs restent utiles pour les autres langages.

Pour ma part j'ai besoin de localiser les villes alentours donc la précision sera sûrement meilleur sur de courtes distances (bien que 26km sur 9000, c'est déjà une précision acceptable)

[Xenos]

En fait, j'aurai aimé avoir une vraie réponse plutôt que celle de Google. Rien ne dit que ce soit Google qui ait raison: et si Mysql prenait en compte le léger "écrasement" de la Terre? Je ne sais pas trop comment vérifier ça en fait...
Peut-être en testant quelques valeurs-clefs, comme la distance entre [0°,0°] et [90°,0°] ; [0°,90°] ; [180°,0°] etc

Après, c'est surtout pour la découverte de cette notion de "SRID" que la version Mysql "native" est intéressante: savoir qu'il existe un typage GEOMETRY, et qu'il est possible de faire des calculs dans des référentiels pas forcément plans et déjà codés par d'autres gens, dans un truc qu'on a déjà d'installé (sans nouvelle dépendance ou autre), je trouve ça chouette : )
Et je pense qu'on peut également calculer la surface d'un polygone dont les sommets sont des coordonnées GPS avec ce système (je ne pense pas que la méthode Haversine le permette)

[niahoo]

Tiens bah je peux étaler ma sience

Ton "4326" est le code d'identification d'un système de coordonnées qu'on appelle WGS84, qui non seulement prend en compte l'écrasement de la terre, mais bien plus que ça : https://fr.wikipedia.org/wiki/WGS_84 (edit: ça peut être confus : WGS84 est le nom d'un ensemble géoïde, ellipsoïde, et systèmes de coordonnées lat/lon. 4326 est le numéro du système de coordonnées).

Quand google maps n'avais pas encore de globe il était basé sur la projection "Web Mercator" connue aussi sous le nom de "Sperical Mercator", qui, comme son nom l'indique, utilise une sphère parfaite comme géoïde. Ça donne des résultats faux sur de longues distances mais ça simplifie grandement les calculs, logique donc pour un service comme google maps.

[Xenos]

Intéressant comme explication, j'aime bien ce genre d'étalage, je suis preneur

En revanche, je ne parvient pas à comprendre pourquoi la distance avec ce SRID 3857 renvoie... "137,97" ?!

SELECT f.nm AS p1, t.nm AS p2, ST_DISTANCE(f.loc, t.loc)

FROM (

	SELECT ST_SRID(POINT(2.3488000, 48.8534100), 3857) AS loc, 'Paris' AS nm

	) AS f

INNER JOIN (

	SELECT ST_SRID(POINT(139.6917100, 35.6895000), 3857) AS loc, 'Tokyo' AS nm

	) AS t ON TRUE

;

Or, d'après

SELECT DISTINCT *

       FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS;

	 WHERE SRS_ID = 3857

On a bien la ligne "WGS 84 / Pseudo-Mercator ; 3857 ; EPSG ; 3857 [je vous passe la définition]" ?!

Edit: Fun fact: on peut aussi créer ses propres projections... Pour peut qu'on sache en écrire la définition :/

Code :

PROJCS["WGS 84 / Pseudo-Mercator",GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]],PROJECTION["Popular Visualisation Pseudo Mercator",AUTHORITY["EPSG","1024"]],PARAMETER["Latitude of natural origin",0,AUTHORITY["EPSG","8801"]],PARAMETER["Longitude of natural origin",0,AUTHORITY["EPSG","8802"]],PARAMETER["False easting",0,AUTHORITY["EPSG","8806"]],PARAMETER["False northing",0,AUTHORITY["EPSG","8807"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",EAST],AXIS["Y",NORTH],AUTHORITY["EPSG","3857"]]

[niahoo]

L'unité de 3857 ce sont des mètres, pas des degrés !

Si tu veux travailler avec des latitudes/longitudes et Spherical Mercator il te faut utiliser 900913.

[Xenos]

Ah, en effet, bien vu! C'était même marqué dans la définition (que j'aurai dû prendre la peine de lire :/ )

Merci, très instructif tout ça : )

[niahoo]

Y a pas de quoi, si t'as des questions n'hésite pas