Quelle fonction postgis ?

Extraire des données OSM, créer sa carte, uMap, utiliser sur un GPS ou un smartphone...
Répondre
Tioneb
Messages : 32
Inscription : mar. mars 27, 2018 4:03 pm

Quelle fonction postgis ?

Message par Tioneb » mer. juin 06, 2018 1:01 am

J'ai épluché la doc à la recherche de la fonction idéale... mais je n'ai rien trouvé. Avant de mettre les mains dans la cambouis... je réfère poser la question :

J'ai une linestring, avec un poi A... j'aimerais pouvoir ressortir tous les Pois situés par exemple entre 5 et 10 km en aval de ce point (de part et d'autre de la linestring).

En pseudo code, je pense que ça donnerait un truc dans le genre :

- trouver le point de la linestring le plus proche (ST_LineLocatePoint)
- trouver les points sur la linestring situé à 5 et 10 km du point A (c'est cette partie qui me pose souci)
- faire un substring de la linestring
- rechercher les pois autour de cet extrait de linestring suivant un radius donné

En fait je n'ai pas trouvé de fonction "inverse" à St_distance qui mesure entre deux points... mais la fonction qui retourne le point situé à x km du point A.

9a existe ? Ou sinon quelle est la meilleure façon de procéder ?

Avatar de l’utilisateur
cquest
Messages : 1823
Inscription : ven. avr. 16, 2010 12:22 am
Localisation : Val de Marne
Contact :

Re: Quelle fonction postgis ?

Message par cquest » ven. juin 08, 2018 10:48 am

On va faire le truc à l'envers...

Trouver ce qui est à 5 ou 10km d'un bout de linestring: ST_DWithin
la portion aval de linestring, consiste à couper la linestring en deux: ST_Split
la couper avec quoi... avec un point sur la linestring le plus proche du point A: ST_ClosestPoint

Donc en gros...

ST_Split(linestring, ST_Closestpoint(linestring, pointA)) va retourner 2 morceaux de linestring

Il faut que tu sélectionne celui en aval... par exemple en prenant la partie avec son centroid le plus éloigné du point de départ.

Puis tu cherche avec ST_DWithin(linestring_aval::geography, lespoints::geography, distance)

Après se posera peut-être la question de l'optimisation de cette dernière requête ;)

Tioneb
Messages : 32
Inscription : mar. mars 27, 2018 4:03 pm

Re: Quelle fonction postgis ?

Message par Tioneb » dim. juin 10, 2018 12:22 am

Merci pour ce retour, mais j'avoue que je ne comprends pas la logique.
Peut être que ma question était mal formulée, aussi, j'ai fait un petit schéma.

Image

Pour faire simple, je suis au point A..., j'ai envie de marcher entrer 15 km (point B) et 20 km (point C) et j'aimerais bien connaitre les pois situés dans cet espace (3,4,5,6).

Si je suis ta logique, je splite la linestring (Y-Z) au point a'. J'obtiens 2 segments (a'-Y et a'-Z).
Je sélectionne le segment avec le centroid le plus éloigné de A... c'est donc a'-Y.
Sauf que ma recherche doit porter sur a'-Z....

J'ai loupé quelque chose ??

Avatar de l’utilisateur
cquest
Messages : 1823
Inscription : ven. avr. 16, 2010 12:22 am
Localisation : Val de Marne
Contact :

Re: Quelle fonction postgis ?

Message par cquest » lun. juin 11, 2018 5:57 pm

Je pensais que A était entre B et C... car tu étais déjà sur l'itinéraire.

Tioneb
Messages : 32
Inscription : mar. mars 27, 2018 4:03 pm

Re: Quelle fonction postgis ?

Message par Tioneb » mar. juin 12, 2018 1:07 am

Donc ce n'est pas possible ? :(

Avatar de l’utilisateur
cquest
Messages : 1823
Inscription : ven. avr. 16, 2010 12:22 am
Localisation : Val de Marne
Contact :

Re: Quelle fonction postgis ?

Message par cquest » mar. juin 12, 2018 8:32 am

Si bien sûr !

ST_DWithin(ST_LineSubstring(ST_LineLocatePoint(linestring, pointB), ST_LineLocatePoint(linestring, pointC))::geography, point::geography, distance)

ST_LineLocatePoint permet de savoir où se trouve les points B et C sur le chemin (de 0 à 1)
ST_LineSubstring récupère que la portion de chemin entre B et C
ST_DWithin sélectionne les points à moins d'une certaine distance de la portion de chemin

Tioneb
Messages : 32
Inscription : mar. mars 27, 2018 4:03 pm

Re: Quelle fonction postgis ?

Message par Tioneb » mar. juin 12, 2018 2:29 pm

Merci ;) Ca ressemble de près à mon pseudo code et ce que je voulais faire... mais j'ai toujours le même souci : je ne connais pas à priori la valeur de B qui peut être à 10, 15 ou x kilomètres du point de départ.
C'est justement ce point qui me pose souci, comment trouver la valeur de B (où comment trouver le point de la linestring qui est à x km de A)

:(

Avatar de l’utilisateur
cquest
Messages : 1823
Inscription : ven. avr. 16, 2010 12:22 am
Localisation : Val de Marne
Contact :

Re: Quelle fonction postgis ?

Message par cquest » mar. juin 12, 2018 7:09 pm

Tu veux calculer B, à X m de A en direction de C ?

1) on calcule la position relative de a' sur le linestring avec un : ST_LineLocatePoint(linetring, A) -> pA
2) on calcule la position relative de C sur le linestring : ST_LineLocatePoint(linetring, C) -> pC

