从 R 查询 SQL Server 地理空间数据 [英] Querying SQL Server geospatial data from R
问题描述
已我已将登录详细信息添加到演示 SQL Server 2017 中.
Edited: I have added login details to a demo SQL Server 2017.
我正在使用 FreeTDS 驱动程序从 R 查询 SQL Server(优先于 Microsoft odbc 驱动程序,因为它支持 Linux 中的 Windows 身份验证).
I am querying an SQL Server from R using the FreeTDS driver (preferred over the Microsoft odbc driver as it supports Windows authentication in Linux).
我正在努力用 st_read
解释几何列.
I'm struggling to interpret the geometry column with st_read
.
连接是这样发起的:
library(odbc)
library(DBI)
library(sf)
username <- 'SO-user'
sql_server_ip <- '35.214.169.110'
password <- 'SQLaskingfortrouble?!'
con <- dbConnect(odbc(),
Driver = "FreeTDS",
Server = sql_server_ip,
Database = "stackoverflow-example",
UID = username,
PWD = password,
Port = 1433)
# I have also tried Driver = "ODBC Driver 17 for SQL Server"
查询结果如下:
data <- dbGetQuery(con, 'SELECT TOP 2
objectid, geom,
geom.STGeometryType() geom_type,
geom.STSrid STSrid
FROM spatialdata')
objectid geom geom_type stsRID
864 blob[416 B] LineString 25832
865 blob[416 B] LineString 25832
class(data$geom)
"blob" "vctrs_list_of" "vctrs_vctr"
原始/斑点几何看起来像十六进制:
The raw/blob geometry looks like hex:
rawToHex(data$geom[1])
e86400000104180000004003780b38331f416b4df3ea9ecf5741a04c150c38331f4159ca32f09ecf574160ba...
尝试使用 st_read
,所以我可以从中得到一个有用的 sf 数据帧,根据我传递给函数的选项,给了我一系列错误消息:
Trying to use st_read
, so I can get a useful sf dataframe out of it, gives me a range of error messages, depending on the options I pass to the function:
st_read(con, geometry_column = "geom",
query = "SELECT TOP 10 objectid, geom FROM \"spatialdata\"")
Error: nanodbc/nanodbc.cpp:1617: 00000: [FreeTDS][SQL Server]Invalid object name 'spatial_ref_sys'. [FreeTDS][SQL Server]Statement(s) could not be prepared.
<SQL> 'select srtext from spatial_ref_sys where srid = 70647808'
In addition: Warning message:
In CPL_crs_from_input(x) :
GDAL Error 6: EPSG PCS/GCS code 70647808 not found in EPSG support files. Is this a valid EPSG coordinate system?
看起来 EPSG (25832) 没有被正确识别.
It looks like the EPSG (25832) is not being recognised correctly.
在我的例子中 st_read 的正确构造是什么?
What is the correct construction of st_read in my case?
额外问题:如何将边界框有效地附加到 st_read?
Bonus question: How can a bounding box be efficiently appended to st_read?
推荐答案
您是否尝试过 WKB 表示法?
Have you tried the WKB representation?
mytest <- st_read(con, geometry_column = "geom",
query = "
select geometry::STGeomFromText('POLYGON ((0 0, 1.5 0, 1.5 1.5, 0 1.5, 0 0))', 0).STAsBinary() as geom
union all
select geometry::Parse('CIRCULARSTRING(0 0, 1 1.10, 2 2.3246, 0 7, -3 2.3246, -1 2.1082, 0 0)').STAsBinary() as geom;
")
plot(mytest)
这篇关于从 R 查询 SQL Server 地理空间数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!