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

Sun Oct 2 10:37:06 PDT 2011

@Andy: Sorry, I forgot the error message in the last email. Here it is the new error message:

=====
opdb=# select profile,
opdb-# (select ST_LineFromMultiPoint (ST_collect(geom))
opdb(# from tmp x2 where x2.profile = tmp.profile
opdb(# order by id
opdb(# )
opdb-# from tmp
opdb-# group by profile;
ERROR:  column "x2.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 4: order by id
                 ^
opdb=#
===== 

In this case, "tmp" replaces the "point_table" you wrote before. Btw, x2 should be a value in the "tmp" table? I created the "tmpline" table as follows:

=====
opdb=# \d tmpline 
            Table "public.tmpline"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 profile  | character varying(30) | 
 comments | text                  | 
 geom     | geometry              | 
Check constraints:
    "enforce_dims_geom" CHECK (st_ndims(geom) = 2)
    "enforce_geotype_geom" CHECK (geometrytype(geom) = 'LINESTRING'::text OR geom IS NULL)
    "enforce_srid_geom" CHECK (st_srid(geom) = 4326)
=====

Thanks again,

@Nicolas: thanks for your message, I used your code below and got no error messages:

=====
opdb=# insert into tmpline
with t as (select * from tmp order by profile, id)
select profile, st_makeline(geom)
from t group by profile;
INSERT 0 71
opdb=#
=====

but interesting, after converting the table to shape with pgsql2shp I got the following error:

=====
[postgres at localhost script]$ tmpline_table2shp.sh 
Preparing table for user query... Done.
Initializing... Warning: values of field 'comments' exceeding maximum dbf field width (254) will be truncated.
WARNING: Cannot determine spatial reference (empty table or unknown spatial ref). No prj file will be generated.
ERROR: Cannot determine geometry type (empty table).
[postgres at localhost script]$ 
=====

BTW, this is the info about both "tmp" and "tmpline", and in the latter I didn't get the GIST because I don't have the fields (e.g longitude and latitude as in the "tmp" point table), is that the reason of the above error perhaps?

Thank you guys for your support.

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