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