How can PostGIS shorten the end segments of polylines?

How can PostGIS shorten the end segments of polylines?

I already figured out how to calculate the length of the shortest end segment of any end of any polyline in my_table:

select min(least) from
(select least
(ST_Length
(ST_MakeLine
(ST_PointN(geom, 1),
ST_PointN(geom, 2))),
ST_Length
(ST_MakeLine
(ST_PointN(geom, ST_NPoints(geom) - 1),
ST_PointN(geom, ST_NPoints(geom)))))
from (select (ST_Dump(geom)).geom as geom from my_table) as polylines)
as a;

Now I just want to shorten both end segments of every polyline with 1/3 of that value. (So that a polyline with a single segment will still consist of the middle 1/3 of the original.)

• so you want to go from here (dots are vertices, hyphens are 1\3 of a segment): .---.---.---. to .-.---.-. or, if only one segment, .---. to .-. (middle one)? is this somehow related to your other question? if so, maybe be a little more specific about your goal ,) – ThingumaBob Mar 12 '18 at 9:54
• I want to trim the same length from both ends of every polyline in the layer. For the shortest end segment in the layer, that will be 1/3 of it. (I chose 1/3 because if I would choose 1/2, a polyline would be lost completely if it consists of a single segment and no other polyline has a shorter end segment.) I have a Python/QGIS script that does this and the thing in the other question. But since I have to move the data over to PostGIS at the end, I would like to see if I could just do it all in PostGIS. The densified lines with trimmed ends are meant to be input to ST_VoronoiPolygons. – odalman Mar 12 '18 at 18:22

You can use:

select ST_LineSubstring(geom,1/3.0,2/3.0) from my_polyline

I quote the documentation:

ST_LineSubstring(geometry a_linestring, float8 startfraction, float8 endfraction):

Return a linestring being a substring of the input one starting and ending at the given fractions of total 2d length. Second and third arguments are float8 values between 0 and 1.

PostGIS documentation

Or if you want to delete 1/3 of the length of the shortest segment in your table to each line (using your query):

with minval AS
(
select min(least) from
(select least
(ST_Length
(ST_MakeLine
(ST_PointN(geom, 1),
ST_PointN(geom, 2))),
ST_Length
(ST_MakeLine
(ST_PointN(geom, ST_NPoints(geom) - 1),
ST_PointN(geom, ST_NPoints(geom)))))
from (select (ST_Dump(geom)).geom as geom from osm_edge) as polylines)
as a
)
select ST_LineSubstring(geom,(1/3.0)*min/st_length(geom),1-(1/3.0)*(min/st_length(geom))) from osm_edge, minval