library(RPostgreSQL)
library(sf)
library(leaflet)
library(janitor)
library(dplyr)
source('secrets.R')
Available drivers using sf library
sf::st_drivers()
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)
# 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]]
leaflet() %>%
addTiles() %>%
addMarkers(data = point_geo)
# alternatively with ggplot
#library(ggplot2)
# point_geo %>%
# st_transform(coordinate_reference_system) %>%
# ggplot() +
# geom_sf()
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
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.
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"