[R-sig-Geo] [R-sig-DB] Raster data and MySQL

[Jeffrey Horner]

Toad 2000 wrote:
> Hi Folks,
> 
> Hope this isn't too far off topic + apologies for cross posting if you're on 
> both lists...
> 
> I'm in the lucky(?) position of setting up an archive of geophysical 
> (meteorological) data from scratch. R has been my working 
> language/environment for the last few years. I've had plenty of need for the 
> maptools + rgdal packages.  Ditto the RMySQL package but never inconjuction 
> with spatial analyses.  My worlds are about to collide.
> 
> The data come in two types: Station data (irregular grid, irregular times, 
> lots of missing values) and Model output (regular grid, regular times, no 
> missing data).  The data come in hour after hour, day after day.
> 
> Typical queries can be spatial (e.g. what did the rainfall over a given post 
> code area look like yesterday?), temporal (what were the houlry wind speeds 
> at a specific point for last month?), or both (what was the mean temperature 
> across the UK last year).
> 
> So how best to store the data to facilitate such queries?  This is where I 
> hope you can help! :)
> 
> My initial thoughts are to invoke the spatial capabilites of MySQL.  The 
> station data seem easy enough to store as POINT or MULTIPOINT and set up a 
> spatial index and another on the time stamp.  Spatial queries can be 
> performed using a WKB (Well Known Binary - new to me;) representation of the 
> region of interest.

Neither RMySQL nor RODBC support BLOB types or any of the Spatial types. 
BLOB (binary large object) types are useful for storing any binary data, 
including raster images. Also, some of the comments here:

http://dev.mysql.com/doc/refman/5.0/en/populating-spatial-columns.html

suggest that actually getting data into MySQL may be difficult.

What's really needed is an update to the R DBI spec to support prepared 
statements, placeholders, and of course BLOB types. I'm actually 
planning an R module which will use BLOB types, but not the way a DBI 
spec would, so I'd be happy to consider helping write some of this after 
I get my hands dirty with the MySQL client apis.

> 
> I'm stuck on the model data.  I could break it down into points and proceed 
> as above but this seems grossly inefficient.  I was hoping to find a RASTER 
> data type and store the grids in that.  Trouble is, I could be missing 
> something, that I can't seem to find anything like one in MySQL.  Don't know 
> about Postgre or SQL sever.  I'm open to suggestions.
> 
> Your hopefully, T
> 
> _________________________________________________________________
> 
> 7.5 today! http://join.msn.com/messenger/overview
> 
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB at stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db


-- 
Jeffrey Horner       Computer Systems Analyst         School of Medicine
615-322-8606         Department of Biostatistics   Vanderbilt University