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)
);

Answer 1

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.

• Pitrako Junior: 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: Sorry, ignore first question. I just need to remove 'ST_AsText' from the query. I'd appreciate any tip on the 2nd one.
• geozelot: @PitrakoJunior see update
• geozelot: @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!
• Pitrako Junior: 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.