Hi,
Sorry in advance, for the extensive POST, but we don’t found an easiest way to explain it!
Scenario:
Using: geoserver 2.25.2 with geotools 31.2
Database: Oracle 19c
Java: 11
Problem:
When exporting KML through WMS service, we’re facing heavy queries on oracle database, because geoserver is not considering the bbox sent in the request.
Example:
…/geoserver/OSP/wms?service=WMS&version=1.1.0&request=GetMap&layers=OSP%3ALINES&BBOX=-963904.28267229%2C4955818.584451%2C-963678.25599103%2C4955934.285031&WIDTH=1514&HEIGHT=775&srs=EPSG%3A3857&styles=&format=application%2Fvnd.google-earth.kml
Resulting query to obtain a “lookAt” of KML inside “Folder” tag:
SELECT SDO_AGGR_MBR(GEOM), SDO_AGGR_MBR(BOX_GEOM), SDO_AGGR_MBR(LABEL_GEOM)
FROM MYSCHEMA.V_GS_LINES
Resulting problem:
As you can see, this query is going to fetch the entire table referenced on V_GS_LINES, to perform the SDO_AGGR_MBR for all the geometry columns. This is super overkill, because we’ve millions of records on this table.
KML output:
Attached KML of this example: MYSCHEMA-LINES.kml (8.1 KB)
We’re not experts on KML 
, but we’re expecting the following on the output:
- The “LookAt” of “Placemark” must represent the bbox of the feature
- The “LookAt” of “Folder” must represent the bbox of the “Placemark”
- The “LookAt” of “Document” must represent the bbox of the “Folder”
But, what we see when exporting is the following:
- The “LookAt” of “Placemark” is - OK
- The “LookAt” of “Folder” is pointing to the center of Portugal (in our case, probably because the query generated, is getting the MBR of all the V_GS_LINES features) - NOK
- The “LookAt” of “Document” seems to be pointing to the original request bbox, not showing the entire features. (for us this could be the behavior) - NOK
KML structure example:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<kml ....>
<Document>
<LookAt>
<longitude>-8.657884285410205</longitude>
<latitude>40.6160733058264</latitude>
<altitude>238.26560720816633</altitude>
<heading>0.0</heading>
<tilt>0.0</tilt>
<range>192.53110805327475</range>
<altitudeMode>clampToGround</altitudeMode>
</LookAt>
<Folder>
<name>MYSCHEMA:LINES</name>
<LookAt>
<longitude>-7.940855119558194</longitude>
<latitude>39.35544189442219</latitude>
<altitude>694315.7492837217</altitude>
<heading>0.0</heading>
<tilt>0.0</tilt>
<range>561043.543442022</range>
<altitudeMode>clampToGround</altitudeMode>
</LookAt>
<Placemark id="NODES.4542733">
<name>ARMVerdemilho >> PTEsteiroMedela (192,64m)</name>
<LookAt>
<longitude>-8.657990952662047</longitude>
<latitude>40.61705045026257</latitude>
<altitude>204.95134092055306</altitude>
<heading>0.0</heading>
<tilt>0.0</tilt>
<range>165.61143350397117</range>
<altitudeMode>clampToGround</altitudeMode>
</LookAt>
.....
</Placemark>
</Folder>
</Document>
</kml>
Our code analysis:
When exporting, the Geoserver calls the “LookAtDecoratorFactory” class, to get the “LookAt” of the “Folder” tag:
class LayerLookAtDecorator implements KmlDecorator {
@Override
public Feature decorate(Feature feature, KmlEncodingContext context) {
-----> Envelope bounds = context.getCurrentLayer().getBounds();
LookAt lookAt = buildLookAt(bounds, context.getLookAtOptions(), false);
feature.setAbstractView(lookAt);
return feature;
}
}
Reaching Geotools class “JDBCDataStore”, to build the bbox for the multiple geometries:
void buildEnvelopeAggregates(SimpleFeatureType featureType, StringBuffer sql) {
// walk through all geometry attributes and build the query
for (AttributeDescriptor attribute : featureType.getAttributeDescriptors()) {
if (attribute instanceof GeometryDescriptor) {
String geometryColumn = attribute.getLocalName();
-----> dialect.encodeGeometryEnvelope(featureType.getTypeName(), geometryColumn, sql);
sql.append(",");
}
}
sql.setLength(sql.length() - 1);
}
Calling the “OracleDialect” class that calculates the “SDO_AGGR_MBR”:
@Override
public void encodeGeometryEnvelope(String tableName, String geometryColumn, StringBuffer sql) {
sql.append("SDO_AGGR_MBR(");
encodeColumnName(null, geometryColumn, sql);
sql.append(")");
}
It calculates the bounds, according to the geoserver layer bounds (discarding original bbox), instead of only calculate the bounds of the features that the KML layer has.
So, our question is:
- Why the order of generating the “LookAt” tags while building KML are not the following?
- Getting placemarks
- Getting “Folder/Layer” bbox based on the mbr of all placemarks inside
- Getting “Document” bbox based on the mbr of all folders inside
- In this way, point 2 and 3 can be done on server-side instead of database-side
KML Documentation:
