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. 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
Raster data and MySQL
5 messages · Toad 2000, Roger Bivand, Jeffrey Horner +1 more
On Wed, 19 Apr 2006, 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! :)
Could you have a look at aRT? It has an R component, a database (typically MySQL) component, and uses Terralib as glue: http://www.est.ufpr.br/art/ I think you can use RMySQL directly for non-spatial tasks too. Terralib has well-developed spatial data types.
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. 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-Geo mailing list R-sig-Geo at stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Roger Bivand Economic Geography Section, Department of Economics, Norwegian School of Economics and Business Administration, Helleveien 30, N-5045 Bergen, Norway. voice: +47 55 95 93 55; fax +47 55 95 95 43 e-mail: Roger.Bivand at nhh.no
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
Hello, thanks for all the help, BTW. It seems that bbox coordinates in sp are tested for is.na, but not is.finite. (The only finiteness test I see in sp is in spDistsN1.) Is there a good reason for this? I can see that Inf/-Inf has meaning under some scenarios, but perhaps they should not be allowed by default? Cheers, Mike.
library(sp)
pts <- cbind(c(147, 148, 149), c(-42, -42, -44))
pts.na <- pts
pts.na[2,1] <- NA # or NaN
pts.inf <- pts
pts.inf[2,] <- Inf
SpatialPoints(pts, CRS("+proj=longlat"))
SpatialPoints(pts.na) #doesn't work,
SpatialPoints(pts.inf) # does work is.na(Inf) is FALSE, but plot
doesn't like it
sessionInfo()
R version 2.2.1, 2005-12-20, i386-pc-mingw32
attached base packages:
[1] "methods" "stats" "graphics" "grDevices" "utils" "datasets"
[7] "base"
other attached packages:
sp
"0.8-14"
On Thu, 20 Apr 2006, Michael Sumner wrote:
Hello, thanks for all the help, BTW. It seems that bbox coordinates in sp are tested for is.na, but not is.finite. (The only finiteness test I see in sp is in spDistsN1.) Is there a good reason for this? I can see that Inf/-Inf has meaning under some scenarios, but perhaps they should not be allowed by default?
I agree that Inf/-Inf should not be allowed as coordinate values, in fact under any circumstances - any Inf or other markers ought to be in the attributes. We'll see if any transform/project problems arise, but these ought to be handled by other means. I've committed a modification to CVS to check bounding box values, and to prevent the Spatial object from being created if Inf/-Inf are present, this will be in the next release. Thanks, Roger
Cheers, Mike.
library(sp)
pts <- cbind(c(147, 148, 149), c(-42, -42, -44))
pts.na <- pts
pts.na[2,1] <- NA # or NaN
pts.inf <- pts
pts.inf[2,] <- Inf
SpatialPoints(pts, CRS("+proj=longlat"))
SpatialPoints(pts.na) #doesn't work,
SpatialPoints(pts.inf) # does work is.na(Inf) is FALSE, but plot
doesn't like it
sessionInfo()
R version 2.2.1, 2005-12-20, i386-pc-mingw32
attached base packages:
[1] "methods" "stats" "graphics" "grDevices" "utils" "datasets"
[7] "base"
other attached packages:
sp
"0.8-14"
_______________________________________________ R-sig-Geo mailing list R-sig-Geo at stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Roger Bivand Economic Geography Section, Department of Economics, Norwegian School of Economics and Business Administration, Helleveien 30, N-5045 Bergen, Norway. voice: +47 55 95 93 55; fax +47 55 95 95 43 e-mail: Roger.Bivand at nhh.no