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

[Gery .]

BTW, this is how I created the "tmpline" table:

=====
DROP TABLE tmpline;
CREATE TABLE tmpline(
PROFILE VARCHAR(30),
COMMENTS TEXT
);
SELECT AddGeometryColumn('public', 'tmpline', 'geom', 4326, 'LINESTRING', 2);
=====

and Nicolas the answer to your commentary about if the points are ordered in the CSV file, yes, they are ordered in order to create a straight line (or quasi-straight one) using longitude and latitude, and this is ordered by the ID column. Is it possible to create from scratch a table with correlative ID? I saw the ID serial type, is this the way?

Thanks,


----------------------------------------
> Date: Sun, 2 Oct 2011 09:42:05 -0500
> From: andy at squeakycode.net
> To: gamejihou at hotmail.com
> CC: postgis-users at postgis.refractions.net
> Subject: Re: [postgis-users] csv table to points and then create line based on same name
>
> >> 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
> >
>
> On 10/02/2011 08:44 AM, Gery . 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?
> >
> >
> >
>
> What error did you get?
>
> Hum...
>
>
> select profile,
> ( select ST_LineFromMultiPoint(ST_collect(the_geom))
> from point_table x2 where x2.profile = point_table.profile
> order by id
> )
> from point_table
> group by profile;
>
>
> again, totally untested. Error messages sure make it simpler to fix.
>
> -Andy