5

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))
4
  • 1
    What server? What specific "OGC" geometry data?
    – BradHards
    Commented Apr 14, 2013 at 21:14
  • 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.
    – ike
    Commented Apr 15, 2013 at 14:59
  • I meant what format is the data in? Some binary blob (like WKB)? KML geometry? GML Geometry? Some other OGC format?
    – BradHards
    Commented Apr 15, 2013 at 22:29
  • 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.
    – ike
    Commented Apr 15, 2013 at 22:41

1 Answer 1

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.