Le portail francophone de la géomatique

Ricola62 · Fri 27 November 2020 18:23

Bonjour,
J'ai un soucis avec une multiligne cf image
après un dump, ou j'isole st_startpoint et st_endpoint voici ce que j'ai

Code:

"idlin","startpt","endpt"
1,"POINT Z (477969.399999933 6758795.99999823 33.1)","POINT Z (475644.399999933 6760065.69999823 32.8)"
2,"POINT Z (478043.799999933 6758632.49999823 32.8)","POINT Z (477969.399999933 6758795.99999823 33.1)"
3,"POINT Z (478043.799999933 6758632.49999823 32.8)","POINT Z (477969.399999933 6758795.99999823 33.1)"
4,"POINT Z (481522.099999934 6758363.19999823 34.1)","POINT Z (478043.799999933 6758632.49999823 32.8)"

L'idée est d'écarter la ligne 2 ou 3. je précise que les lignes 2 et 3 n'ont pas le même geom.
J'ai utilisé le CTE 'WITH RECURSIVE'

Code:

WITH RECURSIVE t(id,g,explored_paths) AS (with ml as (select 'MULTILINESTRING Z ((477969.399999933 6758795.99999823 33.1,477967.799999933 6758802.29999823 33.1,477944.799999933 6758894.59999823 33.1,477943.499999933 6758900.09999823 33.1,477933.499999933 6758944.89999823 33.1,477930.699999933 6758980.29999823 33.1,477930.099999933 6759079.29999823 33.1,477934.499999933 6759171.79999823 33.1,477930.899999933 6759239.49999823 33.1,477922.399999933 6759332.29999823 33.1,477907.899999933 6759427.69999823 33.1,477884.299999933 6759552.59999823 33.1,477864.899999933 6759611.29999823 33.1,477831.899999933 6759694.49999823 33.1,477795.899999933 6759771.69999823 33.1,477763.999999933 6759825.49999823 33.1,477726.899999933 6759879.99999823 33.1,477675.999999933 6759941.09999823 33.1,477607.199999933 6760000.59999823 33.1,477567.099999933 6760030.09999823 33.1,477511.399999933 6760059.39999823 33.1,477441.399999933 6760085.89999823 33.1,477339.999999933 6760113.09999823 33.1,477307.399999933 6760117.79999823 33.1,477282.999999933 6760117.79999823 33.1,477232.299999933 6760107.89999823 33.1,477112.399999933 6760074.79999823 33.1,477085.399999933 6760069.39999823 33.1,477082.399999933 6760068.69999823 33.1,477059.399999933 6760066.09999823 33.1,477030.799999933 6760065.89999823 33.1,477007.199999933 6760071.69999823 33.1,476960.599999933 6760086.99999823 32.9,476945.199999933 6760092.09999823 32.9,476914.199999933 6760096.79999823 32.7,476892.199999933 6760098.99999823 32.7,476870.199999933 6760098.79999823 32.7,476836.999999933 6760098.69999823 32.7,476741.999999933 6760086.79999823 32.7,476677.699999933 6760071.59999823 32.7,476594.399999933 6760043.99999823 32.7,476590.699999933 6760042.79999823 32.7,476471.099999933 6760006.09999823 32.7,476372.999999933 6759980.39999823 32.7,476302.599999933 6759967.89999823 32.7,476198.299999933 6759954.19999823 32.7,476070.699999933 6759945.99999823 32.7,475965.899999933 6759950.59999823 32.7,475881.299999933 6759967.29999823 32.7,475807.699999933 6759986.49999823 32.7,475728.899999933 6760017.39999823 32.8,475726.899999933 6760018.49999823 32.8,475671.599999933 6760048.59999823 32.8,475644.399999933 6760065.69999823 32.8),(478043.799999933 6758632.49999823 32.8,478010.599999933 6758689.99999823 33.2,478008.499999933 6758695.19999823 33,478003.999999933 6758706.99999823 32.8,477969.399999933 6758795.99999823 33.1),(478043.799999933 6758632.49999823 32.8,478025.199999933 6758735.99999823 33.2,478018.999999933 6758748.89999823 32.8,477992.899999933 6758771.19999823 33,477969.399999933 6758795.99999823 33.1),(481522.099999934 6758363.19999823 34.1,481510.099999934 6758407.69999823 34,481503.799999934 6758433.49999823 33.9,481493.999999934 6758464.49999822 33.9,481483.099999934 6758495.89999823 33.9,481468.299999934 6758530.19999823 33.9,481453.999999934 6758557.09999823 33.9,481435.799999934 6758585.59999823 33.9,481411.599999934 6758612.99999823 33.9,481384.899999934 6758635.79999823 33.9,481360.599999934 6758652.19999823 33.9,481333.799999934 6758666.49999823 33.9,481307.999999934 6758676.39999823 33.9,481267.999999934 6758685.69999823 33.9,481230.999999934 6758692.49999823 34.1,481186.499999934 6758699.39999823 33.9,481143.299999934 6758705.99999823 34.1,481123.199999934 6758707.99999823 34,481096.199999934 6758708.99999823 34,481063.499999934 6758707.89999823 34,481030.399999934 6758705.79999823 34.1,481006.699999934 6758703.59999823 34.1,480975.599999934 6758700.19999823 34,480936.999999934 6758696.69999823 34,480891.499999934 6758690.69999823 34,480851.699999934 6758685.49999822 34,480795.799999934 6758676.49999823 34,480767.299999934 6758672.39999823 34,480752.599999934 6758670.59999823 34,480722.799999934 6758668.99999823 34,480656.199999934 6758667.39999823 34,480591.199999934 6758665.99999823 34,480548.999999934 6758664.99999823 34,480542.499999934 6758664.59999823 34,480537.499999934 6758664.39999823 34,480483.799999934 6758663.19999823 33.8,480451.999999934 6758660.89999823 33.8,480407.499999934 6758655.49999823 33.8,480352.899999934 6758642.79999823 33.6,480301.799999934 6758627.89999823 33.6,480242.999999934 6758611.89999823 33.6,480177.899999934 6758597.19999823 33.6,480109.999999934 6758579.39999823 33.6,480079.799999934 6758573.19999823 33.6,480027.799999934 6758553.59999822 33.6,479975.499999934 6758533.09999823 33.6,479914.299999934 6758502.39999823 33.6,479905.599999934 6758498.39999823 33.6,479831.299999934 6758460.29999822 33.6,479763.299999934 6758424.29999823 33.6,479731.699999934 6758403.29999822 33.6,479709.799999934 6758386.69999823 33.6,479672.899999934 6758357.19999823 33.6,479671.199999934 6758356.09999823 33.6,479648.199999934 6758341.09999823 33.6,479642.599999934 6758337.29999823 33.6,479627.499999934 6758325.19999823 33.6,479597.599999934 6758300.09999823 33.6,479568.699999934 6758275.39999823 33.6,479545.399999934 6758254.09999823 33.6,479530.399999934 6758240.99999823 33.6,479523.399999934 6758234.89999823 33.6,479456.099999934 6758174.79999822 34,479436.099999934 6758161.39999823 34,479419.999999934 6758148.59999822 34,479401.499999934 6758137.89999822 34,479388.799999934 6758130.59999822 34,479353.299999934 6758110.99999823 34,479309.399999934 6758090.79999822 34,479259.499999934 6758073.59999823 34,479161.599999934 6758041.79999822 34.3,479113.699999934 6758027.99999822 34.3,479109.999999934 6758026.49999822 34.3,479052.099999933 6758013.69999822 34.3,478997.299999934 6758001.39999823 34.3,478961.399999933 6757993.39999823 34.3,478912.799999933 6757986.19999823 34.3,478851.999999933 6757981.79999822 34.3,478826.099999933 6757981.59999822 34.3,478798.599999933 6757981.29999822 34.3,478762.799999933 6757981.09999822 34.3,478704.199999933 6757979.49999822 34.3,478649.199999933 6757973.49999822 34.3,478591.099999933 6757965.79999822 34.3,478537.999999933 6757958.99999822 34.3,478510.399999933 6757958.79999822 34.3,478490.199999933 6757959.69999822 34.3,478459.999999933 6757963.19999823 33.2,478427.899999933 6757972.39999823 33.2,478393.299999933 6757983.69999823 33.2,478368.299999933 6757996.89999822 33.2,478350.099999933 6758007.69999823 33.2,478326.099999933 6758030.29999822 33.2,478274.999999933 6758088.29999822 33.2,478223.599999933 6758156.59999822 33.2,478194.899999933 6758201.69999823 33.2,478173.699999933 6758244.79999822 33.2,478154.299999933 6758289.89999823 33.2,478128.999999933 6758345.29999823 33.2,478112.199999933 6758389.69999823 33.2,478105.499999933 6758412.79999822 33.2,478088.699999933 6758475.19999823 33.2,478069.499999933 6758552.59999823 33.2,478045.499999933 6758626.99999823 32.8,478043.799999933 6758632.49999823 32.8))'::geometry as geom)
    , l as 
    (select (st_dump(geom)).path[1] as idln, (st_dump(geom)).geom
    from ml)
    SELECT l.idln,l.geom, ARRAY[idln]
    FROM l
    WHERE idln=1
  UNION ALL                                 
    SELECT idln, geom, explored_paths || idln
    FROM l, t
    WHERE ST_touches(geom, t.g)
    AND NOT (idln= ANY(explored_paths))
)
SELECT *, row_number () over () FROM t;

