R – How to Read OGC Geometry Data from SQL Server Using R

geometryogcqueryrsql server

I would like to write a script in R that creates a map using OGC geometry data that is stored on a Microsoft SQL server. Is there a way to read OGC geometry data using a query passed from R?

Maybe the code would look something like this (But not like this because the following code is actually garbage).

library(RODBC)
library(maptools)
library(maps)

png(file="example%02d.png", width=600, height=480)

con <- odbcDriverConnect('driver={SQL Server};server=SERVERNAME;database=DBNAME;trusted_connection=true')

objects_1 <- sqlQuery(con, 'SELECT OBJID, Shape FROM TABLENAME;')

spplot(objects_1, col="#000000FF", sp.layout = list(otherObjects))

Best Answer

The following code in R allows a representation of Microsoft SQL Server geometry objects:

library(RODBC)

png(file="examplex.png", width=600, height=480)
setwd("C:/ArcR")

con <- odbcDriverConnect('driver={SQL Server};server=SERVERNAME;database=DBNAME;trusted_connection=true')

objects_1 <- sqlQuery(con, 'SELECT TOP (1) Shape.STAsText() as ShapeWKT FROM TABLENAME ;')

things <- vector("list", 1)

z = 0
for(line in objects_1$ShapeWKT)
{
  {
    things[[z+1]]<-readWKT(line)
  }
  z = z + 1
}

plot(things[[1]]) 

dev.off()
Related Question