<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">Hi,<br><br>Glad it worked for you.<br><br>Cheers,<br><br> Brent Wood<br><br>--- On <b>Thu, 12/27/12, tasneem dewaswala <i><tasneem.europe@gmail.com></i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: tasneem dewaswala <tasneem.europe@gmail.com><br>Subject: Re: [postgis-users] linestring using geography columns<br>To: pcreso@pcreso.com<br>Date: Thursday, December 27, 2012, 12:35 AM<br><br><div id="yiv862568524">Hii,<div><br></div><div>I got it successfully worked in C#</div><div><br></div><div>Thanks a lot for your help<br><br><div class="yiv862568524gmail_quote">On Wed, Dec 26, 2012 at 12:54 AM, <span dir="ltr"><<a rel="nofollow" ymailto="mailto:pcreso@pcreso.com" target="_blank" href="/mc/compose?to=pcreso@pcreso.com">pcreso@pcreso.com</a>></span>
wrote:<br>
<blockquote class="yiv862568524gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td style="font:inherit;" valign="top">Hi,<br><br>Answers inline in red below...<div class="yiv862568524im">
<br><br>--- On <b>Tue, 12/25/12, tasneem dewaswala <i><<a rel="nofollow" ymailto="mailto:tasneem.europe@gmail.com" target="_blank" href="/mc/compose?to=tasneem.europe@gmail.com">tasneem.europe@gmail.com</a>></i></b> wrote:<br></div><blockquote style="border-left:2px solid rgb(16,16,255);margin-left:5px;padding-left:5px;">
<br>From: tasneem dewaswala <<a rel="nofollow" ymailto="mailto:tasneem.europe@gmail.com" target="_blank" href="/mc/compose?to=tasneem.europe@gmail.com">tasneem.europe@gmail.com</a>><br>Subject: Re: [postgis-users] linestring using geography columns<br>To: <a rel="nofollow" ymailto="mailto:pcreso@pcreso.com" target="_blank" href="/mc/compose?to=pcreso@pcreso.com">pcreso@pcreso.com</a><br>
Date: Tuesday, December 25, 2012, 11:46 PM<br><br><div><div class="yiv862568524im">Hii,<div><br></div><div>thank you very much for your reply.</div><div>your example has given me a good idea.</div><div><br></div><div>But I have few questions,</div>
<div><br></div><div>in the following query are you creating a new table?</div>
</div><div><div class="yiv862568524im"><pre style="word-wrap:break-word;white-space:pre-wrap;"><font size="4">psql -d $DB -Atc "select distinct timestamp from points;" > times.dat</font></pre></div><div><br><font color="#ff0000">Nope, the ">" saves the query output as a file called times.dat</font><br>
<br><br></div><div class="yiv862568524im">also i dint understand how are you iterating through time</div></div>
<div><div class="yiv862568524im"><pre style="word-wrap:break-word;white-space:pre-wrap;"><font size="4">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/,$//'`</font></pre><pre style="word-wrap:break-word;white-space:pre-wrap;"><font size="4"><br></font></pre></div><pre style="word-wrap:break-word;white-space:pre-wrap;"><font size="4"><div class="yiv862568524im">is TIME a column or a datatype,<br><br></div><font color="#ff0000">neither, it is the name of a variable read from a file<br><br>While read TIME ; do<br> ....<br>done < times.dat<br><br>
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 <br></font><br></font><div class="yiv862568524im">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</div>
</pre>
<div><font color="#ff0000">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. </font><br>
<br><font color="#ff0000">Cheers,<br><br> Brent</font><br><br><br><br><br></div><div><div class="yiv862568524h5"><div>On Tue, Dec 25, 2012 at 7:23 AM, <span dir="ltr"><<a rel="nofollow" target="_blank" href="http://mc/compose?to=pcreso@pcreso.com">pcreso@pcreso.com</a>></span> wrote:<br>
<blockquote style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td style="font:inherit;" valign="top">Hi,<br><br>It is relatively easy to do, but you need some field to order the points in to create the linestring.<br><br>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.<br>
<br>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.<br>
<br>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
:-)<br><br>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. <br><br>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.<br>
<br>I assume this is what you were looking to do, feel free to get in touch if you have any questions.<br><br>Cheers,<br><br> Brent Wood<br><br><br><br><br>--- On <b>Tue, 12/25/12, tasneem dewaswala <i><<a rel="nofollow" target="_blank" href="http://mc/compose?to=tasneem.europe@gmail.com">tasneem.europe@gmail.com</a>></i></b> wrote:<br>
<blockquote style="border-left:2px solid rgb(16,16,255);margin-left:5px;padding-left:5px;"><br>From: tasneem dewaswala <<a rel="nofollow" target="_blank" href="http://mc/compose?to=tasneem.europe@gmail.com">tasneem.europe@gmail.com</a>><br>
Subject: [postgis-users] linestring using geography columns<br>
To: "PostGIS Users Discussion" <<a rel="nofollow" target="_blank" href="http://mc/compose?to=postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br>Date: Tuesday, December 25, 2012, 5:19 AM<br>
<br><div>Hello,<div><br></div>
<div>I have 3 columns in my table, with data as</div><div><br></div><div>Latitude Longitude Utc_time</div><div> </div><div><div>56.6474065 12.8533945 132733</div><div>
56.647401 12.8534145 132733</div></div><div><div>56.6473953333 12.8534343333 132733</div></div><div><div>56.6473893333 12.8534556667 132733</div></div><div>56.6473006667 12.8537761667 132737</div>
<div>56.647293 12.8538066667 132737</div><div>56.6472853333 12.8538373333 132737</div><div><br></div><div>I want to create a Geography linestring/multiline column of latitude/longitude values where time is same</div>
<div>for eg. time of first 4 rows is same, hence a line should be created between 1st row and 4th row.</div><div><br></div><div>any idea how can i do this, and a proper syntax is requested , since i am very new in creating a spatial table.</div>
</div><br>-----Inline Attachment Follows-----<br><br><div>_______________________________________________<br>postgis-users mailing list<br><a rel="nofollow" target="_blank" href="http://mc/compose?to=postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a rel="nofollow" target="_blank" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></div></blockquote></td></tr></tbody></table></blockquote>
</div>
<br></div></div></div>
</div></blockquote></td></tr></tbody></table></blockquote></div><br></div>
</div></blockquote></td></tr></table>