Raster data and MySQL
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