postgresql 把几列的数据合并为一列？

Question

现有如下数据：
sroadid    id       geom
"4L4552";337671;"POINT(120.24588 31.50656)"
"4L4552";337672;"POINT(120.245696625233 31.506692654087)"
"4L4552";337673;"POINT(120.245513250465 31.506825308174)"
"4L4552";337674;"POINT(120.245340073154 31.5069699268456)"
"4L4552";337675;"POINT(120.245180036577 31.5071299634228)"
"4L4552";337676;"POINT(120.24502 31.50729)"
"5L30394";337677;"POINT(120.22481 31.68237)"
"5L30394";337678;"POINT(120.224603162589 31.6823602664747)"
"5L30394";337679;"POINT(120.224396325177 31.6823505329495)"
"5L30394";337680;"POINT(120.224189487765 31.6823407994242)"
"5L30394";337681;"POINT(120.223982650354 31.682331065899)"
"5L30394";337682;"POINT(120.22378 31.68229)"
"5L5511";337683;"POINT(120.36083 31.51451)"
"5L5511";337684;"POINT(120.360941252689 31.5147914038613)"如何通过sql语句变为如下数据：
sroadid        geoms
4L4552        POINT(120.24588 31.50656)...POINT(120.24502 31.50729)
5L30394       POINT(120.22481 31.68237)...POINT(120.22378 31.68229)
...
geoms中point要按id顺序排列相组合。我用了group by  但是geom没有按顺序添加到geoms中。

Answer 1

转：
postgresql没有现成的group_concat，不过postgresql还是很强的，你可以用aggregate function来自己定义一个group_concat，如下
PostgreSQL:create aggregate array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);CREATE OR REPLACE FUNCTION _group_concat(text, text)
RETURNS text AS $$
SELECT CASE
WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL THEN $2
ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
END
$$ IMMUTABLE LANGUAGE SQL;CREATE AGGREGATE group_concat (
BASETYPE = text,
SFUNC = _group_concat,
STYPE = text
);
select name, array_accum(seq_id)
from produk
group by name

Answer 2

concat(字段1,'自定义字符串',字段2.....)mysql是这么用，这应该是通用的吧

Answer 3

wwwwb:
使用了您给的方法array_accum
能够组合我想要的字段
但是有些组合的字段还是无序的
单个执行是正确的：
select m.sroadid , array_accum(geoms) 
from ch10.mulitpoints as m where sroadid='4L4552'
group by m.sroadid 
"4L4552";"{"POINT(120.24588 31.50656)","POINT(120.245696625233 31.506692654087)","POINT(120.245513250465 31.506825308174)","POINT(120.245340073154 31.5069699268456)","POINT(120.245180036577 31.5071299634228)","POINT(120.24502 31.50729)"}"
但是一起执行就有问题：
select m.sroadid , array_accum(geoms) into  ch10.mprealroad
from ch10.mulitpoints as m 
group by m.sroadid select sroadid, array_accum  from ch10.mprealroad where sroadid='4L4552'"4L4552";"{"POINT(120.245513250465 31.506825308174)","POINT(120.245696625233 31.506692654087)","POINT(120.24588 31.50656)","POINT(120.245340073154 31.5069699268456)","POINT(120.24502 31.50729)","POINT(120.245180036577 31.5071299634228)"}"

Answer 4

看数据好像是想把点数据合并成线或者区域~~
PostGIS里面的操作吧，我猜的话PostGIS
里面也许有办法把点转成线，或者线拆分成点