Read Geometry Field in SQL Table

[michaelgloven]

Hi,

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]
list()
..@ 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.

[michaelgloven]

ok, I found help from someone who can construct SQL queries with the correct syntax for retrieving list fields from the shape_geography column. The syntax is tricky for the novice, but an experienced SQL person should know what to do. Once the list is retrieved, the WKT and WKB packages can be used to get the data into a dataframe for use in mapping functions like Leaflet.