NULL error in sfc_from_ogr and segfaults with st_read

[tbradley1013]

Hi,

I am trying to use sf on an ubuntu 16.04 server to read layers out of a MSSQLSpatial database. I am able to connect to the database on the server and st_layers returns the expected output. However, when I run st_read I get the following error:

NULL error in sfc_from_ogr

The table has data and I know that because when I run the same command on my windows machine it returns the information as expected. Additionally, if I try to read a layer with polygon geometry columns, I get a segfault error and my R session crashes.

I was using the version of gdal that was available though ppa:ubuntu-gis/ubstable (2.2.2), however it was returning encoding errors when using ogrinfo to read geometry columns. As a result, I built gdal (2.4.0) and proj (5.2.0) from source to be able to read the data out of the database. Do you have any idea why these errors would be occuring?

Ubuntu 16.04

library(sf)
Linking to GEOS 3.5.1, GDAL 2.4.0, PROJ 5.2.0
# define dsn
dsn_srs <- paste0(
  "MSSQL:server=xxx;",
  "database=xxx;",
  "UID=xxx;",
  "PWD=xxx;"
)

# list layers 
st_layers(dsn_srs)
#> Driver: MSSQLSpatial
#> Available layers:
#>                     layer_name geometry_type features fields
#> 1         dbo.OLWQ_SITES_withG                     40     50
#> 2        dbo.PressureDistricts                     12      9
#> 3         dbo.CCSAlertCalls_VW                    614     17
#> 4           dbo.OLWQ_SITES_OLD                     50     51
#> 5                      dbo.WTP                      6     14
#> 6               dbo.OLWQ_SITES                     40     50
#> 7              dbo.tblCCSCalls                   3388     15
#> 8                      dbo.ICS                      0      1
#> 9          dbo.Cust_complaints                      0      8
#> 10                   dbo.ICSFC                      0      1
#> 11             dbo.SampleSites                     92     67
#> 12 dbo.tblPHS_CDMSDetailsTable                     92     19
#> 13         dbo.tblPHS_Zipcodes                    274      5
#> 14        dbo.tblPHS_Hospitals                     33     12
#> 15          dbo.tblCCSCalls_VW                   3388     15
#> 16               dbo.ESM_SITES                     16      2
#> 17             dbo.PHS_CDMS_VW                      0     25
#> 18        dbo.PHS_SYNDROMIC_VW                      0     18
#> 19             dbo.MainBreaks2                      4      7
#> 20           dbo.OLWQ_SITES_VW                     28     50
#> 21          dbo.MainBreaks2_VW                      4      7
#> 22             dbo.CCSCalls_RG                   3338     15
#> 23        dbo.tblCWSAlertTable                   1103     13
#> 24                    dbo.Test                      6      1
#> Warning message:
#> In CPL_get_layers(dsn, options, do_count) :
#>   GDAL Error 1: Error initializing the metadata tables : [37000]CREATE TABLE permission denied in database 'xxx'., (262)


# This table has point geometries
st_read(dsn_srs, layer = "dbo.SampleSites")
#> Reading layer `dbo.SampleSites' from data source `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;' using driver `MSSQLSpatial'
#> Error in CPL_read_ogr(dsn, layer, query, as.character(options), quiet,  :
#>   NULL error in sfc_from_ogr
#> In addition: Warning messages:
#> 1: In CPL_read_ogr(dsn, layer, query, as.character(options), quiet,  :
#>   GDAL Error 1: Error initializing the metadata tables : [37000]CREATE TABLE permission denied in database 'xxx'., (262)
#> 2: In CPL_read_ogr(dsn, layer, query, as.character(options), quiet,  :
#>   GDAL Error 1: [00000](0)

# This table has polygon geometries and causes 
st_read(dsn_srs, layer = "dbo.PressureDistricts")
#> Reading layer `dbo.PressureDistricts' from data source `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;' using driver `MSSQLSpatial'

#>  *** caught segfault ***
#> address 0x20, cause 'memory not mapped'

#> Traceback:
#>  1: CPL_read_ogr(dsn, layer, query, as.character(options), quiet,     type, fid_column_name, promote_to_multi, int64_as_string)
#>  2: st_read.character(dsn_srs, layer = "dbo.PressureDistricts")
#>  3: st_read(dsn_srs, layer = "dbo.PressureDistricts")

#> Possible actions:
#> 1: abort (with core dump, if enabled)
#> 2: normal R exit
#> 3: exit R without saving workspace
#> 4: exit R saving workspace
#> Selection: 3
#> Warning messages:
#> 1: In CPL_read_ogr(dsn, layer, query, as.character(options), quiet,  :
#>   GDAL Error 1: Error initializing the metadata tables : [37000]CREATE TABLE permission denied in database 'xxx'., (262)
#> 2: In CPL_read_ogr(dsn, layer, query, as.character(options), quiet,  :
#>   GDAL Error 1: CPLMalloc(-2): Silly size requested.

Windows

library(sf)
#> Warning: package 'sf' was built under R version 3.5.2
#> Linking to GEOS 3.6.1, GDAL 2.2.3, PROJ 4.9.3

