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

[Andy Colson]

>> 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