1

I have a strange berhaviour in this postGIS query:

SELECT 
st_length(st_linemerge(v.geom)),
c.prog_ini,
c.prog_fin,
c.prog_ini/st_length(st_linemerge(v.geom)) as ini,
c.prog_fin/st_length(st_linemerge(v.geom))as fin
,
ST_Line_Substring ( 
st_linemerge(v.geom),
0.00505368,
--(c.prog_ini/st_length(st_linemerge(v.geom))) ,
--(c.prog_fin/st_length(st_linemerge(v.geom)))
0.98862698327
)
FROM geometrie.route v, geometrie.test_editing_banchine c
WHERE c.id=(SELECT id FROM geometrie.test_editing_banchine ORDER BY id desc limit 1) 
order by 
st_distance(
v.geom,
(select geom from geometrie.test_editing_banchine order by id desc limit 1)
)
limit 1

c.prog_ini ~ 16

c.prog_fin ~ 3130

st_length(st_linemerge(v.geom)) ~ 3166

-->

c.prog_ini/st_length(st_linemerge(v.geom)) = 0.00505368

and

c.prog_fin/st_length(st_linemerge(v.geom)) = 0.98862698327

Nevertheless if I use c.prog_fin/st_length(st_linemerge(v.geom)) in the ST_Line_Substring function it give me this strange error : line_interpolate_point: 3rd arg isn't within [0,1] strange behavour

The following code using a multiline extracted from database as WKT run, but I do not understand why. I have extracted data from PostGIS and saved as geopackage file

DROP TABLE route_test;

CREATE TABLE route_test (
    gid integer NOT NULL,
    geom geometry(MultiLineStringZ,25832),
    CONSTRAINT route_pkey PRIMARY KEY (gid) );


INSERT INTO route_test(     gid,  geom)     VALUES ( 1 , st_geomfromtext(' MULTILINESTRINGZ ((552870.079182017 4975608.0872109 0,552863.928878817
4975605.22438149 0,552858.4079636 4975603.01583816 0,552857.656937005 4975602.715425 0,552849.650736397 4975604.02613298 0,552840.64907789 4975606.59785298 0,552831.614026171 4975608.02451834 0,552828.432932115 4975608.52668306 0,552819.398683959
4975611.49597686 0,552788.666634788 4975615.25887112 0,552756.473805761 4975620.62236648 0,552678.339864291
4975633.63992574 0,552557.373880618 4975653.54486617 0,552525.218462427 4975658.64878726 0,552327.622462795
4975690.42472925 0,552227.108015194 4975718.78024926 0,552105.383953447 4975764.42584021 0,551949.540417632
4975820.44538071 0,551885.911822372 4975842.57668511 0,551809.379121691 4975870.15340717 0,551806.759177161
4975871.09697318 0,551803.379237542 4975872.31512546 0,551701.020527821 4975894.4461884 0,551576.99073443 4975915.88529909 0,551491.081111876 4975933.21036542 0,551456.082581007
4975946.42067222 0,551392.954650983 4975981.23030989 0,551359.792727037 4975999.77810068 0,551353.331676582 4976003.3919956 0,551249.90914644 4976060.36314126 0,551145.143687712 4976119.34901554 0,551053.137737444 4976172.29073488 0,551020.421585494
4976188.27273662 0,550955.75965049 4976219.85992434 0,550865.09689163 4976266.08617302 0,550858.129804193 4976269.48846335 0,550780.814485796 4976307.24439802 0,550624.500956325 4976384.7435956 0,550605.785135294 4976394.02270788 0,550553.242764374
4976420.07262972 0,550530.895177457 4976431.15228196 0,550484.691467709 4976450.40361963 0,550224.271294783
4976534.40852089 0,550104.561932027 4976580.96124509 0,550062.013950878 4976595.37250615 0,550021.310661589
4976609.15860862 0,549992.363990882 4976618.96304992 0,549974.351749695 4976625.06383419 0,549945.370657045
4976635.22605682 0,549908.481140796 4976648.16112703 0))     ', 25832)  );


SELECT  1 as id,
--c.prog_ini,
--c.prog_fin,
16,
 3130, 
st_length(st_linemerge(v.geom)), 
16/st_length(st_linemerge(v.geom))::float as ini, 
3130/st_length(st_linemerge(v.geom))::float as fin, 
ST_Line_Substring (  st_linemerge(v.geom), 
16/st_length(st_linemerge(v.geom))::float, 
3160/st_length(st_linemerge(v.geom))::float 
) as geom 
FROM  route_test v;
  • 6
    Would you be able to create an example with actual geometries in it? So replace the v.geom with some 'WKT' sting. In that way it is easier to test it. – tilt Feb 13 at 15:18
  • In the following answer I try to create the table suing WKT geom, but I do not encountered the error... Nevertheless using the layers in this geopackage file i encountered the error – Roberto Marzocchi Feb 14 at 22:36
  • Some of your line have a length smaller than 3160, so your second division is sometime bigger than 1, which is not accepted by st_line_substring. – obchardon Feb 18 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.