I am trying to read a SQL Table which contains a column type 'geography'. I can use DBI::dbGetQuery to read non-geography SQL data, but the geography column fails the query - its structure is indicated as:
$ Shape_Geography : blob [1:0]
..@ ptype: raw
The result of the query provides this error:
Error in result_fetch(res@ptr, n) : nanodbc/nanodbc.cpp:3280: 07009: [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
If I set n = 0 for rows to retrieve the query completes but without any data.
I found this old explanation related to this issue: https://gis.stackexchange.com/questions/182483/read-sql-server-geometry-datatype-in-r#182495 but can't get the process to work with what seems to be a straight forward issue.
My goal is to retrieve the geography field from SQL, store it in a dataframe and use it in leaflet to make maps in Shiny.
Does anyone have suggestions on where I can go to figure out the first step i.e. retrieve the data from SQL geography column and store it in a dataframe?
Appreciate any suggestions.