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


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

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


png(file="examplex.png", width=600, height=480)

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)
  z = z + 1



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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