How to connect all lines within in Multilinestring to one?

Question

I am trying below query to convert Multilinestring to Linestring. It’s output is a multi string because it can’t merge because of different values which is marked as bold in below code.

Is there different function to convert this to linestring? So, Please give me an idea…

select astext(st_Linemerge('MULTILINESTRING((73.757115 18.592133,73.756847 18.592839, 73.756599 18.59351,73.756313 18.594214,73.756074 18.594824,73.755916 18.595293,73.755199 18.597174, 73.754713 18.598488,73.754397 18.5993,73.754128 18.600052,73.753932 18.600594,73.753808 18.600893, 73.753723 18.60112,73.753454 18.601868,73.75307 18.602883,73.752814 18.603602,73.752524 18.604302, 73.752281 18.604937,73.752114 18.605394,73.751991 18.605802,73.751803 18.606368,73.751414 18.607346, 73.751082 18.608264,73.75103 18.608386,73.750804 18.609041,73.750382 18.610152,73.749959 18.61126, 73.749814 18.611681,73.749669 18.612113,73.749481 18.612603,73.749068 18.613723,73.748867 18.614252, 73.748543 18.615122,73.748201 18.616003,73.747958 18.61663,73.747536 18.617624,73.747177 18.618534, 73.746503 18.620115,73.746277 18.620701,73.746119 18.621073,73.745726 18.62202,73.745432 18.622735, 73.745167 18.62335,73.744399 18.625315,73.742741 18.629516,73.740813 18.634355,73.73931 18.638227, 73.737653 18.642391,73.735802 18.64703,73.734529 18.65031,73.733141 18.653762,73.732563 18.655059, 73.730755 18.659847,73.730615 18.660146,73.729577 18.662626,73.728733 18.66455,73.728283 18.665719, 73.727889 18.666553),(73.777732 18.528281,73.778042 18.528576,73.778281 18.52888,73.778425 18.529134, 73.778664 18.52962,73.778882 18.530115,73.779231 18.530925,73.779346 18.531171,73.779688 18.531996 ,73.779815 18.532332,73.779959 18.532794,73.780132 18.533532,73.7804 18.534849,73.780428 18.534958, 73.780523 18.53527,73.780852 18.536224,73.781091 18.536936,73.781144 18.537092,73.781189 18.53728, 73.781239 18.537754,73.781234 18.538097,73.781197 18.538425,73.781062 18.538942,73.780884 18.53932,73.780765 18.539555, 73.780395 18.540029,73.780288 18.54013,73.779835 18.540497,73.778888 18.541159,73.778489 18.541447,73.777267 18.542316, 73.777168 18.542402,73.776958 18.542608,73.776176 18.543266,73.77583 18.543567,73.774896 18.544404,73.774863 18.54445, 73.77467 18.544696,73.774497 18.544952,73.77434 18.545264,73.774159 18.545674,73.773904 18.546335,73.773784 18.546729, 73.773533 18.547375,73.77341 18.547664,73.773344 18.547844,73.773237 18.54813,73.773159 18.548314))'));

Thanks .....

Is it correct ?

