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

[Gery .]

wow, thank you so much Nicolas, I'm officially impressed, it worked perfectly, so easy and direct and efficient, and the command line (asText) to see the coordinates of each profile, wow really great, doing this manually in arcgis for instance is painful, time-consuming and most of the time stupid, I'm really impressed about how this worked. Please find an image of the output.

You guys rock, this forum rocks,

Thank you again,



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://postgis.refractions.net/pipermail/postgis-users/attachments/20111002/da178a25/attachment.html>





    

 		 	   		  
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screenshot.png
Type: image/png
Size: 249343 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111002/fdc9ceda/attachment.png>