Hi Walter
Le 25/03/2019 à 01:18, wambac...@posteo.de a écrit :
select st_centroid(unnest(st_clusterwithin(geom,0.00045))) geom
from assoc3_house
This query is used in a sql-layer (WFS) and gives reasonable results. :)
BUT: the performance is very, very poor. Tablesize is about 460.000
nodes and there is a spatial index on geom. *Runtime 3-4 minutes.*
In PSQL the same query (of course with the corrisponding bbox) is
running in 2-3 seconds giving the same results.
Have you rewritten the query, or have you copy/pasted it from
GeoServer's log ?
Geoserver wraps the query inside a subquery and then applies the
bounding box's where clause on the subquery. Maybe this is what causes
the different behaviour.
Note that if you do :
select st_centroid(unnest(st_clusterwithin(geom,0.00045))) geom from
assoc3_house where geom && bbox
you will filter on the base geometry and therefore use your spatial
index on the "geom" column.
But if you do :
SELECT * FROM (select
st_centroid(unnest(st_clusterwithin(geom,0.00045))) geom from
assoc3_house) AS vtable WHERE geom && bbox
you will filter on the centroid geometry which is the subqueries output
and therefore NOT the index on the geom column.
Both queries wont give exactly the same results, for clusters that don't
entirely lie within your bbox for instance.
Cheers !
--
Arnaud
_______________________________________________
Geoserver-users mailing list
Please make sure you read the following two resources before posting to this
list:
- Earning your support instead of buying it, but Ian Turton:
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines:
http://geoserver.org/comm/userlist-guidelines.html
If you want to request a feature or an improvement, also see this:
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
