Using ST_Segmentize to PostgreSQL table of segments?

Question

I have a PostgreSQL table with straight lines (segments) that I need to subdivide in further segments so that none of them is more than 50 metres long. I am new using PostGIS (and SQL) and I'm struggling quite a bit to understand 'ST_xxxx' commands in general. The examples I see in the documentation are way too simple. Like the following one:

SELECT ST_AsText(ST_Segmentize(
ST_GeomFromText('MULTILINESTRING((-29 -27,-30 -29.7,-36 -31,-45 -33),(-45 -33,-46 32))'),5)
);

How can I apply ST_Segmentize (and later on ST_AsText) to the geometry of my table of segments? I can obviously obtain all the geometries of the segments with a simple query (SELECT geom from public.segments). But then, how should I insert the result of this query into the code shown above?

Something like this does not work:

SELECT ST_AsText(ST_Segmentize(
ST_GeomFromText(SELECT geom from public.segments),50)
);

geozelot · Accepted Answer · 2019-09-07 06:29:02Z

When you SELECT a column FROM a table, the column value(s) are made available to functions, either per row, or as aggregates. Function results can be passed consecutively into enclosing functions.

In this case you would simply

SELECT ST_AsText(ST_Segmentize(geom::GEOGRAPHY, 50)) AS geom
FROM   public.segments
;

Note that I use a cast (::) to GEOGRAPHY; since you intent to use meter as distance threshold, you either need your geometries projected in a suitable CRS (with meter as unit), or the GEOGRAPHY type, which implicitly uses meter as unit; this only works on geographic reference systems, and the cast assumes EPSG:4326 (WGS84) as the default.

However:

ST_Segmentize only inserts vertices to ensure no two vertices are further apart than the given threshold (the definition of a segment is a geometry part between two vertices)!

To obtain individual geometries representing parts of your 'segments' with a max. length, you'd need to get more involved; there is no simple command as of yet.

There are multiple possibilities, e.g.:

create line 'segments' from the densified lines (following the usage of ST_Segmentize above); you'd get 'segments' with arbitrary lines not longer than the threshold given to ST_Segmentize.
create line 'segments' of fixed length, using ST_LineSubstring; well defined line length, e.g. you could find a length that divides a line equally

For your case (initial two-vertice 'segments' will have segments with exactly 50m length, except the last one with up to 50m length), the first approach should work just fine:

WITH
  densified AS (
    SELECT <id>, -- ROW_NUMBER() OVER() AS id
           ST_AsText(ST_Segmentize(geom, 50)) AS geom
    FROM   public.segments
  )

SELECT id AS line_id,
       path[1] AS segment_id,
       geom
FROM   (
  SELECT d.id,
         dmp.path,
         ST_MakeLine(dmp.geom, LEAD(dmp.geom) OVER(PARTITION BY d.id ORDER BY dmp.path)) AS geom
  FROM   densified AS d,
         LATERAL ST_DumpPoints(d.geom) AS dmp
) q
WHERE  geom IS NOT NULL
;

Don't forget to replace <id> with the actual id column of your table, or use the commented out ROW_NUMBER

Note that I do not use a cast to GEOGRAPHY, since your mentioned CRS is a metric projection already.

Thanks for the tip. I'm using British National Grid (27700). If I put 'geom:GEOMETRY' it seems to work fine. There are two aspects I'd like to ask: a) The geom column created is text type, what do I need to do to make it of Geometry type? b) If ST_Segmentize only insterts vertices. What would be then the command to obtain the actual subsegments? — Pitrako Junior
– Pitrako Junior, Commented Sep 5, 2019 at 10:36
Sorry, ignore first question. I just need to remove 'ST_AsText' from the query. I'd appreciate any tip on the 2nd one. — Pitrako Junior
– Pitrako Junior, Commented Sep 5, 2019 at 10:51
@PitrakoJunior btw., I referred to ::GEOGRAPHY, not ::GEOMETRY; it's a GEOMETRY already; read about those two type in the docs. the unit of that CRS is metre already, so no need for any cast at all! — geozelot
– geozelot, Commented Sep 5, 2019 at 11:50
It seems to be working fine. Thanks for the script. I must admit I understand very little of it. I'll have to spend some time reading some tutorials. I was aware you had used GEOGRAPHY, it was just giving me a lot of warnings. I changed to GEOMETRY since I have projected coordinates, and then it worked fine. I'll read about it anyway. — Pitrako Junior
– Pitrako Junior, Commented Sep 5, 2019 at 12:08

Stack Exchange Network

Using ST_Segmentize to PostgreSQL table of segments?

1 Answer 1

Your Answer

Linked

Hot Network Questions

Using ST_Segmentize to PostgreSQL table of segments?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions