Re: [postgis-users] Converting a LineString to MultiPoint

[Mike Toews]

If you have PostGIS 1.5, then you can use ST_DumpPoints:
http://postgis.refractions.net/docs/ST_DumpPoints.html

E.g.:

SELECT gid, ST_AsText(ST_Collect(ST_AsText(geom)))
FROM (
  SELECT gid, (ST_DumpPoints(g.geom)).*
  FROM
(SELECT 1 as gid, 'LINESTRING (0 0, 0 3, 3 4)'::geometry AS geom
 UNION ALL
 SELECT 2 as gid, 'LINESTRING (1 1, 2 6, 7 7)'::geometry AS geom) AS g
  ) j
GROUP BY gid;

-Mike

On 17 June 2010 14:57, Andrea Peri 2007  wrote:
> Hi,
>
> I have a table of LineStrings, and need to create a table of MultiPoints,
> where every multipoint is using the same vertex of a LineString.
> There is a method to do this using only sql ?
>
> Thx,
>
> Andrea.
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Converting a LineString to MultiPoint

[Mike Toews]

Or, if you have version < 1.5, you could use a simple WKT hack to text
replace 'LINESTRING' with 'MULTIPOINT', since the rest is the same:

SELECT gid, ST_AsText(replace(ST_AsEWKT(geom), 'LINESTRING',
'MULTIPOINT')::geometry)
FROM
 (SELECT 1 as gid, 'LINESTRING (0 0, 0 3, 3 4)'::geometry AS geom
  UNION ALL
  SELECT 2 as gid, 'LINESTRING (1 1, 2 6, 7 7)'::geometry AS geom) AS g;

Again, this is a complete hack, and assumes you have all LINESTRING
types. I use ST_AsEWKT in case you have more than 3 dimensions and/or
an SRID.

-Mike

On 17 June 2010 16:36, Mike Toews  wrote:
> If you have PostGIS 1.5, then you can use ST_DumpPoints:
> http://postgis.refractions.net/docs/ST_DumpPoints.html
>
> E.g.:
>
> SELECT gid, ST_AsText(ST_Collect(ST_AsText(geom)))
> FROM (
>  SELECT gid, (ST_DumpPoints(g.geom)).*
>  FROM
>    (SELECT 1 as gid, 'LINESTRING (0 0, 0 3, 3 4)'::geometry AS geom
>     UNION ALL
>     SELECT 2 as gid, 'LINESTRING (1 1, 2 6, 7 7)'::geometry AS geom) AS g
>  ) j
> GROUP BY gid;
>
> -Mike
>
> On 17 June 2010 14:57, Andrea Peri 2007  wrote:
>> Hi,
>>
>> I have a table of LineStrings, and need to create a table of MultiPoints,
>> where every multipoint is using the same vertex of a LineString.
>> There is a method to do this using only sql ?
>>
>> Thx,
>>
>> Andrea.
>>
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Converting a LineString to MultiPoint

[Andrea Peri]

HI Mike,

it work very well.

So I reached my scope.

I used this sql:

INSERT INTO public. (id,idrtt,geom) (
select
c.id,
c.idrtt,
st_union(c.geom)
from (
select
a.id as id,
a.idrtt as idrtt,
(ST_DumpPoints(a.geom)).* as geom
from
public. as a
) as c
group by
c.id,
c.idrtt
);


Regards,

Andrea.

>If you have PostGIS 1.5, then you can use ST_DumpPoints:
>http://postgis.refractions.net/docs/ST_DumpPoints.html 
>
>
>E.g.:
>
>SELECT gid, ST_AsText(ST_Collect(ST_AsText(geom)))
>FROM (
>  SELECT gid, (ST_DumpPoints(g.geom)).*
>  FROM
>(SELECT 1 as gid, 'LINESTRING (0 0, 0 3, 3 4)'::geometry AS geom
> UNION ALL
> SELECT 2 as gid, 'LINESTRING (1 1, 2 6, 7 7)'::geometry AS geom) AS g
>  ) j
>GROUP BY gid;
>
>-Mike



-- 
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users