Write shapefile data into SQL database

I have a shapefile with geographical polygon data (downloaded here: geoBoundaries)

I want to write the data into an SQL database so that I can intersect it with longitude / latitude data. I already tried the following:


# Read the shapefile into R
shapename <- read_sf('.../DownloadsgeoBoundariesCGAZ_ADM2/geoBoundariesCGAZ_ADM2.shp')

# Set up connection to SQL database
con = dbConnect(odbc(),
                Driver = 'Driver',
                Server = 'Server',
                UID = 'Test'_UID,
                scheme = 'dbo',
                PWD = 'Test_PWD'
                encoding = 'latin1')

# Write data into database
dbWriteTable(con, name = 'Test.dbo.Geo_Polygons', value = shapename)

Unfortunately, this will write the field geometry into the database as a varchar(max). However, in order to intersect it with longitudes / latitudes, I think it should be of type geography with CRS WGS84.

How do I manage to write the geometry data in the correct format into the SQL database?

Only MSSQLSpatial and PostgreSQL are supported. MySql, MariaDB require the conversion of the geometry to a WKT representation.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.