[R-sig-Geo] Bug in writeOGR MSSQLSpatial driver?

cmundy Craig.Mundy at utas.edu.au
Fri May 24 07:34:57 CEST 2013


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=rgdal&r1=408&r2=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=rgdal&r1=394&r2=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          names    4      0   String
>> 2         diveid    4      0   String
>> 3        dropnum    0     10  Integer
>> 4        dvstart    4      0   String
>> 5          dvend    4      0   String
>> 6        avgdpth    2      0     Real
>> 7        avgtemp    2      0     Real
>> 8      catchblkg    2      0     Real
>> 9      catchglkg    2      0     Real
>> 10 blip_gps_cpue    2      0     Real
>> 11  blip_qd_cpue    2      0     Real
>> 12 glip_gps_cpue    2      0     Real
>> 13  glip_qd_cpue    2      0     Real
>> 14      duration    2      0     Real
>> 15       numpoly    0     10  Integer
>> 16       maxdist    2      0     Real
>> 17         parea    2      0     Real
>> 18         haphr    2      0     Real
>> 19         lmphr    2      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.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
>>
> 
> -- 
> Roger Bivand
> Department of Economics, NHH Norwegian School of Economics,
> Helleveien 30, N-5045 Bergen, Norway.
> voice: +47 55 95 93 55; fax +47 55 95 95 43
> e-mail: 

> Roger.Bivand@

> 
> _______________________________________________
> 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/Bug-in-writeOGR-MSSQLSpatial-driver-tp7583633p7583649.html
Sent from the R-sig-geo mailing list archive at Nabble.com.



More information about the R-sig-Geo mailing list