requesting all glaciers kills the server

smnorris commented 2 years ago

Not an issue with fwapgr but I'll file this here for now, from pgfs log:

INFO[0298] Query                                         args="[]" fields.time=2.772108562s pid=92131 rowCount=10000 sql="SELECT ST_AsGeoJSON( ST_Transform( (\"geom\")::geometry, 4326)  ) AS _geojson , \"waterbody_poly_id\",\"watershed_group_id\",\"waterbody_type\"::text,\"waterbody_key\",\"area_ha\",\"gnis_id_1\",\"gnis_name_1\"::text,\"gnis_id_2\",\"gnis_name_2\"::text,\"gnis_id_3\",\"gnis_name_3\"::text,\"blue_line_key\",\"watershed_key\",\"fwa_watershed_code\"::text,\"local_watershed_code\"::text,\"watershed_group_code\"::text,\"left_right_tributary\"::text,\"waterbody_key_50k\",\"watershed_group_code_50k\"::text,\"waterbody_key_group_code_50k\"::text,\"watershed_code_50k\"::text,\"feature_code\"::text,\"wscode_ltree\"::text,\"localcode_ltree\"::text FROM \"whse_basemapping\".\"fwa_glaciers_poly\"     LIMIT 10000;"
DEBU[0298] Database query result: 10000 rows in 2.774296033s
Killed

smnorris commented 2 years ago

Playing with this request, the precision can be reduced to get more features but the web server still kills pgfs at about 8700 features. This works, and the resulting json is about 110M: curl -o glaciers.geojson "https://features.hillcrestgeo.ca/fwa/collections/whse_basemapping.fwa_glaciers_poly/items.json?limit=8675&precision=5" But the web server (1G RAM) requires about 70% memory to serve up the data. Requesting 8700 features fails.

Not sure if there is a workaround other than upping the resources for the web server (or setting LimitMax config param to ~5000 features)

smnorris commented 1 year ago

Note that requesting the entire collection whse_basemapping.fwa_watershed_groups_poly will have the same result.

joethorley commented 1 year ago

A reprex using fwapgr

library(fwapgr)
library(tibble)

fwa_query_collection("whse_basemapping.fwa_glaciers_poly", limit = 4000, nocache  = TRUE)
#> Simple feature collection with 4000 features and 24 fields
#> Geometry type: MULTIPOLYGON
#> Dimension:     XY
#> Bounding box:  xmin: -138.9438 ymin: 49 xmax: -115.0322 ymax: 60
#> Geodetic CRS:  WGS 84
#> # A data frame: 4,000 × 25
#>      area_ha blue_line…¹ featu…² fwa_w…³ gnis_…⁴ gnis_…⁵ gnis_…⁶ gnis_…⁷ gnis_…⁸
#>  *     <dbl> <chr>       <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>  
#>  1 0.0000479 <NA>        GD1230… <NA>    <NA>    <NA>    <NA>    <NA>    <NA>   
#>  2 0.0000466 <NA>        GD1230… <NA>    <NA>    <NA>    <NA>    <NA>    <NA>   
#>  3 0.0000416 <NA>        GD1230… <NA>    <NA>    <NA>    <NA>    <NA>    <NA>   
#>  4 0.0000403 <NA>        GD1230… <NA>    <NA>    <NA>    <NA>    <NA>    <NA>   
#>  5 0.0000378 <NA>        GD1230… <NA>    <NA>    <NA>    <NA>    <NA>    <NA>   
#>  6 0.0000378 <NA>        GD1230… <NA>    <NA>    <NA>    <NA>    <NA>    <NA>   
#>  7 0.0000328 <NA>        GD1230… <NA>    <NA>    <NA>    <NA>    <NA>    <NA>   
#>  8 0.0000327 <NA>        GD1230… <NA>    <NA>    <NA>    <NA>    <NA>    <NA>   
#>  9 0.0000239 <NA>        GD1230… <NA>    <NA>    <NA>    <NA>    <NA>    <NA>   
#> 10 0.0000239 <NA>        GD1230… <NA>    <NA>    <NA>    <NA>    <NA>    <NA>   
#> # … with 3,990 more rows, 16 more variables: gnis_name_3 <chr>,
#> #   left_right_tributary <chr>, local_watershed_code <chr>,
#> #   localcode_ltree <chr>, waterbody_key <chr>, waterbody_key_50k <chr>,
#> #   waterbody_key_group_code_50k <chr>, waterbody_poly_id <dbl>,
#> #   waterbody_type <chr>, watershed_code_50k <chr>, watershed_group_code <chr>,
#> #   watershed_group_code_50k <chr>, watershed_group_id <dbl>,
#> #   watershed_key <chr>, wscode_ltree <chr>, geometry <MULTIPOLYGON [°]>, and …

fwa_query_collection("whse_basemapping.fwa_glaciers_poly", limit = 8000, nocache  = TRUE)
#> Error in `get_request()` at pgfeatureserv/R/collection-features.R:73:2:
#> ! API request failed [502]: <html>
#> <head><title>502 Bad Gateway</title></head>
#> <body>
#> <center><h1>502 Bad Gateway</h1></center>
#> <hr><center>nginx/1.18.0 (Ubuntu)</center>
#> </body>
#> </html>
#> .

#> Backtrace:
#>     ▆
#>  1. └─fwapgr::fwa_query_collection(...)
#>  2.   └─pgfeatureserv::pgf_collection_features(...) at fwapgr/R/query-collection.R:35:2
#>  3.     └─pgfeatureserv:::get_request(url = url, user = user, verbose = verbose) at pgfeatureserv/R/collection-features.R:73:2
#>  4.       └─pgfeatureserv:::chk_response_status(resp) at pgfeatureserv/R/get.R:13:2
#>  5.         └─chk::abort_chk(glue("API request failed [{cd}]: {msg}")) at pgfeatureserv/R/chk.R:7:2
#>  6.           └─chk::err(..., n = n, tidy = tidy, .subclass = "chk_error", call = call)
#>  7.             └─rlang::abort(msg, class = .subclass, !!!args[named], call = call)

^{Created on 2022-11-27 with reprex v2.0.2}

smnorris commented 1 year ago

FYI, I've filed this https://github.com/CrunchyData/pg_featureserv/issues/129

But bumping up the web server resources just a bit will fix the issue, I might go ahead with that.

smnorris commented 1 year ago

Bumped to 2G of memory, looks good to me.

joethorley commented 1 year ago

Yes fwa_query_collection("whse_basemapping.fwa_glaciers_poly", limit = 8000, nocache = TRUE) now works.

joethorley commented 1 year ago

And can query up to the full 10000.

poissonconsulting / fwapgr

requesting all glaciers kills the server #64