Le problème c'est le sens de la linestring... où se trouve le début et la fin... A est avant C ou après ?
On a besoin de la savoir pour ajouter ou retirer la distance entre A et B.

L'astuce: (pC-pA)/abs(pC-pA) sera égal à 1 si C est après A et -1 si C est avant A...

3) B se trouve donc ici : ST_InterpolatePoint(linestring, pA + X / ST_Length(linestring::geography) * (pC-pA)/abs(pC-pA))


C'est pas testé... mais ça doit être quelque chose comme ça ;)

Tioneb
Messages : 32
Inscription : mar. mars 27, 2018 4:03 pm

Re: Quelle fonction postgis ?

Message par Tioneb » mar. juin 12, 2018 11:45 pm

Bon, je me prend un doliprane, et je réfléchi à ta suggestion.... :D

En fait B et C sont des variables à partir de A (a'). Par exemple B = 10 km et C = 15 km. Je ne les connais donc pas par avance. La distance de ces deux points est déterminée en fonction de A(a').

Pour faire plus simple :

Comment je peux trouver le point à X kms à partir d'un point de cette même linestring ? C'est le point qui me pose souci :D

Si je suis à a' (sur la linestring)... comment, je peux trouver le point B qui est à X km sur la même linestring ( x pouvant être égal à 5, 10 ou 15 km... ou une autre variable).

Ou répondre à la simple question, quel est le point B situé à x km du point a'... en allant vers C.

Je pense qu'avec ça je devrais me débrouiller ;)

Pour remettre dans le contexte. Imagine, je suis dans un ville, j'ai envie de marcher 15 km et j'aimerais savoir où je peux manger et/ou dormir à cette distance donnée...

En tous les cas, un grand merci de prendre le temps de m'aider à progresser dans la maitrise des subtilités de postgis ;)

Avatar de l’utilisateur
cquest
Messages : 1823
Inscription : ven. avr. 16, 2010 12:22 am
Localisation : Val de Marne
Contact :

Re: Quelle fonction postgis ?

Message par cquest » mer. juin 13, 2018 8:36 am

Quelle différence entre:
"quel est le point B situé à x km du point a'... en allant vers C."
et
"Tu veux calculer B, à X m de A en direction de C ?

C'est bien LineInterpolatePoint qui sert à ça, il fait juste le calcul en relatif (0 à 1) sur la longueur totale du linestring (de 0 à 1) alors que tu raisonne en km, donc le ST_Length(linestring::geography) permet de passer de l'un à l'autre avec une simple règle de 3.

L'autre point c'est le sens du linestring (il va de Y à Z ou de Z à Y ?) et de quel côté on veut partir (vers Y ou Z ?).

Dans ton schéma, A est plus proche de Z que de Y... si je ne sais pas où sont B et C, il y a donc 2 zones possibles à X km du départ... donc potentiellement un B' et C' du côté de Y

Il faut donc au moins savoir si on va vers Y ou Z sur le linestring, dans ce cas dans les fonctions précédents tu peux remplacer C qui ne servait qu'à déterminer de quel côté on partait par Y ou Z et ça sera ok.

Tioneb
Messages : 32
Inscription : mar. mars 27, 2018 4:03 pm

Re: Quelle fonction postgis ?

Message par Tioneb » mer. juin 13, 2018 5:14 pm

