Question

Best way to read in only some attributes from all features

  • 24 June 2014
  • 6 replies
  • 10 views

Badge +8
I have a workspace with 3 feature classes being read from an ESRI SDE 9.3.1 geodatabase (Oracle 11g). There are about 440,000 features total and reading the features takes about 45 minutes while transforming only takes about 10. (I tested this by disconnecting all the readers from their initial transfomers so the workspace would end after the features were read in.) I only need a couple of attributes from each feature class so I was wondering if there is a way to only read in the attributes I need rather than all of them. The SQLCreator or FeatureReader both look promising or maybe even SQL Statement To Execute Before Translation (under Reader --> Parameters --> Advanced). Anybody have advice/expertise to point me in the right direction? It would be most appreciated. (Win7 64-bit, FME 2014, Build 14230, 32-bit).

 

 

Thanks,

 

 

Aaron

 

Albuquerque, NM

6 replies

Userlevel 3
Badge +13
Hi,

 

You are on the right track, I would use a SQL statement in a SQLCreator to let the db take the load.

 

Itay
Badge +8
Thanks, Itay. Having never used SQLCreator, how do I connect to the data? As mentioned, it's an ESRI SDE geodabase sitting on top of an Oracle 11g DB. Do I use Oracle Non-spatial or Oracle Spatial Object as my reader? I'm used to connecting to an ArcSDE reader (I'm a GIS guy, not an Oracle DBA) so I'm a bit in the dark as to how to proceed.

 

 

Aaron
Userlevel 4
Hi,

 

 

if you need the geometries I'm not convinced that the SQLCreator will make life any easier for you, as they're not supported "out of the box" unless you cast the to something like WKB (which has a performance penalty in itself).

 

 

The best long term solution would be to have your dba create spatial views in Oracle with only the necessary parameters, the use the regular GeoDb reader to access them.

 

 

That being said, 45 minutes to read 440k features sounds awfully slow. Unless you have a crazy amount of attributes I'm not so sure losing some of them is going to make a big difference. You might want to look into your db and network performance first of all.

 

 

David
Badge +8
David,

 

 

Thanks for your response. I don't need geometries in this case. In addition, we do have a crazy amount of attributes - 248 total attributes across the three feature classes (69, 103, and 76) - so you can see why I'd prefer to only read in the 6 that I need.

 

 

Aaron
Userlevel 4
Hi,

 

 

in that case the SQLCreator should be perfect. Just use the Oracle Non-Spatial data type and connect as usual.

 

 

Remember to fill in "Attributes to expose" in the SQLCreator with the attributes specified in your SELECT-statement.

 

 

See also the documentation (http://docs.safe.com/fme/html/FME_Transformers/Default.htm#Transformers/sqlcreator.htm).

 

 

David
Badge +8
Thanks Itay and David! Using the SQLCreator is so much faster.

 

 

Aaron

Reply