[postgis-users] csv table to points and then create line based on same name

[Nicolas Ribot]

Hi,

If the points are ordered in the CSV file, you could add a column with an
autoincremented ID value before loading it into Postgis (with Excel or Awk).
Then order the table by this ID and group by profile to create linestrings.
The order of points in the CSV file will be kept to generate linestrings:

insert into lines
with t as (select * from tmp order by profile, id)
select profile, st_makeline(the_geom))  from t group by profile;

Nicolas

On 2 October 2011 15:44, Gery . <gamejihou at hotmail.com> wrote:

>
> Hello Andy,
>
> Thanks for your answer, I updated my table, now it looks like:
>
> ====
>  id  | profile  | longitude | latitude |
> geom
>
> -----+----------+-----------+----------+----------------------------------------------------
>    0 | HH00-23  |  -80.2835 | -8.05167 |
> 0101000020E6100000A01A2FDD241254C05F5E807D741A20C0
>    1 | HH00-23  |   -80.633 | -8.20033 |
> 0101000020E61000008D976E12832854C0BBD05CA7916620C0
>    2 | HH00-22  |  -80.6027 |  -8.2655 |
> 0101000020E6100000265305A3922654C00E2DB29DEF8720C0
>    3 | HH00-22  |  -80.2018 |   -8.094 |
> 0101000020E61000004D158C4AEA0C54C0E3A59BC4203020C0
> ...
> ...etc
> ====
>
> I tried your code below but didn't work, but I think it is getting closer,
> how could I update it with this new ID field in order of getting what I
> want?
>
>
>
> > Date: Sun, 2 Oct 2011 07:51:02 -0500
> > From: andy at squeakycode.net
> > To: postgis-users at postgis.refractions.net
> > CC: gamejihou at hotmail.com
> > Subject: Re: [postgis-users] csv table to points and then create line
> based on        same name
> >
> > On 10/02/2011 06:41 AM, Gery . wrote:
> > >
> > > Hello,
> > >
> > > I'm new around here and after working a while with postgis/postgresql,
> I'd like to get something that up to know is advanced for me. Please don't
> get mad at me if this is a stupid question =)
> > >
> > > I want to create a line table based on a point table, which it is
> composed of rows with equal names, this is an example of this csv table:
> > >
> > > ===
> > > PROFILE,LONGITUDE,LATITUDE
> > > HH00-23,-80.2835,-8.05167
> > > HH00-23,-80.633,-8.20033
> > > HH00-22,-80.6027,-8.2655
> > > HH00-22,-80.2018,-8.094
> > > ...
> > > ...
> > > ===
> > >
> > > I loaded this csv table into my database without problems, so this is
> how it looks like:
> > >
> > > ===
> > > profile | longitude | latitude | geom
> > >
> ----------+-----------+----------+----------------------------------------------------
> > > HH00-23 | -80.2835 | -8.05167 |
> 0101000020E6100000A01A2FDD241254C05F5E807D741A20C0
> > > HH00-23 | -80.633 | -8.20033 |
> 0101000020E61000008D976E12832854C0BBD05CA7916620C0
> > > HH00-22 | -80.6027 | -8.2655 |
> 0101000020E6100000265305A3922654C00E2DB29DEF8720C0
> > > HH00-22 | -80.2018 | -8.094 |
> 0101000020E61000004D158C4AEA0C54C0E3A59BC4203020C0
> > > ...
> > > ...
> > > ===
> > >
> > > now comes the part I don't know how to solve it, I want to get a table
> like this:
> > >
> > > ===
> > > profile | comments | geom
> > > -------------------------------------------------------------
> > > HH00-23 | some stuff | "HERE THE GEOMETRY SHOULD BE A LINE!"
> > > HH00-22 | some stuff | "HERE THE GEOMETRY SHOULD BE A LINE!"
> > > ...
> > > ...
> > > ===
> > >
> > > the case is that I have these points (from the csv table) in order, so,
> a straight line should be build after connecting the points. Here I showed
> just two pairs of points in each case, but I have also in that table 6 to 10
> points with the same profile name. I did this in arcgis manually and it is
> painful, I think that postgis is definitively more practice.
> > >
> > > Any hint is very welcome,
> > >
> > > Gery
> >
> > One little problem I can see is the order of the points will be
> undefined.  For example
> >
> > HH00-23,-80,-8
> > HH00-23,-81,-8
> > HH00-23,-80,-7
> > HH00-23,-81.-7
> >
> > after you dump theses into a table, then turn around and select them, if
> you dont put an order by on a select statement, then PG can return them in
> any order it wants.  And "order by profile" wont really help, again, those 4
> points can be returned in any order.  Do you have any method of identifying
> the order of the points?
> >
> > I assume you want to create a new table for the lines?
> >
> > You can try something like this, not sure how well it'll work:
> >
> > create table lines(uid serial primary key, profile text);
> > select AddGeometryColumn('lines', 'the_geom', -1, 'LINESTRING', 2);
> >
> > insert into lines(profile, the_geom)
> >    select profile, ST_LineFromMultiPoint(ST_collect(the_geom))
> >    from point_table
> >    group by profile;
> >
> >
> > This is, of course, untested.
> >
> > -Andy
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111002/ff0093b9/attachment.html>