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

[Andy Colson]

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