Demo

libs

library(RPostgreSQL)
library(sf)
library(leaflet)
library(janitor)
library(dplyr)
source('secrets.R')

available drivers

Available drivers using sf library

sf::st_drivers()

db connection

conn <- RPostgres::dbConnect(
    RPostgres::Postgres(),
    sslmode = 'require',
    user = Sys.getenv('DB_USERS_USERNAME'), 
    password = Sys.getenv('DB_USERS_PASSWORD'), 
    dbname = Sys.getenv('DB_NAME'),
    host = Sys.getenv('DB_HOST'),
    port = Sys.getenv('DB_PORT')
  )

# alt ideast_asGeoJSON(geom) as geom_alt
point_geo <- sf::st_read(conn, query = "SELECT * FROM dshtest.dsp4sh_shape")

dbDisconnect(conn)

gis stuff

# applies CRS baked into postgis geometry
# This may mandate 4326 WGS84 for certain data visualization
coordinate_reference_system <- st_crs(point_geo)
coordinate_reference_system
## Coordinate Reference System:
##   User input: EPSG:4326 
##   wkt:
## GEOGCRS["WGS 84",
##     ENSEMBLE["World Geodetic System 1984 ensemble",
##         MEMBER["World Geodetic System 1984 (Transit)"],
##         MEMBER["World Geodetic System 1984 (G730)"],
##         MEMBER["World Geodetic System 1984 (G873)"],
##         MEMBER["World Geodetic System 1984 (G1150)"],
##         MEMBER["World Geodetic System 1984 (G1674)"],
##         MEMBER["World Geodetic System 1984 (G1762)"],
##         MEMBER["World Geodetic System 1984 (G2139)"],
##         ELLIPSOID["WGS 84",6378137,298.257223563,
##             LENGTHUNIT["metre",1]],
##         ENSEMBLEACCURACY[2.0]],
##     PRIMEM["Greenwich",0,
##         ANGLEUNIT["degree",0.0174532925199433]],
##     CS[ellipsoidal,2],
##         AXIS["geodetic latitude (Lat)",north,
##             ORDER[1],
##             ANGLEUNIT["degree",0.0174532925199433]],
##         AXIS["geodetic longitude (Lon)",east,
##             ORDER[2],
##             ANGLEUNIT["degree",0.0174532925199433]],
##     USAGE[
##         SCOPE["Horizontal component of 3D system."],
##         AREA["World."],
##         BBOX[-90,-180,90,180]],
##     ID["EPSG",4326]]

quick plot

leaflet() %>% 
  addTiles() %>% 
  addMarkers(data = point_geo)
# alternatively with ggplot

#library(ggplot2)
# point_geo %>% 
#   st_transform(coordinate_reference_system) %>% 
#   ggplot() +
#   geom_sf() 

save as geojson

sf::st_write(point_geo, dsn = "points_example.geojson", delete_layer = TRUE)
## Warning in CPL_write_ogr(obj, dsn, layer, driver,
## as.character(dataset_options), : GDAL Error 6: DeleteLayer() not supported by
## this dataset.
## Deleting layer not supported by driver `GeoJSON'
## Deleting layer `points_example' failed
## Writing layer `points_example' to data source 
##   `points_example.geojson' using driver `GeoJSON'
## Updating existing layer points_example
## Writing 876 features with 22 fields and geometry type Point.
## Warning in CPL_write_ogr(obj, dsn, layer, driver,
## as.character(dataset_options), : GDAL Message 1: Several features with id = 1
## have been found. Altering it to be unique. This warning will not be emitted
## anymore for this layer

save as shapefile

Shapefiles are ugly things and have limits to field names / sizes to keep in mind

names(point_geo) <- substr(names(point_geo),1,10)
point_geo <- janitor::clean_names(point_geo)
names(point_geo)[names(point_geo) == 'id'] <- 'gid'

point_geo %>% 
  sf::st_write(., "points_example.shp", delete_layer = TRUE)
## Warning in abbreviate_shapefile_names(obj): Field names abbreviated for ESRI
## Shapefile driver
## Deleting layer `points_example' using driver `ESRI Shapefile'
## Writing layer `points_example' to data source 
##   `points_example.shp' using driver `ESRI Shapefile'
## Writing 876 features with 22 fields and geometry type Point.

proof of output

https://gisgeography.com/arcgis-shapefile-files-types-extensions/

list.files(pattern = "\\.shp$|\\.shx$|\\.dbf$|\\.prj$", ignore.case = TRUE) 
## [1] "points_example.dbf" "points_example.prj" "points_example.shp"
## [4] "points_example.shx"
list.files(pattern = "\\.geojson$", ignore.case = TRUE) # projection file
## [1] "points_example.geojson"