Reading OGC geometry data from SQL server using R

Question

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))

• BradHards: What server? What specific "OGC" geometry data?
• ike: Microsoft's SQL product is named SQL Server, I probably should have written Microsoft SQL Server. The specific OGC geometry data would really be very unspecific (lines, points, polygons). Though for this specific example I'd be willing to settle for just lines.
• BradHards: I meant what format is the data in? Some binary blob (like WKB)? KML geometry? GML Geometry? Some other OGC format?
• ike: Microsoft SQL Server documentation refers to it as OGC geometry data, but using the STasText() function causes it to be readable as WKT. I think combining that function with an r mapping package would get me the results I need.

Answer 1

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()