select ST_MakeLine(geo.geom) from st_dump(ST_GeometryFromText('MULTILINESTRING((73.757115 18.592133,73.756847 18.592839, 73.756599 18.59351, 73.756313 18.594214,73.756074 18.594824,73.755916 18.595293,73.755199 18.597174, 73.754713 18.598488,73.754397 18.5993, 73.754128 18.600052,73.753932 18.600594,73.753808 18.600893, 73.753723 18.60112,73.753454 18.601868,73.75307 18.602883, 73.752814 18.603602,73.752524 18.604302, 73.752281 18.604937,73.752114 18.605394,73.751991 18.605802,73.751803 18.606368, 73.751414 18.607346, 73.751082 18.608264,73.75103 18.608386,73.750804 18.609041,73.750382 18.610152,73.749959 18.61126, 73.749814 18.611681,73.749669 18.612113,73.749481 18.612603,73.749068 18.613723,73.748867 18.614252, 73.748543 18.615122, 73.748201 18.616003,73.747958 18.61663,73.747536 18.617624,73.747177 18.618534, 73.746503 18.620115,73.746277 18.620701, 73.746119 18.621073,73.745726 18.62202,73.745432 18.622735, 73.745167 18.62335,73.744399 18.625315,73.742741 18.629516, 73.740813 18.634355,73.73931 18.638227, 73.737653 18.642391,73.735802 18.64703,73.734529 18.65031,73.733141 18.653762, 73.732563 18.655059, 73.730755 18.659847,73.730615 18.660146,73.729577 18.662626,73.728733 18.66455,73.728283 18.665719, 73.727889 18.666553),(73.777732 18.528281,73.778042 18.528576,73.778281 18.52888,73.778425 18.529134, 73.778664 18.52962, 73.778882 18.530115,73.779231 18.530925,73.779346 18.531171,73.779688 18.531996 , 73.779815 18.532332,73.779959 18.532794, 73.780132 18.533532,73.7804 18.534849,73.780428 18.534958, 73.780523 18.53527,73.780852 18.536224,73.781091 18.536936, 73.781144 18.537092,73.781189 18.53728, 73.781239 18.537754,73.781234 18.538097,73.781197 18.538425,73.781062 18.538942, 73.780884 18.53932, 73.780765 18.539555, 73.780395 18.540029,73.780288 18.54013,73.779835 18.540497,73.778888 18.541159, 73.778489 18.541447,73.777267 18.542316, 73.777168 18.542402,73.776958 18.542608,73.776176 18.543266, 73.77583 18.543567, 73.774896 18.544404,73.774863 18.54445, 73.77467 18.544696,73.774497 18.544952, 73.77434 18.545264,73.774159 18.545674, 73.773904 18.546335,73.773784 18.546729, 73.773533 18.547375, 73.77341 18.547664,73.773344 18.547844, 73.773237 18.54813,73.773159 18.548314))')) as geo;

BUT It gives NULL

Please run, ignore my error.. Thanks...

Answer 1

I'll need a bit more time to figure out the exact syntax, but I'd guess you need another line segment to connect the first linestring to the second linestring.

The rough approach is going to be to select the last point in the first linestring (using ST_NumPoints and ST_PointN), and the first point in the second linestring (using ST_PointN again, index 1), and using ST_MakeLine on those two to give you a new linestring, select the first linestring in the original set, then the new linestring, then the second linestring in the original set as a new MultiLinestring, then call ST_LineMerge on the result.

Sorry for the lack of a neat solution, but I'm optimistic that you can figure it from here :-)

Answer 2

To get a single connected line you should try ST_MakeLine, maybe like this:
q1 - to make MultiLine
q2 - to dump Multi to single Linestrings

with q1 as (select ST_GeometryFromText('MULTILINESTRING((73.757115 18.592133,73.756847 18.592839, 73.756599 18.59351,73.756313 18.594214,73.756074 18.594824,73.755916 18.595293,73.755199 18.597174, 73.754713 18.598488,73.754397 18.5993,73.754128 18.600052,73.753932 18.600594,73.753808 18.600893, 73.753723 18.60112,73.753454 18.601868,73.75307 18.602883,73.752814 18.603602,73.752524 18.604302, 73.752281 18.604937,73.752114 18.605394,73.751991 18.605802,73.751803 18.606368,73.751414 18.607346, 73.751082 18.608264,73.75103 18.608386,73.750804 18.609041,73.750382 18.610152,73.749959 18.61126, 73.749814 18.611681,73.749669 18.612113,73.749481 18.612603,73.749068 18.613723,73.748867 18.614252, 73.748543 18.615122,73.748201 18.616003,73.747958 18.61663,73.747536 18.617624,73.747177 18.618534, 73.746503 18.620115,73.746277 18.620701,73.746119 18.621073,73.745726 18.62202,73.745432 18.622735, 73.745167 18.62335,73.744399 18.625315,73.742741 18.629516,73.740813 18.634355,73.73931 18.638227, 73.737653 18.642391,73.735802 18.64703,73.734529 18.65031,73.733141 18.653762,73.732563 18.655059, 73.730755 18.659847,73.730615 18.660146,73.729577 18.662626,73.728733 18.66455,73.728283 18.665719, 73.727889 18.666553),(73.777732 18.528281,73.778042 18.528576,73.778281 18.52888,73.778425 18.529134, 73.778664 18.52962,73.778882 18.530115,73.779231 18.530925,73.779346 18.531171,73.779688 18.531996 , 73.779815 18.532332,73.779959 18.532794,73.780132 18.533532,73.7804 18.534849,73.780428 18.534958, 73.780523 18.53527,73.780852 18.536224,73.781091 18.536936,73.781144 18.537092,73.781189 18.53728, 73.781239 18.537754,73.781234 18.538097,73.781197 18.538425,73.781062 18.538942,73.780884 18.53932, 73.780765 18.539555, 73.780395 18.540029,73.780288 18.54013,73.779835 18.540497,73.778888 18.541159, 73.778489 18.541447,73.777267 18.542316, 73.777168 18.542402,73.776958 18.542608,73.776176 18.543266, 73.77583 18.543567,73.774896 18.544404,73.774863 18.54445, 73.77467 18.544696,73.774497 18.544952, 73.77434 18.545264,73.774159 18.545674,73.773904 18.546335,73.773784 18.546729, 73.773533 18.547375, 73.77341 18.547664,73.773344 18.547844,73.773237 18.54813,73.773159 18.548314))') as geo), q2 as ( select (st_dump(geo)).geom as geo from q1) select st_makeline(geo) from q2

