GeoJSON objects from PostGIS, in R

Question

I'm interested in displaying data in Leaflet from PostGIS, and would particularly like to have my data as GeoJSON when passing to Leaflet. I can currently do this via:

# set connection
conn <- dbConnect("PostgreSQL",user="postgres",password="pass",port=5432,dbname="postgis_user")

# set query and reproject to EPSG:4326 for GeoJSON creation 
# (the real query is more involved with a bounding box etc)
qry <- "SELECT ST_Transform(table.geom, 4326) as geom FROM table"

# i then read as an sf object
pols <- st_read_db(conn, query=qry, geom="geom")

# and convert to GeoJSON
pols.js <- geojsonio::geojson_json(pols)

This is definitely reasonable for smaller datasets, but the bulk of processing time is in the conversion of simple features to geoJSON - geojsonio::geojson_json(pols)

How then do i extract geoJSON directly from PostGIS? And i presume this has to be done as a FeatureCollection? my attempts and reading have not got me to a solution:

attempt 1: simplistic ST_AsGeoJSON

qry1 <- "SELECT ST_AsGeoJSON(ST_Transform(geom,4326)) FROM table"
pols1 <- dbGetQuery(conn,qry1)
## no error messages but no display in Leaflet (incorrectly constructed geoJSON i assume, returned as a data.frame)

attempt 2: building featureCollection as per this answer

qry2 <- "SELECT json_build_object(
'type', 'FeatureCollection',

'features', json_agg(
    json_build_object(
        'type',       'Feature',
        'id',         gid,
        'geometry',   ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
    'properties', jsonb_set(row_to_json(table)::jsonb,'{geom}','0',false)
)
)
)
FROM table"
    pols2 <- dbGetQuery(conn,qry2)
    ## hangs and errors: "DETAIL:  Cannot enlarge string buffer containing 1073740688 bytes by 3167 more bytes."

attempt 3: another try as per this answer:

qry3 <- "SELECT row_to_json(f) As feature 
  FROM (SELECT 'Feature' As type, 
                ST_AsGeoJSON(ST_Transform(l.geom, 4326))::json As geometry, 
                row_to_json((SELECT l FROM (SELECT id, 'l' AS type) As l)) 
As properties 
  FROM table As l) As f"
pols3 <- dbGetQuery(conn,qry3)
## this gives a similar output to attempt 1 above but gives a warning "RS-DBI driver 
## warning: (unrecognized PostgreSQL field type json (id:114) in column 0)", which 
## i'm guessing is bcos i've got the syntax wrong. 

Can i return geoJSON directly from PostGIS in R without having to convert a spatial feature?

Answer 1

ok using attempt 1 above, i am manually building a geoJSON FeatureCollection until i work out how to make it work in SQL & R:

# query the data, transforming to geoJSON EPSG and doing some simplifying
qry <- "SELECT ST_AsGeoJSON(ST_Transform(ST_Simplify(geom,60),4326)) AS geom FROM table"

# execute query
pols2 <- dbGetQuery(conn,qry2)

# manually build geoJSON featureCollection
y <- paste0("{\"type\":\"Feature\",\"properties\":{},\"geometry\":",pols2$geom,"}")
y2 <- paste(y, collapse=',')
x <- paste0("{\"type\":\"FeatureCollection\",\"features\":[",y2,"]}")

Displaying x in Leaflet (in Shiny in R) works. The above method is < 0.5s for 10,000 features and c. 4secs for 100,000 features.