[GRASSLIST:1034] Re: Populating fields of one table based on SQL query from another ta ble

[Patton, Eric]

David, 

That script may come in handy, thanks for pointing it out. It's funny,
around here, Julian day/time is a much-abused term that is used to refer
only to the particular day of the year in a 365-day sequence. I've given up
trying to explain to people that the true Julian day is the number of days
since 4700 B.P because the misunderstanding has become so entrenched it's
easier just to accept that March 12th of every year is day 071...  ;)

~ Eric. 

-----Original Message-----
From: David Finlayson
To: Hamish
Cc: Patton, Eric; GRASSLIST at baylor.edu
Sent: 5/5/2006 12:51 PM
Subject: Re: [GRASSLIST:1002] Re: Populating fields of one table based on
SQL query from another ta ble

I'm proud of a little script I wrote that can extract dates from
tabular data and convert them into alternative formats. I wrote it to
work a lot like sed, you pipe tabular data into the filter and it
converts the date into another format on stdout. Works great for time
series stuff.

I find it really useful to convert time stamps in conventional date
formats into something called a Julian day. This is a monotonically
increasing day number used by astronomers that starts at around -4700
B.C. and has been increasing ever since. It simplifies date math to
addition and subtraction. It has the advantage over day-of-year
schemes in that there is a UNIQUE number for each day, so you can
still figure out if the data were collected on a Monday in spring (for
example).

If you are interested, the program is called redate.py and can be found
here:

http://david.p.finlayson.googlepages.com/gisscripts

On 5/5/06, Hamish <hamish_nospam at yahoo.com> wrote:
> > > I'm trying to build an SQL query where the DGPS northings and
> > > eastings from a table of ship navigation gets written to a second
> > > table of sample locations only where the two tables have matching
> > > timestamps. So I basically have timestamps for my samples, but no
> > > positions. I'm unsure of the correct sql syntax (using Sqlite):
> > >
> > > Here's what the query should do:
> > >
> > > "select * from Easting and Northing in Ship_Navigation WHERE
> > > Timestamp == Timestamp in Station_Samples." | db.execute
> > >
> > > Is this type of query possible?
> >
> > Yes that is possible. The trick is making sure the two time stamps
are
> > EXACTLY the same. In mysql it would look like this (I think):
> >
> > SELECT table1.sampleid, table1.easting, table1.northing,
> > table2.timestamp FROM table1, table2
> > WHERE table1.timestamp == table2.timestamp
> > ORDER BY table2.timestamp ASC;
>
>
> fyi I've got a few Matlab scripts for lining up and interpolating
> positions from a GPS log & a recording instrument. Basic idea is the
> recording instrument is taking a reading every 10 sec but the GPS only
> every 30. I want a x,y column with my sensor data so figure out a
> position for each record, then filter & calc a R:G:B column, then
import
> with v.in.ascii for snail-trail plotting & interpolation. It works
> pretty well as the ship's movements are pretty steady vs a 2nd order
> fit. Currently they are task specific but if I get the funds to do
more
> with that data I can revisit them & put together a more general
script.
> I can share the basic idea though if you are interested.
>
>
>
> Hamish
>


--
David Finlayson