Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
    
So you want to connect the two lines in your multilinestring by connecting the last node of the first line with the first node of the second line? –  underdark Aug 1 '12 at 8:17
    
yes friend, idea? –  shafeek Aug 1 '12 at 8:42

3 Answers 3

up vote 2 down vote accepted

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

share|improve this answer

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

share|improve this answer
    
Thanks friend, But gives NULL value :( –  shafeek Aug 1 '12 at 9:21
    
hm, maybe bad copy/paste or bad aliases for the functions? –  zimirrr Aug 1 '12 at 9:25
    
friend, In st_makeline() the parameter should be points geometry/array of points, but here i am getting parameter as linestring by query q1. –  shafeek Aug 1 '12 at 10:01
    
don't shure if you're right, ST_MakeLine says another. –  zimirrr Aug 1 '12 at 10:09
    
thanks.I am using postgres 8.4,is there any problem in postgres 8.4 ? –  shafeek Aug 1 '12 at 10:24

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.