I found this wonderful piece of informatjon in the description of ArcSDE reader (http://docs.safe.com/fme/html/FME_ReadersWriters/Default.htm#sde30/sde30.htm%3FTocPath%3DFME%20Readers%20and%20Writers%20(formats%20supported%20by%20FME%202014)%7CEsri%20ArcSDE%20Reader%2FWriter%7C_____0):
The SDE reader can also perform multi-table join queries, thereby exploiting the full power of the underlying RDBMS.
Cool... I wanna do that, and most likely either the ArcSDEQuerier or ArcSDE reader, whichever is most convenient.
But I can't seem to wrap my head around the syntax. Composing a valid JOIN statement in SQL should be fairly straightforward, but that doesn't really seem to fit into the concept of WHERE Clause. Or I cant figure it out...
Problem description: I have a huge ArcSDE database. I and I want to extract all data from table A, and a subset of information from table B, only extracting those whose ROUTE_ID values match ROUTE_ID in table A. In my head, this should translate to a very straightforward SQL:
SELECT * FROM A, B
WHERE A.ROUTE_ID = B.ROUTE_ID
And FME ArcSDE reader takes care of anything up to and including the "WHERE", so i should input the "A.ROUTE_ID = B.ROUTE_ID" - part where it says "WHERE Clause".
Apparently not. What am I missing here? Or are there other, quicker ways to achieve what I want?
I've also tested extracting table A and then separately get the data from table A that satisfy my conditions. Both the Joiner and FeatureReader are more than happy to do what I want - but way to sloooow, reading one. single. feature. per. database-query. Table A is big enough to make this unacceptable slow. (Although I could do it over the week-end, I guess).
SQL executor / creator doesn't seem to support arcSDE, or at least I can't find that option.
Other options I've considered:
- Read A and B into, say, sqlite, then perform magic with the SQL executor.
- Read A and B into workspace, then use FeatureMerger. Which will work, but slooooooooow. This is probably what I'll do if I can't make any progress on the SQL magic, letting the server do the work it's supposed to be really good at doing.
