[postgis-users] linestring using geography columns

[pcreso at pcreso.com]

Hi,

Glad it worked for you.

Cheers,

   Brent Wood

--- On Thu, 12/27/12, tasneem dewaswala <tasneem.europe at gmail.com> wrote:

From: tasneem dewaswala <tasneem.europe at gmail.com>
Subject: Re: [postgis-users] linestring using geography columns
To: pcreso at pcreso.com
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,  <pcreso at pcreso.com> wrote:

Hi,

Answers inline in red below...


--- On Tue, 12/25/12, tasneem dewaswala <tasneem.europe at gmail.com> wrote:


From: tasneem dewaswala <tasneem.europe at gmail.com>
Subject: Re: [postgis-users] linestring using geography columns
To: pcreso at pcreso.com

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,  <pcreso at pcreso.com> 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 <tasneem.europe at gmail.com> wrote:



From: tasneem dewaswala <tasneem.europe at gmail.com>

Subject: [postgis-users] linestring using geography columns

To: "PostGIS Users Discussion" <postgis-users at lists.osgeo.org>
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
postgis-users at lists.osgeo.org


http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users






-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121226/71fc1d86/attachment.html>