0

I converted coordinates in SQL to geographic type of POINT:

geography::Point(c.Latitude,c.Longitude,4326).ToString()

In R, I can see that this column is of type char, not geom even though I converted it properly. I also tried conversion without using .ToString() but there was an error in this column when I executed SQL code in R. In SQL it showed my sth like this as POINT:

0xE6100000010CC2BEBAEEA0824940F37CC85CBDE33040

Is it possible at all to convert data to geom type in SQL and see them as geom in R? Or maybe I should do this only in R?

mustafa00
  • 751
  • 1
  • 7
  • 28

1 Answers1

2

Two approaches:

  1. If your table currently has two float fields named Latitude and Longitude, I suggest you pull them directly (as numeric in R), and then do whatever sf:: or sp:: work you need in R.

    DBI::dbGetQuery(con, "select c.Longitude, c.Latitude from ...")

  2. Import into sf objects in R. I'll adapt sample code provided https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/point-geography-data-type?view=sql-server-ver15:

    con <- DBI::dbConnect(...)
# demonstration of the version I'm using, just for comparison
DBI::dbGetQuery(con, "select @@version as ver")
#                                                                                                                                                                                                                          ver
# 1 Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64) \n\tJul  5 2017 23:41:17 \n\tCopyright (c) Microsoft Corporation\n\tStandard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: )\n


out <- DBI::dbGetQuery(con, "DECLARE @g geography;   
SET @g = geography::Point(47.65101, -122.34901, 4326)  
(SELECT @g.ToString() as point) union all (select @g.ToString() as point) ;  ")

out
#                         point
# 1 POINT (-122.34901 47.65101)
# 2 POINT (-122.34901 47.65101)

    From here, you can either parse out the components if you just need the numbers:

    strcapture("\\b([-+]?[0-9]+\\.?[0-9]*)\\s+([-+]?[0-9]+\\.?[0-9]*)\\b", out$point, proto = list(lon=0, lat=0))
#       lon      lat
# 1 122.349 47.65101
# 2 122.349 47.65101

    or you can import them directly as an sf object:

    sf::st_as_sfc(out$point)
# Geometry set for 2 features 
# geometry type:  POINT
# dimension:      XY
# bbox:           xmin: -122.349 ymin: 47.65101 xmax: -122.349 ymax: 47.65101
# CRS:            NA
# POINT (-122.349 47.65101)
# POINT (-122.349 47.65101)

And from there, convert it to the R-specific format you need for local processing.

r2evans
  • 141,215
  • 6
  • 77
  • 149
  • Thank you. I have additional question - what is faster for large amount of data? Importing 'float' coordinates and convert to sf in R or execute code on server side (geography::Point) and create sf object? – mustafa00 Dec 28 '20 at 20:09
  • 1
    I would expect that numbers are theoretically faster, but you should also consider any overhead (on either side) of conversion. I expect that both routines would be fairly efficient, so time them yourself given your typical (or most-likely "worst case", if that makes sense) scenario. For smaller data (1000s of rows), I'd think it'd be a wash. – r2evans Dec 28 '20 at 20:22