dsn_srs <- paste0(
  "MSSQL:server=xxx;",
  "database=xxx;",
  "UID=xxx;",
  "PWD=xxx;"
)

st_read(dsn_srs, layer = "dbo.SampleSites")
#> Warning in CPL_read_ogr(dsn, layer, query, as.character(options), quiet, :
#> GDAL Error 1: Error initializing the metadata tables : [Microsoft][ODBC
#> SQL Server Driver][SQL Server]CREATE TABLE permission denied in database
#> 'xxx'.
#> Reading layer `dbo.SampleSites' from data source `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;' using driver `MSSQLSpatial'
#> Simple feature collection with 92 features and 67 fields
#> geometry type:  POINT
#> dimension:      XY
#> bbox:           xmin: 2669698 ymin: 207794.9 xmax: 2742221 ymax: 297992
#> epsg (SRID):    NA
#> proj4string:    NA

st_read(dsn_srs, layer = "dbo.PressureDistricts")
#> Warning in CPL_read_ogr(dsn, layer, query, as.character(options), quiet, :
#> GDAL Error 1: Error initializing the metadata tables : [Microsoft][ODBC
#> SQL Server Driver][SQL Server]CREATE TABLE permission denied in database
#> 'xxx'.
#> Reading layer `dbo.PressureDistricts' from data source `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;' using driver `MSSQLSpatial'
#> Simple feature collection with 12 features and 9 fields
#> geometry type:  POLYGON
#> dimension:      XY
#> bbox:           xmin: 2660474 ymin: 199788.3 xmax: 2750107 ymax: 304935.2
#> epsg (SRID):    NA
#> proj4string:    NA

Any help with this is greatly appreciated

[edzer]

You might be bitten by the multiple GDAL/GEOS/PROJ version problem on linux, see https://github.com/r-spatial/sf#multiple-gdal-geos-andor-proj-versions-on-your-system . The ugly LD_LIBRARY_PATH hack I mentioned in #844 may help you diagnose the problem.

[tbradley1013]

Thanks for the quick reply. I tried both your solution with LD_LIBRARY_PATH and the other solution which recommended explicitly loading geos with dyn.load and it did not work.

I am not sure exactly how to go about removing everything related to both source and non-source versions of these packages. Is there any quick-ish way to find all of them and remove them and start from scratch with the source versions of the packages?

Is geos the likely culprit since the ogfinfo command works from bash?

[tbradley1013]

Also, is there a way to verify that I have two of any of these packages installed? Before I installed the source version of gdal and proj I ran sudo apt-get purge --auto-remove for both packages and ran make uninstall for the source version of geos before reverting back to the binary version

[edzer]

I don't know. I only work with ubuntugis-unstable, or docker containers with only source installs, stopped trying anything else.

[edzer]

You did reinstall sf after reinstalling the other libs, right?

[tbradley1013]

Yes I reinstalled sf after reinstalling the libs.

If I am understanding the multiple versions issue correctly, then the error occurs because the version being liked to in gdal-config, geos-config, and proj during build differ from the versions being called when the package is loaded. This does not appear to be the case for me. See below:

$ gdal-config --version
2.4.0
$ geos-config --version
3.5.1
$ proj --version
Rel. 5.2.0, September 15th, 2018
<proj>:
invalid option: --
program abnormally terminated
$ R
> library(sf)
Linking to GEOS 3.5.1, GDAL 2.4.0, PROJ 5.2.0

[tbradley1013]

I uninstalled the geos 3.5.1 and installed the source version 3.7.1. I was able to reinstall sf correctly, but I am still getting the same strange error of NULL error in sfc_from_ogr when I try to use st_read against the database.

When I upload an ESRI shapefile onto the server from my local machine and run st_read against the file path, It works as expected without any issues.

It is very possible that I am missing something here, but it doesn't seem like this is an issue with multiple installs to me.

[edzer]

OK, then it may not be R/sf. Try to open the same data source with gdalinfo, outside R.

[tbradley1013]

I am not 100% sure how to use gdalinfo against a MSSQL database. However, using ogrinfo -sql seems to be doing something similar and is giving me the same errors on my machine. A docker image with the same versions of the packages is not giving me this error.

production server

# ogrinfo lists the table as having a geometry
$ ogrinfo "MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;"   
                   
ERROR 1: Error initializing the metadata tables : [37000]CREATE TABLE permission denied in database 'xxx'., (262)
INFO: Open of `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;'
      using driver `MSSQLSpatial' successful.
1: dbo.OLWQ_SITES_withG
2: dbo.PressureDistricts

# ogrinfo -al shows the features of a geometry for one but fails for the second
$ ogrinfo -al "MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;"                  
ERROR 1: Error initializing the metadata tables : [37000]CREATE TABLE permission denied in database 'xxx'., (262)
INFO: Open of `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;'
      using driver `MSSQLSpatial' successful.

