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

[David Finlayson]

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