How to split/divide linestrings of larger lengths in postgis

I want to split/divide linestrings in my data. I have roads data and I want to have every road segment having length not more than 250 meter.

How to split/divide linestrings of larger lengths in postgis

Check the shape length of this particular line feature. I want every road length less than 250 meter. Obviously this particular road must be divided in several parts.

If ST_Line_Substring is the solution, kindly elaborate. I can't figure out how to apply this in my case


Not tested pseudo code

CREATE TABLE work (sid serial , gid biginteger, geom geometry)
INSERT INTO work (gid, geom) SELECT gid, geom from baselines WHERE ST_Length(geom) > 250
SELECT floor(max(1/st_length(geom))) as forloop  from work -- for future use 

SELECT max(sid) as max_sid from work -- for future use

LOOP this : do forloop times (4.5 -> 4 times , etc )

INSERT INTO work (gid,geom) SELECT gid ,ST_Line_Substring(geom,0,5) from baselines  UNION ALL gid, ST_Line_Substring(geom,0.5,1)
DELETE FROM work WHERE sid < max_sid

SELECT max(sid) as max_sid from work -- new max_sid for deleteting old geoms

end loop this:

After forloop times you should have table with has only 250m length or less geoms.

SELECT * INTO work2 FROM baselines  WHERE ST_Length(geom) > 250
DELETE FROM baselines  WHERE ST_Length(geom) > 250

copy data from work to baselines using join with works2 table data

You could also create plpgpsql function from this, see postgresql documentation

Another option if you dont like really short geoms wich this will create, is create check into for loop for some min value , and move those into result table and delete from work table.

performance wise this shuld be 50x faster than do plpgsql function which for loops over one geom at time. (postgresql 8.4 5days and not ready, changed similiar code to loop over whole table 22 times, which was my max value , job was done in 4 hours )

Category: postgis Time: 2014-04-15 Views: 1

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development


Front-end development


development tools

Open Platform

Javascript development

.NET development

cloud computing


Copyright (C), All Rights Reserved.

processed in 0.271 (s). 12 q(s)