Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a postgis table full of very small line segments. I would like to merge them according to an attribute in another field.

update line_table set the_geom = ST_LineMerge(the_geom);

but how do I do this according to the attribute so that all the small segments with name="west" are joined, and those with name="north" are all joined etc?

Thanks for any help,

Robert

share|improve this question

1 Answer

up vote 1 down vote

UPDATE works with WHERE conditions just like SELECT. So it is a simple matter of appending the condition:

update line_table set the_geom = ST_LineMerge(the_geom) WHERE name="west";
update line_table set the_geom = ST_LineMerge(the_geom) WHERE name="east";
update line_table set the_geom = ST_LineMerge(the_geom) WHERE name="north";
update line_table set the_geom = ST_LineMerge(the_geom) WHERE name="south";
...
share|improve this answer
would something like "update line_table set the_geom = ST_LineMerge(the_geom) GROUP BY name;" accomplish the task? guess i've never considered whether or not GROUP BY works in an UPDATE, but it feels too good to be true... – rec.thegeom Jul 11 '12 at 18:50
As can be seen in the linked synopsis, that isn't even part of the valid syntax. If it had any effect, I'd expect it to still merge everything, you'd just have 4 sub-lines of same-named lines in one aggregate line. – lynxlynxlynx Jul 11 '12 at 18:53
rtfm to me. didn't see the link. asked in hopes there might be a way to avoid writing a function to loop through the name values when there are 4,000 instead of four. Perhaps a WITH RECURSIVE... preceding the UPDATE would do the trick? thanks for the feedback. – rec.thegeom Jul 11 '12 at 19:37
Maybe using FROM line_table AS b WHERE name = b.name would work, I doubt recursion is needed. Even easier if you use a wrapper script in another language. – lynxlynxlynx Jul 11 '12 at 19:55
However, creating a new table and inserting rows with a INSERT INTO ... SELECT FROM ... GROUP BY ... should work fine. – Micha Jul 12 '12 at 18:16

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.