Hi, Glad it worked for you.
Cheers, Brent Wood --- On Thu, 12/27/12, tasneem dewaswala <[email protected]> wrote: From: tasneem dewaswala <[email protected]> Subject: Re: [postgis-users] linestring using geography columns To: [email protected] Date: Thursday, December 27, 2012, 12:35 AM Hii, I got it successfully worked in C# Thanks a lot for your help On Wed, Dec 26, 2012 at 12:54 AM, <[email protected]> wrote: Hi, Answers inline in red below... --- On Tue, 12/25/12, tasneem dewaswala <[email protected]> wrote: From: tasneem dewaswala <[email protected]> Subject: Re: [postgis-users] linestring using geography columns To: [email protected] Date: Tuesday, December 25, 2012, 11:46 PM Hii, thank you very much for your reply.your example has given me a good idea. But I have few questions, in the following query are you creating a new table? psql -d $DB -Atc "select distinct timestamp from points;" > times.dat Nope, the ">" saves the query output as a file called times.dat also i dint understand how are you iterating through time while read TIME ; do LIST=`psql -d $DB -Atc "select lon, lat from points where timestamp = $TIME order by timestamp, id;" | \ tr "|" " " | tr "\n" "," | sed 's/,$//'` is TIME a column or a datatype, neither, it is the name of a variable read from a file While read TIME ; do .... done < times.dat This is where the file is iterated through - all the distinct times in the database - the do.done loop gets its input "<" from the file times.dat I would be using C# for much of my manipulations and javascript for visualization on google map, but i am not much experienced in scripts hence would prefer sql sql can be cumbersome to iterate through loops natively, that is perhaps something you could do in C#. Generate the list of distinct times with SQL & iterate through it in your code. In the script. a new db connection is required for every iteration. In C# you can open a connection/iterate/close connection which will have much less overhead. Cheers, Brent On Tue, Dec 25, 2012 at 7:23 AM, <[email protected]> wrote: Hi, It is relatively easy to do, but you need some field to order the points in to create the linestring. Of the three columns you provide for an example, you can select/group by UTC_time to identify the points to use for each line, but (by definition) the sequence of these points is indeterminate. If you add these points into the database table from a file, & the file sequence is the required order, then auto populating a series column as you insert the data will provide a column to order the records on. There are two approaches to doing this, either with a somewhat complex SQL generating a series of the points to create the linestrings, or via a script iterating through the time values. Which works best for you depends on whether you are more comfortable with SQL or scripting. I tend to script these things, much easier to debug :-) A procedural script breaks the process down into (relatively) simple steps, so can be easier to follow than an SQL, if you are not sure what steps are required. I've attached a script which does this with the points/times you provided, along with a map showing the points with the lines the script creates. I assume this is what you were looking to do, feel free to get in touch if you have any questions. Cheers, Brent Wood --- On Tue, 12/25/12, tasneem dewaswala <[email protected]> wrote: From: tasneem dewaswala <[email protected]> Subject: [postgis-users] linestring using geography columns To: "PostGIS Users Discussion" <[email protected]> Date: Tuesday, December 25, 2012, 5:19 AM Hello, I have 3 columns in my table, with data as Latitude Longitude Utc_time 56.6474065 12.8533945 132733 56.647401 12.8534145 13273356.6473953333 12.8534343333 13273356.6473893333 12.8534556667 13273356.6473006667 12.8537761667 132737 56.647293 12.8538066667 13273756.6472853333 12.8538373333 132737 I want to create a Geography linestring/multiline column of latitude/longitude values where time is same for eg. time of first 4 rows is same, hence a line should be created between 1st row and 4th row. any idea how can i do this, and a proper syntax is requested , since i am very new in creating a spatial table. -----Inline Attachment Follows----- _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