Layer name: dbo.OLWQ_SITES_withG
Geometry: Unknown (any)
Feature Count: 40
ERROR 1: [00000](0)
Extent: (2728580.000000, 297706.000000) - (2728580.000000, 297706.000000)
Layer SRS WKT:
PROJCS["NAD83 / Pennsylvania South (ftUS)",
    GEOGCS["NAD83",
        DATUM["North_American_Datum_1983",
            SPHEROID["GRS 1980",6378137,298.257222101,
                AUTHORITY["EPSG","7019"]],
            TOWGS84[0,0,0,0,0,0,0],
            AUTHORITY["EPSG","6269"]],
        PRIMEM["Greenwich",0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.0174532925199433,
            AUTHORITY["EPSG","9122"]],
        AUTHORITY["EPSG","4269"]],
    PROJECTION["Lambert_Conformal_Conic_2SP"],
    PARAMETER["standard_parallel_1",40.96666666666667],
    PARAMETER["standard_parallel_2",39.93333333333333],
    PARAMETER["latitude_of_origin",39.33333333333334],
    PARAMETER["central_meridian",-77.75],
    PARAMETER["false_easting",1968500],
    PARAMETER["false_northing",0],
    UNIT["US survey foot",0.3048006096012192,
        AUTHORITY["EPSG","9003"]],
    AXIS["X",EAST],
    AXIS["Y",NORTH],
    AUTHORITY["EPSG","2272"]]
FID Column = ID
Geometry Column = G


Layer name: dbo.PressureDistricts
ERROR 1: Column Shape requested for geometry, but it does not exist.
Geometry: Unknown (any)
Warning 1: Value 'dbo' of field dbo.PressureDistricts parsed incompletely to real 0.
Feature Count: 1

# specifying -sql and requesting the pressure districts layer gives a segfault error
$ ogrinfo -sql "select top 1 * from dbo.PressureDistricts" "MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;"
ERROR 1: Error initializing the metadata tables : [37000]CREATE TABLE permission denied in database 'xxx'., (262)
INFO: Open of `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;'
      using driver `MSSQLSpatial' successful.

Layer name: SELECT
Geometry: Unknown (any)
ERROR 1: CPLMalloc(-2): Silly size requested.
Segmentation fault (core dumped)

# Using ogrinfo -sql for the table that showed geometry properties in ogr -al output says geometry is now unknown
$ ogrinfo -sql "select top 1 * from dbo.OLWQ_SITES_withG" "MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;"
ERROR 1: Error initializing the metadata tables : [37000]CREATE TABLE permission denied in database 'xxx'., (262)
INFO: Open of `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;'
      using driver `MSSQLSpatial' successful.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
(unknown)
ID: Integer (10.0) NOT NULL
G: Binary (0.0)

docker image

On a docker image with gdal and proj built from source none of these errors are occurring. all of the queries shown in the above section work perfectly fine against the exact same database. Just to illustrate:

$ ogrinfo -sql "select top 1 * from dbo.PressureDistricts" "MSSQL:server=xxx;database
=xxx;UID=xxx;PWD=xxx;Driver=ODBC Driver 17 for SQL Server;"
ERROR 1: Error initializing the metadata tables : [37000][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]CREATE TABLE permission denied in database 'xxx'.(262)
INFO: Open of `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;Driver=ODBC Driver 17 for SQL Server;'
      using driver `MSSQLSpatial' successful.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
PROJCS["NAD83 / Pennsylvania South (ftUS)",
    GEOGCS["NAD83",
        DATUM["North_American_Datum_1983",
            SPHEROID["GRS 1980",6378137,298.257222101,
                AUTHORITY["EPSG","7019"]],
            TOWGS84[0,0,0,0,0,0,0],
            AUTHORITY["EPSG","6269"]],
        PRIMEM["Greenwich",0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.0174532925199433,
            AUTHORITY["EPSG","9122"]],
        AUTHORITY["EPSG","4269"]],
    PROJECTION["Lambert_Conformal_Conic_2SP"],
    PARAMETER["standard_parallel_1",40.96666666666667],
    PARAMETER["standard_parallel_2",39.93333333333333],
    PARAMETER["latitude_of_origin",39.33333333333334],
    PARAMETER["central_meridian",-77.75],
    PARAMETER["false_easting",1968500],
    PARAMETER["false_northing",0],
    UNIT["US survey foot",0.3048006096012192,
        AUTHORITY["EPSG","9003"]],
    AXIS["X",EAST],
    AXIS["Y",NORTH],
    AUTHORITY["EPSG","2272"]]
FID Column = FID
Geometry Column = Shape
OBJECTID: Real (0.0)

The only difference that jumps out to me in the function calls is the driver. In the docker image I am using the one provided by Microsoft for using sql server on linux and in the production server I am using the drivers provided by RStudio for Connect.

[edzer]

Yes, I meant ogrinfo. Well, this indicates to me that sf is not the source of your problem...

[tbradley1013]

I would have to agree with you. I will close this issue. Thanks for you help.

[tbradley1013]

I just wanted to leave a comment here in case any one else runs across a problem like this in the future. I was able to correctly pull data from the database when I changed what driver was being used. Updating the driver from the ones provided by RStudio Connect to the one provided by Microsoft for Ubuntu 16.04 worked for me. The link to the Microsoft driver is here