Skip to main content
  • EN

Looking for tips to improve performance when selecting rows from a database based on an incoming set of features.

 

 

For example, say I have 150 points going into an initiator port on a FeatureReader that then reads 4 tables/features from an Oracle SDE database.

 

 

Scenario 1: Spatial Filter is set to "Initiator Intersects Result"

 

While this does greatly improve performance over reading everything from all 4 tables/features, What else can I do to improve performance here?

 

Is there a way to send all of the features at once? OR generate a set of search envelopes to send to the featureReader?

 

(I tried creating a BoundingBoxAccumulator, but if the points are not close together, this will select too many of the 4 features)

 

 

Here is an example of the kind of output in the log file for each of the 150 points that are sent to the FeatureReader (note, I've change the lat/long):

 

Notice - Performing envelope query on the `GEODATABASE_SDE' reader, post-filtering spatial predicate in FME

2018-08-02 16:17:18| 56.1| 0.0|INFORM|The minimum (x,y) and maximum (x,y) bounds, respectively, of the search envelope being used are: (-50.0001, 10.0000) , (-50.00001, 10.0000)

 

 

 

Scenario 2: Select using unique IDs

 

In this case, each of the 150 points has a matching Unique ID in the 4 features to select from.

 

Does anyone have a smart way to build out long SQL queries to prefetch all of the features with matching unique IDs at the same time?

Hi @michaeltessella,

Scenario 1: Probably you can use the Aggregator to aggregate the points into a single MultiPoint feature, then perform spatial querying with the MultiPoint. In my experiences, it works for a PostGIS database. Hope it works too for your ArcSDE dataset.

Scenario 2: If the points have ID attribute corresponding to ID of records which should be extracted from the tables, just create an expression with the IN operator for setting the WHERE Clause parameter. Aggregator can also be used here. Have a look at the Attributes to Concatenate parameter in the Aggregator.

Hi @michaeltessella,

Scenario 1: Probably you can use the Aggregator to aggregate the points into a single MultiPoint feature, then perform spatial querying with the MultiPoint. In my experiences, it works for a PostGIS database. Hope it works too for your ArcSDE dataset.

Scenario 2: If the points have ID attribute corresponding to ID of records which should be extracted from the tables, just create an expression with the IN operator for setting the WHERE Clause parameter. Aggregator can also be used here. Have a look at the Attributes to Concatenate parameter in the Aggregator.
Aggregator works great with ArcSDE. Exactly what I was looking for, thanks!

 

 

Reply


Community Stats

30,162
Posts
114,480
Replies
37,807
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.