Apologies if this has been asked and answered before. I have tried searching
the archives, but I haven't found a keyword combination that brings up my issue
within a reasonably readable number of results.
I have a parent feature table and a child table (actually, an Oracle
materialized view, but it has all relevant spatial and non-spatial indices).
The geometry data is in the child table. When I execute a WFS getFeatureInfo
request that contains a geometry filter (WITHIN or DWITHIN), the SQL contains a
join between the two tables, but has no WHERE clause for the geometry filter.
Instead, it reads the entire join set and GeoServer does the geometry checking.
The set of five returned features from the WITHIN request does seem to be
correct; they all live inside the bounding box. Unfortunately, the feature
table in question has over 2.5 million rows, so the performance is miserable
(to put it mildly).
WMS requests do generate SQL with a WHERE clause containing an SDO_FILTER
condition.
We are using GeoServer 2.10.2 and Oracle 11.2. GeoServer is deployed to JBoss
EAP 6.4. The geometry column in the child table is always a point with SRID
8265. The Oracle plugin gt-jdbc-oracle-16.2.jar is in the geoserver WAR file.
We are using the ojdbc6.jar Oracle JDBC implementation.
The request:
<wfs:GetFeature count="20" outputFormat="application/gml+xml;
version=3.2" resolve="none" resolveDepth="*" resolveTimeout="600000"
resultType="results" service="WFS" startIndex="0" version="2.0.0"
xsi:schemaLocation="http://www.opengis.net/wfs/2.0
http://schemas.opengis.net/wfs/2.0/wfs.xsd http://www.opengis.net/gml/3.2
http://schemas.opengis.net/gml/3.2.1/gml.xsd "
xmlns:aixm="http://www.aixm.aero/schema/5.1";
xmlns:gml="http://www.opengis.net/gml/3.2";
xmlns:ogc="http://www.opengis.net/ogc";
xmlns:xlink="http://www.w3.org/1999/xlink";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>
<wfs:Query typeNames="aixm:VerticalStructure">
<fes:Filter>
<fes:Within>
<fes:ValueReference>aixm:timeSlice/aixm:VerticalStructureTimeSlice/aixm:part/aixm:VerticalStructurePart/aixm:horizontalProjection_location/aixm:ElevatedPoint/gml:pos</fes:ValueReference>
<gml:Envelope srsName="urn:ogc:def:crs:EPSG::4326">
<gml:lowerCorner>-115.274590 36.004546</gml:lowerCorner>
<gml:upperCorner>-115.088774 36.026682</gml:upperCorner>
</gml:Envelope>
</fes:Within>
</fes:Filter>
</wfs:Query>
</wfs:GetFeature>
The mapping file, abridged to just a few of the elements not involved in
linking or filtering:
<?xml version="1.0" encoding="UTF-8"?>
<as:AppSchemaDataAccess xmlns:as="http://www.geotools.org/app-schema";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://www.geotools.org/app-schema AppSchemaDataAccess.xsd">
<namespaces>
<Namespace>
<prefix>gml</prefix>
<uri>http://www.opengis.net/gml/3.2</uri>
</Namespace>
<Namespace>
<prefix>gmd</prefix>
<uri>http://www.isotc211.org/2005/gmd</uri>
</Namespace>
<Namespace>
<prefix>gco</prefix>
<uri>http://www.isotc211.org/2005/gco</uri>
</Namespace>
<Namespace>
<prefix>aixm</prefix>
<uri>http://www.aixm.aero/schema/5.1</uri>
</Namespace>
<Namespace>
<prefix>aimsaa</prefix>
<uri>urn:us:gov:dot:faa:aim:saa:5.1</uri>
</Namespace>
<Namespace>
<prefix>aimsua</prefix>
<uri>urn:us:gov:dot:faa:aim:saa:sua:5.1</uri>
</Namespace>
<Namespace>
<prefix>faa-shared</prefix>
<uri>urn:gov:us:dot:faa:aim:dataServices:shared</uri>
</Namespace>
<Namespace>
<prefix>faa-obst</prefix>
<uri>urn:gov:us:dot:faa:aim:dataServices:nbi:obstacle</uri>
</Namespace>
<Namespace>
<prefix>nasr</prefix>
<uri>http://www.aixm.aero/schema/5.1/extensions/FAA/NASR</uri>
</Namespace>
<Namespace>
<prefix>event</prefix>
<uri>http://www.aixm.aero/schema/5.1/event</uri>
</Namespace>
<Namespace>
<prefix>xlink</prefix>
<uri>http://www.w3.org/1999/xlink</uri>
</Namespace>
<Namespace>
<prefix>xsi</prefix>
<uri>http://www.w3.org/2001/XMLSchema-instance</uri>
</Namespace>
</namespaces>
<sourceDataStores>
<DataStore>
<id>datastore</id>
<parameters>
<Parameter>
<name>dbtype</name>
<value>Oracle</value>
</Parameter>
<Parameter>
<name>jndiReferenceName</name>
<value>${jndi.name.datasource}</value>
</Parameter>
<Parameter>
<name>Expose primary keys</name>
<value>true</value>
</Parameter>
</parameters>
</DataStore>
</sourceDataStores>
<targetTypes>
<FeatureType>
<schemaUri>http://localhost/files/SAA-Feature.xsd</schemaUri>
<schemaUri>http://localhost/files/event/version51e/Event_Features.xsd</schemaUri>
<schemaUri>http://localhost/files/faa_obstacle_features.xsd</schemaUri>
</FeatureType>
</targetTypes>
<typeMappings>
<FeatureTypeMapping>
<sourceDataStore>datastore</sourceDataStore>
<sourceType>VERT_STRUCT</sourceType>
<targetElement>aixm:VerticalStructure</targetElement>
<attributeMappings>
<AttributeMapping>
<targetAttribute>
aixm:VerticalStructure
</targetAttribute>
<idExpression>
<OCQL>strConcat('recid_',REC_ID)</OCQL>
</idExpression>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>
gml:identifier
</targetAttribute>
<sourceExpression>
<OCQL>UUID</OCQL>
</sourceExpression>
<ClientProperty>
<name>codeSpace</name>
<value>CODESPACE</value>
</ClientProperty>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>
gml:boundedBy
</targetAttribute>
<sourceExpression>
<OCQL>Expression.NIL</OCQL>
</sourceExpression>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>
aixm:timeSlice
</targetAttribute>
<sourceExpression>
<OCQL>REC_ID</OCQL>
<linkElement>aixm_VerticalStructureTimeSlice</linkElement>
<linkField>FEATURE_LINK</linkField>
</sourceExpression>
<isMultiple>true</isMultiple>
</AttributeMapping>
</attributeMappings>
</FeatureTypeMapping>
<FeatureTypeMapping>
<mappingName>aixm_VerticalStructureTimeSlice</mappingName>
<sourceDataStore>datastore</sourceDataStore>
<sourceType>VERT_STRUCT_TS_PART_VIEW</sourceType>
<targetElement>aixm:VerticalStructureTimeSlice</targetElement>
<attributeMappings>
<AttributeMapping>
<targetAttribute>
aixm:VerticalStructureTimeSlice
</targetAttribute>
<idExpression>
<OCQL>strConcat('ts.',REC_ID)</OCQL>
</idExpression>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>
FEATURE_LINK
</targetAttribute>
<sourceExpression>
<OCQL>VER_STRUCT_REC_ID</OCQL>
</sourceExpression>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>aixm:sequenceNumber</targetAttribute>
<sourceExpression>
<OCQL>TS_SEQ_NO</OCQL>
</sourceExpression>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>aixm:correctionNumber</targetAttribute>
<sourceExpression>
<OCQL>CORRECTION_NO</OCQL>
</sourceExpression>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>
gml:validTime/gml:TimePeriod
</targetAttribute>
<ClientProperty>
<name>gml:id</name>
<value>strConcat('vt.', REC_ID)</value>
</ClientProperty>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>
gml:validTime/gml:TimePeriod/gml:beginPosition
</targetAttribute>
<sourceExpression>
<OCQL>START_TIME</OCQL>
</sourceExpression>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>gml:validTime/gml:TimePeriod/gml:endPosition
</targetAttribute>
<sourceExpression>
<OCQL>END_TIME</OCQL>
</sourceExpression>
<ClientProperty>
<name>indeterminatePosition</name>
<value>END_TIME_IND_POS</value>
</ClientProperty>
<encodeIfEmpty>true</encodeIfEmpty>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>aixm:featureLifetime/gml:TimePeriod</targetAttribute>
<ClientProperty>
<name>gml:id</name>
<value>strConcat('lt.', REC_ID)</value>
</ClientProperty>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>aixm:featureLifetime/gml:TimePeriod/gml:beginPosition</targetAttribute>
<sourceExpression>
<OCQL>START_LIFE</OCQL>
</sourceExpression>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>aixm:featureLifetime/gml:TimePeriod/gml:endPosition</targetAttribute>
<sourceExpression>
<OCQL>END_LIFE</OCQL>
</sourceExpression>
<ClientProperty>
<name>indeterminatePosition</name>
<value>if_then_else(isNull(START_LIFE),Expression.NIL,if_then_else(isNull(END_LIFE),'unknown',Expression.NIL))</value>
</ClientProperty>
<encodeIfEmpty>true</encodeIfEmpty>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>aixm:interpretation</targetAttribute>
<sourceExpression>
<OCQL>INTERPRETATION</OCQL>
</sourceExpression>
</AttributeMapping>
<!-- Start of Vertical Structure Property Group -->
<AttributeMapping>
<targetAttribute>aixm:name</targetAttribute>
<sourceExpression>
<OCQL>NAME</OCQL>
</sourceExpression>
<encodeIfEmpty>true</encodeIfEmpty>
<ClientProperty>
<name>nilReason</name>
<value>NAME_NILR</value>
</ClientProperty>
<ClientProperty>
<name>xsi:nil</name>
<value>if_then_else(isNull(NAME_NIL),Expression.NIL,'true')</value>
</ClientProperty>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>aixm:type</targetAttribute>
<sourceExpression>
<OCQL>TYPE</OCQL>
</sourceExpression>
<encodeIfEmpty>true</encodeIfEmpty>
<ClientProperty>
<name>nilReason</name>
<value>TYPE_NILR</value>
</ClientProperty>
<ClientProperty>
<name>xsi:nil</name>
<value>if_then_else(isNull(TYPE_NIL),Expression.NIL,'true')</value>
</ClientProperty>
</AttributeMapping>
<AttributeMapping>
<targetAttribute>aixm:part/aixm:VerticalStructurePart/aixm:horizontalProjection_location/aixm:ElevatedPoint/gml:pos</targetAttribute>
<sourceExpression>
<OCQL>VERT_STRUCT_PART_GEO</OCQL>
</sourceExpression>
</AttributeMapping>
</attributeMappings>
</FeatureTypeMapping>
</typeMappings>
</as:AppSchemaDataAccess>
--------------------------------------------------
Mike Stein
Northrop Grumman Mission Systems
7575 Colshire Dr 7256W M/S C7W2
McLean VA 22102
703-556-1409 (ofc)
703-963-6654 (cell)
_______________________________________________
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