Hello,

Expliqué comme ça ma semblé plus clair... je comprends vite mais faut m'expliquer longtemps. ;)
J'ai donc bossé en suivant tes préconisations en simplifiant un peu.
Plus de point C pour l'instant, et le ST_EndPoint pour le sens.

Ca été un peu laborieux, mais 'ai procédé step by step :

["SELECT
ST_LineLocatePoint(line,pta) AS dst_line,
ST_EndPoint(line) AS end_point,
ST_LineLocatePoint(line,ptb) AS locate_end_point,
ST_Length(line::geography) AS length_line,
CAST (ST_LineLocatePoint(line,pta) + 10000 / ST_Length(line::geography) AS FLOAT8) AS position_b,
ST_Line_Interpolate_Point(line,(CAST (ST_LineLocatePoint(line,pta) + 10000 / ST_Length(line::geography) AS FLOAT8))) as point_b,
ST_distance(ST_Line_Interpolate_Point(line,(CAST (ST_LineLocatePoint(line,pta) + 10000 / ST_Length(line::geography) AS FLOAT8))),pta::geography) As x_to_a
FROM (
SELECT
'SRID=4326;LINESTRING(1.391991 46.441430,1.3926994 46.442145,1.407279 46.456903,1.506078 46.556788)'::geometry line,
'SRID=4326;POINT(1.393783 46.44204)'::geometry pta,
'SRID=4326;POINT(1.506078 46.556788)'::geometry ptb
) data"
]

Et voilà le résultat :

A = 0.0104370666013725
Z : POINT (1.506078 46.556788)
Locate Z : 1.0
Longueur de line : 15528.5444140294
Position B : 0.654412427934365
Position B : POINT (1.466648020242273 46.51692460534924)
A to B : 10030.02053095

Tout me semble cohérent. Maintenant, que j'ai localisé B, vais m'attaquer à la suite ;)

Un grand merci

Tioneb
Messages : 32
Inscription : mar. mars 27, 2018 4:03 pm

Re: Quelle fonction postgis ?

Message par Tioneb » mer. juin 13, 2018 6:01 pm

J'ai un petit souci avec la suite :

["SELECT
ST_LineSubstring(
line,
ST_Line_Interpolate_Point(line,(CAST (ST_LineLocatePoint(line,pta) + 5000 / ST_Length(line::geography) AS FLOAT8))),
ST_Line_Interpolate_Point(line,(CAST (ST_LineLocatePoint(line,pta) + 10000 / ST_Length(line::geography) AS FLOAT8)))
) as line_substring
FROM (
SELECT
'SRID=4326;LINESTRING(1.391991 46.441430,1.3926994 46.442145,1.407279 46.456903,1.506078 46.556788)'::geometry line,
'SRID=4326;POINT(1.393783 46.44204)'::geometry pta,
'SRID=4326;POINT(1.506078 46.556788)'::geometry ptb
) data"
]

Me retourne une erreur :
You might need to add explicit type casts.

Linesubstring attend une linestring suivi de 2 float, j'ai donc tenté de caster mes deux Line_interpolate, mais là j'ai cette erreur :

PG::CannotCoerce: ERROR: cannot cast type geometry to double precision.
Comment passer de geometry a float8 ?

Avatar de l’utilisateur
cquest
Messages : 1823
Inscription : ven. avr. 16, 2010 12:22 am
Localisation : Val de Marne
Contact :

Re: Quelle fonction postgis ?

Message par cquest » mer. juin 13, 2018 7:32 pm

ST_LineSubstring attends:
- un linestring
- deux valeurs entre 0 et 1 qui correspondent à l'interpolation... et pas deux POINT

Donc...

ST_LineSubstring(
line,
ST_LineLocatePoint(line,pta)+ 5000 / ST_Length(line::geography),
ST_LineLocatePoint(line,pta) + 10000 / ST_Length(line::geography)
)

Tioneb
Messages : 32
Inscription : mar. mars 27, 2018 4:03 pm

Re: Quelle fonction postgis ?

Message par Tioneb » jeu. août 23, 2018 1:30 am

Après avoir laissé en stand by cette fonction, je reviens dessus...

