Calculating extent times out

[rduivenvoorde]

Tried to put pg_featureserv in front of a view on some tables which results in say 15000000 (point) records.
(plan to only query them either by spatial or by temporal (indexed) filters)

Going into the html interface, I only get time outs because it tries to find the extent of the data:

SELECT ST_XMin(ext.geom) AS xmin, ST_YMin(ext.geom) AS ymin, ST_XMax(ext.geom) AS xmax, ST_YMax(ext.geom) AS ymax
FROM (SELECT coalesce( ST_Transform(ST_SetSRID(ST_Extent("geom"), 4326), 4326), ST_MakeEnvelope(-180, -90, 180, 90, 4326)) AS geom FROM "schema"."featureserv_test" ) AS ext;

There are proper spatial indexes on the underlying tables (of the view, only points), but finding the extent just takes too much time (couple of minutes)...

Is there a way to let pg_featureserv know of the extent, or can we skip this query?

[dr-jts]

The extent computation can be done more efficiently by using ST_EstimatedExtent. If a spatial index is in place, this is fast and accurate (otherwise, it's fast but may not be totally accurate). pg_tileserv uses this approach (and only computes the extent via table scan if the estimated extent is not available).

I will work on adopting this for pg_featureserv as well.

[rduivenvoorde]

Cool thanks that works! And I was even able to compile myself :-)

The query was actually already in the code:
https://github.com/CrunchyData/pg_featureserv/blob/master/internal/data/db_sql.go#L112

Also: if I am correct I had to change both sqlFmtExtentExact as sqlFmtExtentEst to have the sqlFmtExtentEst before actually using it?

Idea maybe to make this a configurable option?

[dr-jts]

Good to know that the estimated extent works for your use case.

Actually the plan is to have the core code run the EstimatedExtent query first, and then only the ExactExtent query if that doesn't produce an answer.

This will speed up extent calculation for large datasets, at the cost of some potential imprecision (both ways - larger and smaller). If this is a big issue, then perhaps a configuration option can be added to force ExactExtent if required.