如何从SQL Server高效获取空间数据并转换为sf数据框？

当从SQL Server返回空间查询结果时，得到的数据框中“Shape”列为Character类型，无法直接转换为sfg对象。

尝试的两种SQL查询 #

1. 使用STAsText()的查询 #

library(DBI)
library(odbc)
library(sf)

query_sf.AsText <-  "Select CLASEUSO, Shape.STAsText() AS Shape FROM my_Table where REGION IN ('Region_mh', 'Region_no', 'Region_in')"
query_sf.AsBinary <-  "Select CLASEUSO, Shape.STAsBinary() AS Shape FROM my_Table where REGION IN ('Region_mh', 'Region_no', 'Region_in')"

df_text <- st_read(odbc_con, query = query_sf.AsText)
> Warning message:
> In st_read.DBIObject(odbc_con, query = query_sf) :
> Could not find a simple features geometry column. Will return a `data.frame`.

df_binary <- st_read(odbc_con, query = query_sf.AsBinary)

ex_text <- df_text[1:3, ]
ex_binary <- df_binary[1:3, ]

查看ex_text的结构：

str(ex_text)

> 'data.frame': 3 obs. of  2 variables:
>  $ CLASEUSO: chr  "Vegetacion Nativa" "Vegetacion Nativa" "Vegetacion Nativa"
>  $ Shape   : chr  "POLYGON ((-5865371.0349 -2234709.5711000003, -5865383.0660999995 -2234694.5898, -5865392.442 -2234690.814500000"| __truncated__ "POLYGON ((-5866433.0649 -2236171.0835000016, -5866431.4669 -2236170.9224999994, -5866431.1 -2236170.8986000009,"| __truncated__ "POLYGON ((-5864979.8155000005 -2236093.0526, -5865013.8751 -2236072.0670999996, -5865019.1833 -2236072.01419999"| __truncated__

2. 使用STAsBinary()的查询 #

使用STAsBinary()的查询结果中，Shape列未返回小数分隔符：

str(ex_binary)

> Classes ‘sf’ and 'data.frame':    3 obs. of  2 variables:
>  $ CLASEUSO: chr  "Vegetacion Nativa" "Vegetacion Nativa" "Vegetacion Nativa"
>  $ Shape   :sfc_POLYGON of length 3; first list element: List of 1
>   ..$ : num [1:184, 1:2] -5865371 -5865383 -5865392 -5865396 -5865401 ...
>  - attr(*, "class")= chr [1:3] "XY" "POLYGON" "sfg"
>  - attr(*, "sf_column")= chr "Shape"
>  - attr(*, "agr")= Factor w/ 3 levels "constant","aggregate",..: NA
>  - attr(*, "names")= chr "CLASEUSO"

尝试转换字符型Shape列失败 #

尝试将字符型的Shape列转换为"XY" "POLYGON" "sfg"对象，但未成功：

ex_text$Shape <- st_as_sfc(ex_text$Shape)
sf_tbl_text = st_as_sf(ex_text) 
st_crs(sf_tbl_text) = 4326 
sf_tbl_text %>% mapview::mapview()

提问 #

请问获取sf数据框的最优方法是什么？此外，STAsText()查询耗时过长。

内容的提问来源于stack exchange，提问作者viniciovcl