Answer 3

FINALY I GOT . HERE IS THE QUERY...... (for postgres 8.4)

create table temp_createlinestring as select ST_PointN(geo.geom,generate_series(1, ST_NPoints(geo.geom))) from st_dump(ST_GeometryFromText('MULTILINESTRING((73.757115 18.592133,73.756847 18.592839, 73.756599 18.59351, 73.756313 18.594214,73.756074 18.594824,73.755916 18.595293,73.755199 18.597174, 73.754713 18.598488,73.754397 18.5993, 73.754128 18.600052,73.753932 18.600594,73.753808 18.600893, 73.753723 18.60112,73.753454 18.601868,73.75307 18.602883, 73.752814 18.603602,73.752524 18.604302, 73.752281 18.604937,73.752114 18.605394,73.751991 18.605802,73.751803 18.606368, 73.751414 18.607346, 73.751082 18.608264,73.75103 18.608386,73.750804 18.609041,73.750382 18.610152,73.749959 18.61126, 73.749814 18.611681,73.749669 18.612113,73.749481 18.612603,73.749068 18.613723,73.748867 18.614252, 73.748543 18.615122, 73.748201 18.616003,73.747958 18.61663,73.747536 18.617624,73.747177 18.618534, 73.746503 18.620115,73.746277 18.620701, 73.746119 18.621073,73.745726 18.62202,73.745432 18.622735, 73.745167 18.62335,73.744399 18.625315,73.742741 18.629516, 73.740813 18.634355,73.73931 18.638227, 73.737653 18.642391,73.735802 18.64703,73.734529 18.65031,73.733141 18.653762, 73.732563 18.655059, 73.730755 18.659847,73.730615 18.660146,73.729577 18.662626,73.728733 18.66455,73.728283 18.665719, 73.727889 18.666553),(73.777732 18.528281,73.778042 18.528576,73.778281 18.52888,73.778425 18.529134, 73.778664 18.52962, 73.778882 18.530115,73.779231 18.530925,73.779346 18.531171,73.779688 18.531996 , 73.779815 18.532332,73.779959 18.532794, 73.780132 18.533532,73.7804 18.534849,73.780428 18.534958, 73.780523 18.53527,73.780852 18.536224,73.781091 18.536936, 73.781144 18.537092,73.781189 18.53728, 73.781239 18.537754,73.781234 18.538097,73.781197 18.538425,73.781062 18.538942, 73.780884 18.53932, 73.780765 18.539555, 73.780395 18.540029,73.780288 18.54013,73.779835 18.540497,73.778888 18.541159, 73.778489 18.541447,73.777267 18.542316, 73.777168 18.542402,73.776958 18.542608,73.776176 18.543266, 73.77583 18.543567, 73.774896 18.544404,73.774863 18.54445, 73.77467 18.544696,73.774497 18.544952, 73.77434 18.545264,73.774159 18.545674, 73.773904 18.546335,73.773784 18.546729, 73.773533 18.547375, 73.77341 18.547664,73.773344 18.547844, 73.773237 18.54813,73.773159 18.548314))')) as geo;

select astext(ST_MakeLine(st_pointn)) from temp_createlinestring;

THANKS FOR EVERY ONE... :)