0

I am trying to write geodata to a Microsoft SQLServer 2017 using rgdal.

dsn = 'MSSQL:server=XXX;database=XXX;trusted_connection=yes'

data=data.frame(lat=c(52.52437), lon=c(13.41053), value=c("v"))
colnames(data) <- c("lat", "lon", "value")
data_coords = data
coordinates(data_coords) <- ~ lat + lon
data_sp = SpatialPointsDataFrame(data_coords, data)

writeOGR(data_sp, dsn=dsn, driver="MSSQLSpatial", layer="NewMSSQLSpatialTableName", layer_options=c("SRID=28355"), verbose=TRUE)

However, this code fails with:

> writeOGR(data_sp, dsn=dsn, driver="MSSQLSpatial", layer="NewMSSQLSpatialTableName", layer_options=c("SRID=28355"), verbose=TRUE)
Object initially classed as: wkbPoint
Error in writeOGR(data_sp, dsn = dsn, driver = "MSSQLSpatial", layer = "NewMSSQLSpatialTableName",  : 
  Layer creation failed

I have permissions to create tables in the database. Am I missing additional permissions or did I do something else wrong?

Versions:

> library(rgdal)
rgdal: version: 1.2-16, (SVN revision 701)
 Geospatial Data Abstraction Library extensions to R successfully loaded
 Loaded GDAL runtime: GDAL 2.2.0, released 2017/04/28
 Path to GDAL shared files: C:/Users/User/Software/R/R-3.4.3/library/rgdal/gdal
 GDAL binary built with GEOS: TRUE 
 Loaded PROJ.4 runtime: Rel. 4.9.3, 15 August 2016, [PJ_VERSION: 493]
 Path to PROJ.4 shared files: C:/Users/User/Software/R/R-3.4.3/library/rgdal/proj
 Linking to sp version: 1.2-7 
  • I'd try a different data set, more than one point at least, or an established data set like wrld_simpl in maptools, with CRS set already. It's a bit painful, but I recommend using GDAL from the command line to get this prototyped first, just in case it's an rgdal nuance. Also, you might try sf::st_write with its format, it's more modern and has a shorter distance to GDAL itself. – mdsumner Mar 8 '18 at 14:16
  • @mdsummer Writing wrdl_simple using st_write sadly fails: no applicable method for 'st_write' applied to an object of class "c('SpatialPolygonsDataFrame', 'SpatialPolygons', 'Spatial')" – Dennis Mar 8 '18 at 14:41
  • st_write uses the new spatial classes from the sf package, so some conversion is necessary from SpatialPolygons* objects. – Spacedman Mar 8 '18 at 16:22
  • I was assuming you would continue to use writeOGR as per your q, the st_write was after "Also". It is painfully fragmented, but worth trying those three alternatives (at least). – mdsumner Mar 12 '18 at 6:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.