geojson_read() allows you to get data out of a PostgreSQL database set up with PostGIS. Below are steps for setting up data that we can at the end query with geojson_read()
Once you have both of those installed, you can proceed below.
Examples
## Not run:if(requireNamespace("DBI")&& requireNamespace("RPostgres")){ library("DBI") library("RPostgres")# Create connection conn <- tryCatch(dbConnect(RPostgres::Postgres()), error =function(e) e)if(inherits(conn,"PqConnection")){# Create database dbSendQuery(conn,"CREATE DATABASE postgistest")# New connection to the created database conn <- dbConnect(RPostgres::Postgres(), dbname ="postgistest")# Initialize PostGIS in Postgres dbSendQuery(conn,"CREATE EXTENSION postgis") dbSendQuery(conn,"SELECT postgis_full_version()")# Create table dbSendQuery(conn, "CREATE TABLE locations(loc_id integer primary key
, loc_name varchar(70), geog geography(POINT));")# Insert data dbSendQuery(conn, "INSERT INTO locations(loc_id, loc_name, geog) VALUES (1,'Waltham, MA', ST_GeogFromText('POINT(42.40047 -71.2577)')),(2,'Manchester, NH', ST_GeogFromText('POINT(42.99019 -71.46259)')),(3,'TI Blvd, TX', ST_GeogFromText('POINT(-96.75724 32.90977)'));")# Get data (notice warnings of unknown field type for geog) dbGetQuery(conn,"SELECT * from locations")# Once you're setup, use geojson_read() conn <- dbConnect(RPostgres::Postgres(), dbname ="postgistest") state <- "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
FROM (SELECT 'Feature' As type
, ST_AsGeoJSON(lg.geog)::json As geometry
, row_to_json((SELECT l FROM (SELECT loc_id, loc_name) As l
)) As properties
FROM locations As lg ) As f ) As fc;"
json <- geojson_read(conn, query = state, what ="json")## map the geojson with map_leaf() map_leaf(json)}}## End(Not run)