[POSTGIS] Supprimer une ligne d'une multiligne

Version imprimable

1 pièce(s) jointe(s)

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

Code:

1
2
3
4
5
"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.
J'ai utilisé le CTE 'WITH RECURSIVE'

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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?

27/11/2020, 19h14
ced

Bonjour,

Selon quel critère souhaitez-vous faire le choix entre la ligne 2 et la ligne 3 ? Le hasard ? La longueur la moins grande ? Autre chose ?

ced
27/11/2020, 20h16
Rizcolas62

eh bien la longueur la plus courte.

Quelque chose comme ça ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
)
, diff_l AS (
    SELECT idln, geom, count(*) over(PARTITION BY st_startpoint(geom), st_endpoint(geom) ORDER BY st_length(geom)) AS ordre
    FROM l
)
SELECT st_collect(geom)
FROM diff_l
WHERE ordre = 1;

28/11/2020, 13h00
Rizcolas62

Parfait! Merci! j'ai absolument pas pensé à utiliser le fenêtrage :/