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

[Gery .]

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