Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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))
share|improve this question
1  
What server? What specific "OGC" geometry data? –  BradHards Apr 14 '13 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 Apr 15 '13 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 Apr 15 '13 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 Apr 15 '13 at 22:41
add comment

1 Answer

up vote 1 down vote accepted

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()
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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