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

[Nicolas Ribot]

On 2 October 2011 19:44, Gery . <gamejihou at hotmail.com> wrote:

>
>
> BTW, this is how I created the "tmpline" table:
>
> =====
> DROP TABLE tmpline;
> CREATE TABLE tmpline(
> PROFILE VARCHAR(30),
> COMMENTS TEXT
> );
> SELECT AddGeometryColumn('public', 'tmpline', 'geom', 4326, 'LINESTRING',
> 2);
> =====
>
> and Nicolas the answer to your commentary about if the points are ordered
> in the CSV file, yes, they are ordered in order to create a straight line
> (or quasi-straight one) using longitude and latitude, and this is ordered by
> the ID column. Is it possible to create from scratch a table with
> correlative ID? I saw the ID serial type, is this the way?
>
> Thanks,
>
>
>
Yes, you can define a table with an SERIAL type, that provides autoincrement
feature.
If the copy operation guarantees that rows in the files are inserted
sequentially in the table, then adding a serial column may suffice to build
linestrings in the right order (others on the list may confirm that) :

After the "tmp" table is loaded with geometries, alter it to add a serial
column:

alter table tmp add column id serial;

It will fill the id column with incremented values.
Then create the table that will contain the linestrings:

DROP TABLE tmpline;
CREATE TABLE tmpline(
PROFILE VARCHAR(30),
COMMENTS TEXT
);
SELECT AddGeometryColumn('public', 'tmpline', 'geom', 4326, 'LINESTRING',
2);

Then fill it with linestrings:

 insert into tmpline
with t as (select * from tmp order by profile, id)
select profile, st_makeline(geom)
from t group by profile;
Check if linestrings are built correctly after this query:

select profile, astext(the_geom) from tmpline;
After that, you should be able to dump both tables to shapefile, as both
contain geometries.

Nicolas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111002/da178a25/attachment.html>