We have a MSSQL 2012 View added as a layer in Geoserver 2.17.2 that performs
well with GetMap requests but times out with GetFeature requests.
We have tracked it down to the following Query sent from Geoserver to MSSQL:
SELECT top 1000 * FROM "schema"."MyTable_view"
WHERE (
"SHAPE".Filter(geometry::STGeomFromText('POLYGON ((<ListOfPoints>))', 4326))
= 1 AND
"SHAPE".STIntersects(geometry::STGeomFromText('POLYGON
((<ListOfPoints>))', 4326)) = 1 AND
"Field1" = 1234 AND
"Field1" IS NOT NULL
)
The view is just an inner join between two tables, both of which have a
variety of indexes including a proper spatial index on the "shape" field.
If I run this query manually against SQL, it basically runs indefinitely.
HOWEVER,
1. if I remove the "SHAPE".Filter() function in the where clause, it
returns results in less than a second.
2. If I remove the subsequent "Field1" query criteria from the where
clause, it returns results in less than a second. I assume this points to
the culprit here being that it is using the Field1 index instead of the
spatial index on the tables.
Is there a good solution to this problem? We are somewhat new to Geoserver,
so we may very well be doing something wrong. I have tried to find a way to
force the spatial query in the view with no luck. Any help would be much
appreciated!
Thanks,
Matt
_______________________________________________
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