Code : Tout sélectionner

  def poi_around_track_from(poi)
    around_sql = <<-SQL
    SELECT
    ST_DWithin(
      ST_LineSubstring(
      way.path,
      ST_LineLocatePoint(way.path, pta.lonlat::geometry) + 5000 / ST_Length(way.path::geography)::geography,
      ST_LineLocatePoint(way.path, pta.lonlat::geometry) + 10000 / ST_Length(way.path::geography)::geography
      ),
      2000) as pois
    FROM ways way, pois pta
    WHERE (way.id = #{self.id} and pta.id = #{poi.id}) IS TRUE
    SQL
    Poi.find_by_sql(around_sql).pois
  end
J'obtiens une erreur :
PG::CannotCoerce: ERROR: cannot cast type double precision to geography
LINE 5: ...:geometry) + 5000 / ST_Length(way.path::geography)::geograph...

ST_DWithin attend à priori des valeurs geography pour un retour en mètres
ST_LineSubstring attend des valeurs de type geography pour un retour en mètres
mais ST_LineLocatePoint attend lui du geometry

Quel est la bonne méthode pour hiérarchiser la requête entre les données geography et geometry ?

Avatar de l’utilisateur
cquest
Messages : 1823
Inscription : ven. avr. 16, 2010 12:22 am
Localisation : Val de Marne
Contact :

Re: Quelle fonction postgis ?

Message par cquest » jeu. août 23, 2018 10:17 am

Le problème vient du ST_Length(way.path::geography)::geography

ST_Length retourne un double... inutile de le repasser en geography ;)

ST_Length(way.path::geography)::geography -> ST_Length(way.path::geography)

Pas impossible qu'il y ait d'autres trucs qui ne collent pas...

Avatar de l’utilisateur
Renephilippe
Messages : 29
Inscription : lun. juin 18, 2012 12:45 pm

Re: Quelle fonction postgis ?

Message par Renephilippe » ven. août 24, 2018 6:01 pm

