1

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
1

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

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.