Cependant je n'arrive pas à filtrer .
avez vous une idée me permettant d'avancer?

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Nicolas Ribot · Mon 30 November 2020 11:41

Bonjour,

Il y a plusieurs façons de filtrer les lignes sur la base de startpoint/endpoint identiques:

Par exemple avec DISTINCT ON:
On construit une geom avec les start et endpoint, puis on utilise cette geom dans une construction DISTINCT ON: une des deux lignes avec start/end identiques à une autre est virée: on n'a pas le controle sur celle qui est virée:

Code:

with tmp as (
    select * from ( values
        (1,'POINT Z (477969.399999933 6758795.99999823 33.1)'::geometry,'POINT Z (475644.399999933 6760065.69999823 32.8)'::geometry),
        (2,'POINT Z (478043.799999933 6758632.49999823 32.8)'::geometry,'POINT Z (477969.399999933 6758795.99999823 33.1)'::geometry),
        (3,'POINT Z (478043.799999933 6758632.49999823 32.8)'::geometry,'POINT Z (477969.399999933 6758795.99999823 33.1)'::geometry),
        (4,'POINT Z (481522.099999934 6758363.19999823 34.1)'::geometry,'POINT Z (478043.799999933 6758632.49999823 32.8)'::geometry)
        ) as t(id, startpt, endpt)
), tmp1 as (
    select id, st_collect(startpt, endpt) as geom
    from tmp
) select distinct on (geom) id, geom
from tmp1;

