writeOGR layer creation failed - MySQL

Question

I am trying to load multi polygon boundaries to mysql. Here is what I have tried so far,

Installed gdal with mysql driver. Checked with ogrDrivers() in R, it returns MySQL in the list of drivers.

db <- 'testdb'
user <- 'testuser'
pwd <- '****'
host <- '127.0.0.1'

DSN = paste0('MySQL:',db ,',user=', user, ',host=', host, ',password=', pwd)

data_2013 <- readOGR(dsn = "/Path/to/shapefile", layer = "testinglayer")

writeOGR(data_2013, dsn=DSN, driver='MySQL',layer='testinglayer', layer_options='ENGINE=Aria', overwrite_layer = TRUE)

But I am getting this error,

Error in writeOGR(data_2013, dsn = DSN, driver = "MySQL", layer = "testinglayer",  : 
  Layer creation failed

Or is there any other way I can load spatial polygon data from R to MySQL?

EDIT : As requested,

> ogrDrivers()$name
 [1] "AeronavFAA"     "AmigoCloud"     "ARCGEN"         "AVCBin"         "AVCE00"        
 [6] "BNA"            "CAD"            "Carto"          "Cloudant"       "CouchDB"       
[11] "CSV"            "CSW"            "DGN"            "DXF"            "EDIGEO"        
[16] "ElasticSearch"  "ESRI Shapefile" "Geoconcept"     "GeoJSON"        "Geomedia"      
[21] "GeoRSS"         "GFT"            "GML"            "GMLAS"          "GPKG"          
[26] "GPSBabel"       "GPSTrackMaker"  "GPX"            "HTF"            "HTTP"          
[31] "Idrisi"         "Interlis 1"     "Interlis 2"     "JML"            "JP2OpenJPEG"   
[36] "JPEG2000"       "KML"            "LIBKML"         "MapInfo File"   "Memory"        
[41] "MSSQLSpatial"   "MySQL"          "NAS"            "netCDF"         "ODBC"          
[46] "ODS"            "OGR_DODS"       "OGR_GMT"        "OGR_PDS"        "OGR_SDTS"      
[51] "OGR_VRT"        "OpenAir"        "OpenFileGDB"    "OSM"            "PCIDSK"        
[56] "PGDUMP"         "PGeo"           "PLSCENES"       "REC"            "S57"           
[61] "SEGUKOOA"       "SEGY"           "Selafin"        "SQLite"         "SUA"           
[66] "SVG"            "SXF"            "TIGER"          "UK .NTF"        "VDV"           
[71] "VFK"            "Walk"           "WAsP"           "WFS"            "XLS"           
[76] "XLSX"           "XPlane"

What if you change `MySQL:` to `MySQL=` in the DNS creation? — SeGa, May 28 '18 at 05:55

SeGa · Answer 1 · 2018-05-28T15:59:37.010

1

Looking at this link, the arguments must be comma separated. So maybe this would work:

DSN = paste0('MYSQL:dbname=',db ,', user=', user, ', host=', host, ', password=', pwd, ', port=', port)

Or have you tried with the sf package?

DSN = paste0('MYSQL:dbname=',db ,' user=', user, ' host=', host, ' port=', port, ' password=', pwd)

library(sf)
data(meuse)
st_write(st_as_sf(meuse, coords = c("x","y")), dsn = DSN,  layer = "testinglayer")

Or changing host <- '127.0.0.1' to host <- "localhost"?

edited May 28 '18 at 15:59

answered May 28 '18 at 06:39

SeGa

9,454
3
31
70

Thanks. But still the same. :( – ds_user May 28 '18 at 06:41
Same again.. :( – ds_user May 28 '18 at 06:45
How do I specify that? Or is there a way I can debug further on what is happening behind this? – ds_user May 28 '18 at 06:47
I tried this, `port <- 3306 DSN = paste0('MySQL:dbname=',db ,' user=', user, ' host=', host, ' port=', port, ' password=', pwd)` . It didn't work. – ds_user May 28 '18 at 06:50
Updated. Please check. – ds_user May 28 '18 at 07:41
I think you were right with the comma separated arguments. Try the new edited answer. – SeGa May 28 '18 at 16:00

score 1 · Answer 2 · answered May 28 '18 at 22:07

First of all, Thanks to @SeGa for his continuous help, using that, I could manage to finally load the data. But there are some corrections, thats why I am detailing it as a separate answer.

library(sf)
fname <- "/path/to/shapefile/data_2013.shp"
data_2013 <- st_read(fname)

db <- 'testdb'
user <- 'testuser'
pwd <- '****'
host <- '127.0.0.1'
port <- 3306
DSN = paste0('MySQL:',db ,',user=', user,',password=', pwd, ',port=', port)

st_write(obj = data_2013, dsn = DSN, layer = "data_2013")

DSN has to be exactly as the above, no 'dbname' string in that. Also, the order has to be exactly as this. Because, gdal expects that, if you look at this link - http://www.gdal.org/drv_mysql.html

I tried with mysql connection on this, it didn't work, but giving mysql dsn worked. Only issue is, this creates some extra tables (geometry_columns, spatial_ref_sys) in the database.

Thanks again for your help @SeGa.

writeOGR layer creation failed - MySQL

2 Answers2