Postgresql, Postgis et calculs

[Tioneb]

Hello,

Je débute dans la programmation ruby on rails et tente de faire un site web autour de circuits de randonnée.

Par défaut, j'utilise postgresql mais me pose la question de passer éventuellement sur postgis mais je ne sais pas vraiment si c'est nécessaire ou présente un réel avantage. A priori je n'ai pas un grand besoin de données spatiales.

En fait, j'ai une table avec la trace gps (long, lat, alt et point kilométrique(PK) ). Dans le cadre de mon projet j'ai besoin de faire les calculs suivants :

- Calcul distance entre deux PK (pour ça ce n'est pas un problème)
- Calcul du cumul du dénivellé positif et négatif entre deux PK
- Calcul de la difficulté du parcours avec un code couleur (vert, orange, rouge) suivant le dénivellé (en fonction du % de pente)
- Récupérer la distance entre un point donné et la trace gps. Par exemple un centre d'intérêt situé à x mètres du point le plus près de la trace (calcul de cette distance)

Est ce que je peux arriver à faire ces calculs avec postgresql ou est ce que le passage en postgis me faciliterait le travail (tout en le complexifiant coté programmation).

Merci d'avance pour vos retours

[yvecai]

Postgis est une extension à Postgresql qui te simplifiera énormément le travail concernant les calculs que tu évoque.

[cquest]

postgis va te simplifier énormément le travail... et la programmation !

Beaucoup de calculs vont pouvoir être faits par postgres+postgis et limiter le code supplémentaire nécessaire.

Pour les dénivellés, tu peux par exemple prendre une trace 2D, la croiser avec un modèle numérique de terrain et obtenir la même trace en 3D avec le relief.

[Tioneb]

Merci pour vos retours... même si, comme vous, je pense que le passage en Postgis serait un vrai plus dans l'acquisition de nouvelles compétences, la marche à franchir me parait aussi élevé... d'autant que RoR ne me parait pas le langage de prédilection.
Mais avec vos encouragements, vais tenter de passer le cap... mon problème, c'est que je ne sais pas vraiment par où commencer. J'ai regardé en détail les différentes gems qui existent pour faciliter mon approche et j'avoue que je suis un peu largué.
Pour y aller de façon progressive, je me concentre sur 2 fonctionnalités essentielles :
- transformer ma trace gps et ses variantes en point géospacialisé (sais pas si le mot existe)
- récupérer à partir de données gps, la position et la distance vis à vis de cette trace
J'ai parcouru la doc, et suis un peu perdu. Entre la config Postgis avec RoR et les fonctions à implémenter, j'ai pas vraiment trouvé de tutos qui m'aident vraiment à comprendre par quel bout commencer....
Donc si vous pouvez m'orienter....je serais ravi de découvrir les bonnes sources à explorer.

[yvecai]

Finalement, le coeur de ce que tu cherche à faire, c'est avec Postgis, RoR te permettra de faire l'application autour.
Alors pourquoi pas experimenter Postgis à la main pour te lancer ?
Importer un gpx dans une base postgis
Trouver le point du gpx le plus proche d'un point
...
Par exemple :
https://gis.stackexchange.com/questions ... tgis-table

[cquest]

postgis rajoute en gros 2 choses:
- des types de données géographiques (geometry)
- des fonctions pour manipuler ces données

Par rapport à l'usage classique d'une base relationelle où l'on fait le lien entre différentes données essentiellement à partir de valeurs identiques (un identifiant commun dans deux tables différentes) pour les données géographiques, on révèle le plus souvent le lien entre des données à l'aide de fonctions.

Exemple, je cherche les points à proximité d'une coordonnées, on utilisera un WHERE avec ST_dwithin

http://postgis.net/docs/ST_DWithin.html

Pour résumer: liens explicites dans les données relationelles, liens implicites dans les données géo qu'on explicite via des fonctions de calcul géo.

[Tioneb]

Ah super, merci pour ces infos... je bouffe de la doc depuis quelques jours et les choses me semblent plus claires.

Si j'ai bien compris, on stocke en bdd de données des données de type géographiques et postgis permet d'accéder à des fonctions magiques qui permettent de faire des calculs plus simplement.

Donc je n'ai plus besoin de passer mes données dans des moulinettes en ligne, mais je peux faire ces calculs directement sur la bdd comme par exemple :

On choisit d'abord un système geom le 4326 semble le plus pertinent ?
Ensuite pour :
- Calcul longueur d'une trace => ST_Distance
- Calcul distance d'un point vers le point le plus près de la trace => ST_DWithin
- Calcul dénivelé ?

Du coup j'essaie de modéliser ma bdd et c'est pas si évident que ça. Si je comprends bien mes coordonnées géographique peuvent prendre dans mon cas plusieurs valeurs :

Point : pour un POI
LineString ou MultiLineString : pour mes traces qui ne sont pas des boucles
Polygon : pour les circuits en boucles

Je ne sais pas si je suis dans le vrai

En tous les cas merci pour tes éclairages

[Tioneb]

Finalement, le coeur de ce que tu cherche à faire, c'est avec Postgis, RoR te permettra de faire l'application autour.
Alors pourquoi pas experimenter Postgis à la main pour te lancer ?
Importer un gpx dans une base postgis
Trouver le point du gpx le plus proche d'un point
...
Par exemple :
https://gis.stackexchange.com/questions ... tgis-table

Oui, c'est ce que je suis en train de constater...
Mais du coup, je le fais aussi directement dans rails, ça me permet de travailler aussi sur mes modeles, mes controlleurs... etc

Si j'ai bien compris, il y a quelques fondamentaux (projections, fonctions...) à connaitre (et dans mon cas elles semblent assez limitées), mais peut être qu'en découvrant la puissance de l'outil je vais me trouver quelques nouvelles futures

[cquest]

Dans les bases il y a les projections, j'ai volontaire sauté ça dans ma reponse précédente

Le problème: la terre n'est pas plate... (une aspirine est souhaitable pour la suite)

Pour exprimer des coordonnées sur toute la surface du globe on utilise des latitudes au dessus/dessous de l'équateur et longitudes de part et d'autre du méridien de greenwich. C'est le standard, le "WGS84" utilisé par le système GPS (et je simplifie)... ou aussi EPSG:4326, le 4326 dont tu parlais.

Calculer des distances se fait par des calculs trigonométriques, donc un peu coûteux en CPU

Dans le monde de la carte papier, on est à plat, donc on projete la géométrie "sphérique" sur un plan, un cone ou un cylindre et on a des coordonnées X/Y. Du coup calculer des distances est plus simple... sqr(dX^2+dY^2). Mais ça déforme forcément sur les bords... donc c'est acceptable sur de petites zones en général pas plus de 1000km de côté.

En métropole, la projection standard (et légale) c'est le Lambert93 (EPSG:2154), qui se fait sur un cône.
Sur les DOM, on utilise pour chaque département une projection locale.

L'autre projection à connaitre... le "web mercator" (3857), qui est projeté sur un cylindre vertical. C'est ce qu'on utilise pour les cartes mondiales sur le web... ça déforme de plus en plus en allant vers les pôles... du coup on d'arrête en général avant et on ne va pas jusqu'au poles pour cette raison.

Voilà pour la culture générale... je ne rentre pas plus dans les détails... car la terre n'est pas sphérique et les continents se déplacent


Bon, ok, mais dans la BDD, qu'est-ce qu'on fait ?

Si on a des données sur une zone limitée (ex que la métropole), on peut les stocker dans cette projection.
Avantage 1: on peut directement utiliser ces coordonnées pour dessiner la carte à plat
Avantage 2: on peut faire des calculs locaux acceptables et rapides (on a quand même des "km" qui font 1003m en Corse)
Inconvénient... zone limitée

On peut stocker les données en web mercator... parfait si elles servent essentiellement à produire une carte web mondiale. C'est en général le cas des bases pour générer des fonds de carte OSM.
Inconvénient... calcul de distance et angles impossibles... il faut reprojeter les données ce qui est coûteux.

Sinon... stockage en lat/lon WGS4 (4326), ce qui me permet d'ouvrir le chapitre suivant... dans postgis on a des "geometry" et des "geography"

On peut stocker les données en geometry 4326, et quand on a besoin de faire un calcul les projeter localement et les passant en "geography"
pt1 et pt2 sont en geometry en 4326

ST_Distance(pt1, pt2) -> calcule une "distance" en degrés, autant dire un truc sans intérêt
ST_Distance(pt1::geography, pt2::geography) -> transforme nos points en projection locale et le résultat est une distance métrique


A l'affichage... une librairie comme Leaflet (ou openlayers) pourra très ben afficher un fond de carte en web mercator et par dessus des données vectorielles qui sont en WGS84. Elle va heureusement s'occuper de la projection 4326 > 3857

[Tioneb]

Dans les bases il y a les projections, j'ai volontaire sauté ça dans ma reponse précédente

Le problème: la terre n'est pas plate... (une aspirine est souhaitable pour la suite)

Pour exprimer des coordonnées sur toute la surface du globe on utilise des latitudes au dessus/dessous de l'équateur et longitudes de part et d'autre du méridien de greenwich. C'est le standard, le "WGS84" utilisé par le système GPS (et je simplifie)... ou aussi EPSG:4326, le 4326 dont tu parlais.

Calculer des distances se fait par des calculs trigonométriques, donc un peu coûteux en CPU

Dans le monde de la carte papier, on est à plat, donc on projete la géométrie "sphérique" sur un plan, un cone ou un cylindre et on a des coordonnées X/Y. Du coup calculer des distances est plus simple... sqr(dX^2+dY^2). Mais ça déforme forcément sur les bords... donc c'est acceptable sur de petites zones en général pas plus de 1000km de côté.

En métropole, la projection standard (et légale) c'est le Lambert93 (EPSG:2154), qui se fait sur un cône.

J'ai pas le projet de travailler pour l'instant, sur des zones de plus 1000 km de coté.
Quand tu dis que la projection standard c'est le 2154, est ce à dire que je devrais privilégier cette projection vs la 4326 ?

Pour ma culture, quelles sont les conséquences en terme de calcul... ? Par exemple sur un carré de 2000 km, quelles sont le niveau des dérives (ça se compte en quelques centaines de mètres, ou en plusieurs kilomètres ?)

Sur les DOM, on utilise pour chaque département une projection locale.

L'autre projection à connaitre... le "web mercator" (3857), qui est projeté sur un cylindre vertical. C'est ce qu'on utilise pour les cartes mondiales sur le web... ça déforme de plus en plus en allant vers les pôles... du coup on d'arrête en général avant et on ne va pas jusqu'au poles pour cette raison.

Voilà pour la culture générale... je ne rentre pas plus dans les détails... car la terre n'est pas sphérique et les continents se déplacent

Ok même si je ne comprends encore les nuances, je vois bien l'état d'esprit.

Bon, ok, mais dans la BDD, qu'est-ce qu'on fait ?

C'est là la vrai question

Si on a des données sur une zone limitée (ex que la métropole), on peut les stocker dans cette projection.
Avantage 1: on peut directement utiliser ces coordonnées pour dessiner la carte à plat
Avantage 2: on peut faire des calculs locaux acceptables et rapides (on a quand même des "km" qui font 1003m en Corse)
Inconvénient... zone limitée

On peut stocker les données en web mercator... parfait si elles servent essentiellement à produire une carte web mondiale. C'est en général le cas des bases pour générer des fonds de carte OSM.
Inconvénient... calcul de distance et angles impossibles... il faut reprojeter les données ce qui est coûteux.

Pour l'instant, je suis plutôt à un niveau local... mais pourquoi pas passer à un niveau plus large, tout ça me donne des idées

Sinon... stockage en lat/lon WGS4 (4326), ce qui me permet d'ouvrir le chapitre suivant... dans postgis on a des "geometry" et des "geography"

On peut stocker les données en geometry 4326, et quand on a besoin de faire un calcul les projeter localement et les passant en "geography"
pt1 et pt2 sont en geometry en 4326

Ok c'est clair, mais pourquoi alors ne pas les passer directement en geography ?

ST_Distance(pt1, pt2) -> calcule une "distance" en degrés, autant dire un truc sans intérêt
ST_Distance(pt1::geography, pt2::geography) -> transforme nos points en projection locale et le résultat est une distance métrique

A l'affichage... une librairie comme Leaflet (ou openlayers) pourra très ben afficher un fond de carte en web mercator et par dessus des données vectorielles qui sont en WGS84. Elle va heureusement s'occuper de la projection 4326 > 3857

Pour l'instant, je n'en suis pas encore à l'affichage dans une libraire quelconque (peut être que je devrais me poser la question au préalable ?), je n'ai même pas encore abordé la question.

Pour résumer et si je comprends bien :

A un niveau local (France) une projection 3857 ou 2154 sont pernitentes
A un niveau plus large (Europe) il vaut mieux privilégier la 4326

Quand au stockage des données, il vaut mieux privilégier le geography...

J'ai tout faux ou j'ai tout compris ?

En tous les cas merci bcp pour ces éclairages lumineux

[cquest]

Local France: 2154 (Lamber93)... mais ça te limitera à la métropole et le jour où tu veux t'édendre tu dois tout repenser, modifier...

3857 (Web Mercator), c'est pour une pur rendu carto mondial, je ne vois pas l'intérêt dans ton cas.

4326 (WGS84): sûrement le plus simple vu les données que tu va manipuler qui vont arriver en 4326 et que tu enverra sûrement en geojson (donc 4326) au navigateur pour affichage par LEaflet ou autre.


Pourquoi ne pas stocker en "geography" ? Parce que postgis ne propose pas autant de fonctions pour travailler sur les geography que sur les geometry.

Tu peux aussi très bien avoir plusieurs colonnes "geo" dans tes tables, et stocker dans plusieurs projections en même temps, ce qui fait une transformation unique à la création et ensuite bien moins de calculs... juste un peu plus de place en stockage. Si tu as un volume raisonnable, c'est une bonne solution à envisager.

[Tioneb]

Merci pour ces infos, finalement je suis parti sur une solution différente. Les circuits secondaires, sont tous rattachés à un circuit... cela me permet de les traiter tous de façon indépendantes (descriptif, trace...etc), j'évite la table de jointure et je devrais pouvoir optimiser mes requêtes.

Quand on fait un calcul de distance, postgis parcours l'ensemble des points ? Est ce qu'il y a une limite au nombres de points pouvant être stockés dans une LINESTRING ? Parceque par exemple, j'ai des circuits de 28 kms plus une variante de 12 km (et un relevé csv environ tous les 10 mètres) soit environ 4000 à 5000 points... je crains que le geoJSON d'export soit carrément lourd.
J'ai vu qu'on pouvait optimiser un peu en réduisant le nombre de chiffres après la virgule dans les coordonnées, mais que coup on perdait en précision... à combien de chiffre on peut descendre tout en restant assez précis

[yvecai]

Pour un calcul de distance, cela devrait peut-être aller, mais pour exporter ou représenter graphiquement, il y a des fonctions comme st_simplify() qui peuvent t'aider.

[cquest]

ST_Simplify permet de supprimer des points de détails, qui ne changent pas beaucoup la forme globale... cas typique des points en ligne droite.

5 décimales en wgs84, ça fait 1m... ce qui peut être largement suffisant vu la précision d'un GPS.

Regarde aussi ST_SnapToGrid, qui recale les points par exemple sur la 5ème décimale, vire ceux redondants, à combiner avec un ST_Simplify... mais bon, là tu sera à la phase optimisation