1

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?

  • Did you try to run the queries in the database itself? And what would be the output then? (use LIMIT 10 to try it in a subset). And I can't understand wheter you mean Leaflet or R to display your data in. – tilt Aug 22 '17 at 14:23
  • that would be in Leaflet via a Shiny app (in R). I know this seems a long way around and perhaps i should look to doing things directly in javascript, perhaps in time. I shall try directly into the database. – Sam Aug 22 '17 at 14:52
  • attempt 3 inside pgAdmin: imgur.com/a/iWBtr – Sam Aug 22 '17 at 15:19
  • Can you figure out what is wrong with pols1 in your first try? That seems like the obvious way. Maybe you just need to paste the data frame together with newlines or something? – Spacedman Aug 22 '17 at 17:12
  • in pgAdmin, attempt1 returns as st_asgeojson text, in exactly the same format as the dataframe returned in R. Which doesn't seem right, especially as the image above is returned as json. – Sam Aug 23 '17 at 7:39
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.