Je n'y comprends pas grand chose bien sûr, mais qu'est-ce que c'est beau les maths (ou géométrie); il y en a qui ont de la chance d'avoir pu étudier tout cela :roll:
Facta non jam facienda (ce qui est fait n'est plus à faire)

Tioneb
Messages : 32
Inscription : mar. mars 27, 2018 4:03 pm

Re: Quelle fonction postgis ?

Message par Tioneb » mer. août 29, 2018 12:28 pm

Ce n'est pas qu'une question de math ou de geometrie, c'est surtout de la logique et de comprendre comment tout cela fonctionne. Rassure toi, j'ai jamais été très fort dans ces domaines, c'est aussi pour ça que c'est parfois compliqué, mais avec un peu d'acharnement et de volonté, et l'aide personnes disponibles comme Quest, pas à pas, on arrive à comprendre comment dérouler le fil et obtenir un résultat.

Le souci, c'est que parfois le résultat n'est pas forcément celui attendu. C'est pour ça aussi que je me casse un peu la tête avec cette fonction, qui pour bien des spécialistes du sujets doit être un jeu d'enfant. :(

Code : Tout sélectionner

def poi_around_track_from(poi, dist)
    around_sql = <<-SQL
    SELECT
    ptb.*,
    ST_DWithin(
      ST_LineSubstring(
      way.path,
      ST_LineLocatePoint(way.path, pta.lonlat::geometry) + #{dist} / ST_Length(way.path::geography),
      ST_LineLocatePoint(way.path, pta.lonlat::geometry) + 20000 / ST_Length(way.path::geography)
      ),
      ptb.lonlat,
      2000) is true as pois
    FROM ways way, pois pta, pois ptb
    WHERE way.id = #{self.id}
      and pta.id = #{poi.id}
    SQL
    Poi.find_by_sql(around_sql)
  end
Il y quelque chose qui cloche, mais je n'arrive pas trouver quoi.

Je sélectionne tous les pois dans la base

- Avec les deux ST_LineLocatePoint je défini les points d'entrée et de sortie
- Avec ST_LineSubstring je récupère la nouvelle linestring
- Avec ST_DWithin je fais une requête pour savoir quels sont les pois qui sont (à 2000 m) autour de cette ligne

et au final, je souhaite récupérer tous ceux qui sont true.

Si je teste cette function, elle me retourne tous les pois, il y a donc un truc qui cloche dans la requête. Si je pouvais savoir où et pourquoi, je pourrais peut etre avancer ;)

Tu sais Renephilippe , faut pas se décourager devant des choses qui paraissent bien obscures au départ, avec juste un peu de bonne volonté, on arrive tout doucement à dérouler... sans pour autant devenir un expert :mrgreen: :mrgreen:

Avatar de l’utilisateur
cquest
Messages : 1823
Inscription : ven. avr. 16, 2010 12:22 am
Localisation : Val de Marne
Contact :

Re: Quelle fonction postgis ?

Message par cquest » mer. août 29, 2018 7:41 pm

Je pense que c'est le ST_DWithin qui ne va pas...

Le way.path est en WGS84 (EPSG:4326) ?

Si oui, tu va sélectionner les POI à 2000° de ton morceau de linestring... soit tout ce qui peut être à la surface du globe (360° x 180°) !

Tu as de la chance, ST_DWithin fonctionne aussi sur les geography, donc en métrique, du coup ceci a plus de chance de fonctionner:

Code : Tout sélectionner

def poi_around_track_from(poi, dist)
    around_sql = <<-SQL
    SELECT
    ptb.*,
    ST_DWithin(
      ST_LineSubstring(
      way.path,
      ST_LineLocatePoint(way.path, pta.lonlat::geometry) + #{dist} / ST_Length(way.path::geography),
      ST_LineLocatePoint(way.path, pta.lonlat::geometry) + 20000 / ST_Length(way.path::geography)
      )::geography,
      ptb.lonlat::geography,
      2000) is true as pois
    FROM ways way, pois pta, pois ptb
    WHERE way.id = #{self.id}
      and pta.id = #{poi.id}
    SQL
    Poi.find_by_sql(around_sql)
  end
  
Par contre, le ST_DWithin aurait plus sa place dans le WHERE !

Code : Tout sélectionner

def poi_around_track_from(poi, dist)
    around_sql = <<-SQL
    SELECT
    ptb.* as pois
    FROM ways way, pois pta, pois ptb
    WHERE way.id = #{self.id}
      and pta.id = #{poi.id}
      and ST_DWithin(
      ST_LineSubstring(
      way.path,
      ST_LineLocatePoint(way.path, pta.lonlat::geometry) + #{dist} / ST_Length(way.path::geography),
      ST_LineLocatePoint(way.path, pta.lonlat::geometry) + 20000 / ST_Length(way.path::geography)
      )::geography,
      ptb.lonlat::geography,
      2000)
    SQL
    Poi.find_by_sql(around_sql)
  end
  
et ça serait plus propre avec un JOIN qui devrait ressembler à quelque chose comme

Code : Tout sélectionner

def poi_around_track_from(poi, dist)
    around_sql = <<-SQL
    SELECT
    ptb.* as pois
    FROM ways way
    JOIN pois pta
    JOIN pois ptb ON ST_DWithin(
      ST_LineSubstring(
      way.path,
      ST_LineLocatePoint(way.path, pta.lonlat::geometry) + #{dist} / ST_Length(way.path::geography),
      ST_LineLocatePoint(way.path, pta.lonlat::geometry) + 20000 / ST_Length(way.path::geography)
      )::geography,
      ptb.lonlat::geography,
      2000)
    WHERE way.id = #{self.id}
      and pta.id = #{poi.id}
    SQL
    Poi.find_by_sql(around_sql)
  end
  
Après il faudra voir si postgres arrive à bien utiliser les index géo... pas sûr.

Tioneb
Messages : 32
Inscription : mar. mars 27, 2018 4:03 pm

Re: Quelle fonction postgis ?

Message par Tioneb » jeu. août 30, 2018 11:28 pm

Un grand merci... grace à ton aide, je viens de passer une grosse étape.

J'ai encore besoin de décortiquer ces requêtes, pour bien comprendre comment elles fonctionnent et comment je peux ordonner tout ça. Finalement, j'avais comme un quinté dans le désordre.
C'est parfois obscur, mais avec ton aide, je commence à cerner certains points. Merci encore pour ta générosité, le temps que tu passes à répondre à des trucs qui probablement te semblent basiques.

Geography, geometry... comment l'appliquer, avec quelle méthode ? C'est pas toujours limpide, mais avec tes éclairages.... j'ai l'impression de progresser.

Alors tout simplement, un grand merci Quest ;)

Avatar de l’utilisateur
cquest
Messages : 1823
Inscription : ven. avr. 16, 2010 12:22 am
Localisation : Val de Marne
Contact :

Re: Quelle fonction postgis ?

Message par cquest » dim. sept. 02, 2018 4:22 pm

De rien, moi aussi j'ai patiné au début et on m'a montré le chemin... le déclic vient quand on comprend la logique.

Répondre

Qui est en ligne ?

Utilisateurs parcourant ce forum : Aucun utilisateur inscrit et 13 invités