Querying Sql Server Geospatial Data From R

Edited: I have added login details to a demo SQL Server 2017. I am querying an SQL Server from R using the FreeTDS driver (preferred over the Microsoft odbc driver as it supports W

Solution 1:

Have you tried the WKB representation?

mytest <- st_read(con, geometry_column = "geom", 
                         query = "
select geometry::STGeomFromText('POLYGON ((0 0, 1.5 0, 1.5 1.5, 0 1.5, 0 0))', 0).STAsBinary() as geom
union all
select geometry::Parse('CIRCULARSTRING(0 0, 1 1.10, 2 2.3246, 0 7, -3 2.3246, -1 2.1082, 0 0)').STAsBinary() as geom;
")

plot(mytest)

Solution 2:

The issue here is that you want to translate the format in which MSSQL stores geometry data to something that the sf package can understand. STAsBinary() should work.

data <- dbGetQuery(con, 'SELECT TOP 2
                     objectid, 
                     geom.STAsBinary(),
                     geom.geom_type,
                     geom.STSrid STSrid
                     FROM spatialdata')

Then st_read(data) should work