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

[Hamish]

> > 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