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

[Roger Bivand]

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 at r-project.org
> 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 at nhh.no