id    geom
4    0104000080...
1    0104000080...
2    0104000080...

Nicolas

Nicolas Ribot · Mon 30 November 2020 11:54

Vous pouvez aussi utiliser une WINDOW FUNCTION comme row_number pour identifier les lignes identiques et ne garder que celle qui correspond à votre critère de choix.
Par ex, en voulant conserver la plus petite lignes des deux:

• On construit la geom avec les start et endpoint des lignes
• on fait une partition par geom identiques, en classant les geoms au sein de la partition par longueur croissante. row_number nous donne le numéro de ligne de chaque geom dans sa partition (la plus courte a donc tjs le numéro 1)
• on ne garde que les lignes de numéro 1: on filtre alors les doublons.

Code:

with tmp as (
    select id, st_length(geom) as len, st_collect(st_startpoint(geom), st_endpoint(geom)) as geom
    from testln2
), tmp1 as (
    select id, len, row_number() over (partition by geom order by len) as rn
    from tmp
) select t.id, t.len, l.geom
from tmp1 t join testln2 l on t.id = l.id
where rn = 1;

id    len    geom
4    417.3127159332863    01020000000400...
2    48.129166707149636    01020000000300...
1    277.6032849817096    01020000000500...

En faisant "order by len desc" on aurait gardé la ligne 3 et viré la 2

Nico

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Fri 27 November 2020 18:23

doublons

Code:

Code:

#2 Mon 30 November 2020 11:41

Re: doublons

Code:

#3 Mon 30 November 2020 11:54

Re: doublons

Code:

Pied de page des forums