Re: [R-sig-Geo] Bug in writeOGR MSSQLSpatial driver?
Thanks Roger This didn't quite work row.names(polyunion90df) - integers as strings # one for each row Error in spChFIDs(SP, x) : lengths differ But this did, row.names(polyunion90df) - as.character(1:nrow(polyunion90df)) I suspect part of the problem was my use of an ID variable for the FID that includes numeric and character info. That flowed from using spCbind to match up some additional metadata to each feature. Many thanks Craig Craig Roger Bivand wrote On Thu, 23 May 2013, cmundy wrote: Hi All, Trying to run a well used script over the last few days has shown up an error when writing a SpatialPolygonDataFrame to SQL SERVER using writeOGR. This script worked 4 weeks ago, so I assume it is do with updates in the latest version of rgdal. This can be seen by comparing SVN revisions in the R-forge rgdal project: https://r-forge.r-project.org/scm/viewvc.php/pkg/R/ogr_write.R?root=rgdalr1=408r2=468 As you can see, writeOGR() takes the row.names of the object, coerces them to integer (because OGR only uses long integer FIDs), and if any are NA, should replace them with default integer. You'll see the typo in line 135. These are then inserted using the C code. https://r-forge.r-project.org/scm/viewvc.php/pkg/src/OGR_write.cpp?root=rgdalr1=394r2=468 I've committed the fix to R-forge (revision 473), but for now as a work-around that should avoid the bug, assign character row names that converts to integer without NA to: row.names(polyunion90df) # shows the input value row.names(polyunion90df) - integers as strings # one for each row row.names(polyunion90df) # check that it got assigned as.integer(row.names(polyunion90df)) # check that it isn't NA but integer Hope this helps, Roger The write attempt creates a new table in SQL SERVER, inserts a single row, then exits with the following error. writeOGR(polyunion90df, dsn=dsn, layer=lyrout90, driver=MSSQLSpatial, layer_options=c(SRID=28355)) Error in writeOGR(polyunion90df, dsn = dsnAbTrack_Analysis_SBY, layer = lyrout90, : Failed to create feature The single feature written to SQL Server appears normal, except the ogr_fid field contains the following value -2147483648. The write attempt seems to update the geometry_column table successfully. Using writeOGR with the ESRI shapefile driver successfully writes the SPDF to a shapefile, which imports normally to a GIS. writeOGR works normally if I run R 2.15.3 with rgdal 0.8-6. quizzing the SQL SERVER spatial feature returns the following ogrInfo returns the following ogrInfo(dsnAbTrack_Analysis_SBY, 'kud2012_test') Source: MSSQL:server=MyServer;database=MyDatabase;trusted_connection=yes, layer: kud2012_test Driver: MSSQLSpatial number of rows 1 Feature type: wkbMultiPolygon with 2 dimensions Extent: (608073.9 5543662) - (608322.9 5544055) CRS: +proj=utm +zone=55 +south +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs Number of fields: 19 name type length typeName 1 names4 0 String 2 diveid4 0 String 3dropnum0 10 Integer 4dvstart4 0 String 5 dvend4 0 String 6avgdpth2 0 Real 7avgtemp2 0 Real 8 catchblkg2 0 Real 9 catchglkg2 0 Real 10 blip_gps_cpue2 0 Real 11 blip_qd_cpue2 0 Real 12 glip_gps_cpue2 0 Real 13 glip_qd_cpue2 0 Real 14 duration2 0 Real 15 numpoly0 10 Integer 16 maxdist2 0 Real 17 parea2 0 Real 18 haphr2 0 Real 19 lmphr2 0 Real ogrFid info provides the following ogrFIDs(dsnAbTrack_Analysis_SBY, 'kud2012_test') [1] NA attr(,nf) [1] 1 attr(,i) [1] 1 sessionInfo() R version 3.0.1 (2013-05-16) Platform: x86_64-w64-mingw32/x64 (64-bit) locale: [1] LC_COLLATE=English_Australia.1252 [2] LC_CTYPE=English_Australia.1252 [3] LC_MONETARY=English_Australia.1252 [4] LC_NUMERIC=C [5] LC_TIME=English_Australia.1252 attached base packages: [1] stats graphics grDevices utils datasets methods [7] base loaded via a namespace (and not attached): [1] tools_3.0.1 library(rgdal) Loading required package: sp rgdal: version: 0.8-9, (SVN revision 470) Geospatial Data Abstraction Library extensions to R successfully loaded Loaded GDAL runtime: GDAL 1.9.2, released 2012/10/08 Path to GDAL shared files: C:/Program Files/R/R-3.0.1/library/rgdal/gdal GDAL does not use iconv for recoding strings. Loaded PROJ.4 runtime: Rel. 4.7.1, 23 September 2009, [PJ_VERSION: 470] Path to PROJ.4 shared files: C:/Program Files/R/R-3.0.1/library/rgdal/proj Hoping the developers can help? Thanks Craig -- View this message in context: http://r-sig-geo.2731867.n2.nabble.com/Bug-in-writeOGR-MSSQLSpatial-driver-tp7583633
Re: [R-sig-Geo] Help converting matlab raster to R raster brick
Hi Mike, Needed to flip the y axis to get the orientation right. Levelplot() show images that look like something I expected to see, and in the right part of the world - all good. I will need to check the x,y resolution of the original matlab file, but it is in the right ballpark. Thanks for the tip. test - readMat(CraigMundy.mat) x - brick(test$data[[2]], xmn = min(test$data[[4]]), xmx = max(test$data[[4]]), ymn = min(test$data[[3]]), ymx = max(test$data[[3]])) x - flip(x, direction=y) res(x) [1] 0.04100 0.03528 extent(x) class : Extent xmin: 146.599 xmax: 148.321 ymin: -44.3339 ymax: -42.5699 Michael Sumner-2 wrote Hi Craig, see the array method for ?brick: brick(x, xmn=0, xmx=1, ymn=0, ymx=1, crs=NA So, something like x - brick(test$data[[2], xmn = min(test$data[[5]]), ymx = max(test$data[[5]]), ymn = min(test$data[[4]]), ymx = max(test$data[[4]])) You might need to mess around to get the x/y ranges right, and figure out if you need to transpose or do other reorientations. This assumes the lon/lat vectors are regular so you should check that. Cheers, Mike On Thu, Apr 4, 2013 at 9:00 AM, cmundy lt; Craig.Mundy@.edu gt; wrote: Hi All, I have been provided with a .mat file containing a time series of Sea Surface Temperature data (50 x 42 cells with 92 time layers). It was a Struct object in Matlab. I can happily import the file in to R using R.matlab, creating a list as follows. My question is how then to convert this to a raster brick? Thanks Craig library(R.matlab) test - readMat(CraigMundy.mat) str(test) List of 2 $ data :List of 9 ..$ : num [1, 1:92] 734138 734139 734140 734141 734142 ... ..$ : num [1:50, 1:42, 1:92] 14.4 14.5 15.2 15.3 14.8 ... ..$ : num [1:50, 1] -44.3 -44.3 -44.3 -44.2 -44.2 ... ..$ : num [1, 1:42] 147 147 147 147 147 ... ..$ : num [1, 1] 101 ..$ : chr [1, 1] SST: 3 Day Composite ..$ : num [1, 1] 0 ..$ : num [1, 1] 34.5 ..$ : num [1, 1] 0.44 ..- attr(*, dim)= int [1:3] 9 1 1 ..- attr(*, dimnames)=List of 3 .. ..$ : chr [1:9] Time Values Lat Long ... .. ..$ : NULL .. ..$ : NULL $ times: chr [1:92, 1] 31-Dec-2009 01-Jan-2010 02-Jan-2010 03-Jan-2010 ... - attr(*, header)=List of 3 ..$ description: chr MATLAB 5.0 MAT-file, Platform: PCWIN64, Created on: Thu Apr 04 11:29:53 2013 ..$ version: chr 5 ..$ endian : chr little version _ platform x86_64-w64-mingw32 arch x86_64 os mingw32 system x86_64, mingw32 status major 3 minor 0.0 year 2013 month 04 day03 svn rev62481 language R version.string R version 3.0.0 (2013-04-03) nickname Masked Marvel -- View this message in context: http://r-sig-geo.2731867.n2.nabble.com/Help-converting-matlab-raster-to-R-raster-brick-tp7583204.html Sent from the R-sig-geo mailing list archive at Nabble.com. ___ R-sig-Geo mailing list R-sig-Geo@ https://stat.ethz.ch/mailman/listinfo/r-sig-geo -- Michael Sumner Hobart, Australia e-mail: mdsumner@ [[alternative HTML version deleted]] ___ R-sig-Geo mailing list R-sig-Geo@ https://stat.ethz.ch/mailman/listinfo/r-sig-geo -- View this message in context: http://r-sig-geo.2731867.n2.nabble.com/Help-converting-matlab-raster-to-R-raster-brick-tp7583204p7583222.html Sent from the R-sig-geo mailing list archive at Nabble.com. ___ R-sig-Geo mailing list R-sig-Geo@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Re: [R-sig-Geo] rgdal and MSSQL Server geometries
Hi Shannon, I can recreate your error message when I set an incorrect dsn. I've pasted in your line of code to read from SQL Server. Can you replace the single quote with double quotes and re try as is. check your server and database name. readOGR(dsn='MSSQL:server=CPU\\CPU_SQL2012EXP;database=Deer;uid=myUser;trusted_connection=true', layer='CollarPoly') From the line provided, I gather your computer name is CPU and the SQL server instance is called CPU_SQL2012EXP. Can you confirm these two names and the database name are correct. Regards Craig -- View this message in context: http://r-sig-geo.2731867.n2.nabble.com/rgdal-and-MSSQL-Server-geometries-tp7583193p7583220.html Sent from the R-sig-geo mailing list archive at Nabble.com. ___ R-sig-Geo mailing list R-sig-Geo@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Re: [R-sig-Geo] rgdal and MSSQL Server geometries
Hi Shannon,
The following syntax has worked for me for the past year.
projstring - CRS('+init=epsg:28355')
##Establish the dsn
# Note: use the odbc tool in Windows to create a dsn for your SQL Server
database beforehand
myMSSQLdsn -
c(MSSQL:server=mysqlservername;database=mydatabase;trusted_connection=yes)
#Confirm connection is working
ogrListLayers(myMSSQLdsn)
#Reading sp object classes from SQL SERVER
lyr - c(mssqlserverTablename)
spdf -readOGR(dsn=myMSSQLdsn, layer=lyr,p4s=CRSargs(projstring))
#Writing sp object classes to SQL SERVER
lyrout= c(NewMSSQLSpatialTableName)
writeOGR(spdfname, dsn=myMSSQLdsn, layer=lyrout, driver=MSSQLSpatial,
layer_options=c(SRID=28355))
If you haven't created a dsn, this could be a big part of your problem.
Craig
--
View this message in context:
http://r-sig-geo.2731867.n2.nabble.com/rgdal-and-MSSQL-Server-geometries-tp7583193p7583203.html
Sent from the R-sig-geo mailing list archive at Nabble.com.
___
R-sig-Geo mailing list
R-sig-Geo@r-project.org
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
[R-sig-Geo] Help converting matlab raster to R raster brick
Hi All, I have been provided with a .mat file containing a time series of Sea Surface Temperature data (50 x 42 cells with 92 time layers). It was a Struct object in Matlab. I can happily import the file in to R using R.matlab, creating a list as follows. My question is how then to convert this to a raster brick? Thanks Craig library(R.matlab) test - readMat(CraigMundy.mat) str(test) List of 2 $ data :List of 9 ..$ : num [1, 1:92] 734138 734139 734140 734141 734142 ... ..$ : num [1:50, 1:42, 1:92] 14.4 14.5 15.2 15.3 14.8 ... ..$ : num [1:50, 1] -44.3 -44.3 -44.3 -44.2 -44.2 ... ..$ : num [1, 1:42] 147 147 147 147 147 ... ..$ : num [1, 1] 101 ..$ : chr [1, 1] SST: 3 Day Composite ..$ : num [1, 1] 0 ..$ : num [1, 1] 34.5 ..$ : num [1, 1] 0.44 ..- attr(*, dim)= int [1:3] 9 1 1 ..- attr(*, dimnames)=List of 3 .. ..$ : chr [1:9] Time Values Lat Long ... .. ..$ : NULL .. ..$ : NULL $ times: chr [1:92, 1] 31-Dec-2009 01-Jan-2010 02-Jan-2010 03-Jan-2010 ... - attr(*, header)=List of 3 ..$ description: chr MATLAB 5.0 MAT-file, Platform: PCWIN64, Created on: Thu Apr 04 11:29:53 2013 ..$ version: chr 5 ..$ endian : chr little version _ platform x86_64-w64-mingw32 arch x86_64 os mingw32 system x86_64, mingw32 status major 3 minor 0.0 year 2013 month 04 day03 svn rev62481 language R version.string R version 3.0.0 (2013-04-03) nickname Masked Marvel -- View this message in context: http://r-sig-geo.2731867.n2.nabble.com/Help-converting-matlab-raster-to-R-raster-brick-tp7583204.html Sent from the R-sig-geo mailing list archive at Nabble.com. ___ R-sig-Geo mailing list R-sig-Geo@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Re: [R-sig-Geo] rgdal and MSSQL Server geometries
Hi Shannon, The other requirement to read tables from MS SQL Server is that details of the table with geometry columns must be registered in a table called geometry_columns in your database. If your database doesn't have this table, do the following; 1) write a small point or polygon object back to SQL Server using writeOGR. 2) This process will create two additional tables, the geometry_columns table and a spatial_ref_sys table. 3) open the geometry_columns table in SQL Server, and create a new record with the details of the table you want to read into R, following the pattern of the record created by the write process. 4) try reading the table again I'm out of the office for a day or so, but happy to help work this out. I use rgdal to exchange spatial data between R and SQL Server on a weekly basis, so can at least confirm that it works very nicely. Craig -- View this message in context: http://r-sig-geo.2731867.n2.nabble.com/rgdal-and-MSSQL-Server-geometries-tp7583193p7583209.html Sent from the R-sig-geo mailing list archive at Nabble.com. ___ R-sig-Geo mailing list R-sig-Geo@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-geo
[R-sig-Geo] Building RGDAL to enable access to new GDAL OGR drivers
Hi all, On the theme of building RGDAL to take advantage of new(ish) OGR drivers available in GDAL, and, heeding Rogers comments on this topic in recent posts, I am looking for help with an updated procedure (recipe) for compiling RGDAL to take advantage of specific GDAL OGR drivers (mssqspatial, wfs) on Windows 7 platforms. At a recent GIS/Fisheries conference there was an interest from several attendees on a more seemless integration of R with spatial RDBMS and/or web portals for spatial data (e.g. as for postgre/postgis). We were all rather daunted at the prospect of achieving this on Windows and my clumsy attempts to date have not yielded success (although a scan of blogs and posts, it seems less problematic on MacOS). The instructions/guides to compile RGDAL from source for MS windows to link to the GDAL driver libraries are now quite dated, and a more up to date guide would help us access drivers of interest that are not part of the standard drivers included in RGDAL. One of the original authors of some of those instructions was equally daunted by this task. Some questions as a start; 1) has anyone achieved this on Win7 recently? 2) how important is it to compile everything from source: i.e. R, rgdal, gdal - or can standard binaries be used for some components 3) is there a description anywhere of which Win7 system enviornmental variables for GDAL are required by RGDAL Any assistance greatly appreciated. Regards Craig P.s. Funding could be made available if there is interest to develop an updated set of instructions for general use. -- View this message in context: http://r-sig-geo.2731867.n2.nabble.com/Building-RGDAL-to-enable-access-to-new-GDAL-OGR-drivers-tp6762759p6762759.html Sent from the R-sig-geo mailing list archive at Nabble.com. ___ R-sig-Geo mailing list R-sig-Geo@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-geo
