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

[Puneet Kishor]

On Oct 2, 2011, at 9:36 AM, Nicolas Ribot wrote:

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


Very nicely done. Elegant and effective.




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