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

[Gery .]

Ok, I will set varchar(254) instead to text, and yes it was text. Please find the tmpline_table2shp.sh:

pgsql2shp -f tmpline -h localhost -u postgres -P **** opdb "SELECT * FROM public.tmpline"

I tried the same command line with another table and it worked well.

I created the tmpline table with:

=====
[postgres at localhost script]$ cat tmpline.sql 
DROP TABLE tmpline;
CREATE TABLE tmpline(
PROFILE VARCHAR(30),
COMMENTS TEXT
);
SELECT AddGeometryColumn('public', 'tmpline', 'geom', 4326, 'LINESTRING', 2);
=====

So, I don't know why I received this:
WARNING: Cannot determine spatial reference (empty table or unknown spatial ref). No prj file will be generated.
ERROR: Cannot determine geometry type (empty table).
Again, Nicolas's code I tried is as follows:

=====
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=#
=====
and why the "t" in the second line? could you explain me a bit the above code please?

Thanks,





----------------------------------------
> Date: Sun, 2 Oct 2011 13:15:00 -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
>
> On 10/02/2011 12:37 PM, Gery . wrote:
> >
> >
> > @Andy: Sorry, I forgot the error message in the last email. Here it is the new error message:
> >
>
> Yeah, Nicolas sql is much butter, forget mine.
>
>
> >
> > @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.
> Probably because the comments field is declared as text. Try varchar(245) instead. Its just warning you that anything over 254 characters will be truncated.
>
>
> > ERROR: Cannot determine geometry type (empty table).
>
> I'm not sure what the error is about. Would need to see tmpline_table2shp.sh to know more.
>
> -Andy
>
>