Do you have any ability to create temporary tables in the environment that your table with geometry is in? In the past when i've had to do similar I found the quickest way is to create a temp table with your 200 features, then an sql exectutor to join this table with your geometry table to retrieve the geometry.

 

(I've presumed your 200 features and geometry aren't already in the same environment so you can't simply use an sql creator?)

SQLExecutor to pull just those records you need and then FeatureJoiner. That would mean 1 query per feature though, definately preferable over reading 2.5 million but maybe still too much of an overhead?

That would be my first idea as well. Executing 200-and-something queries shouldn't be too bad, especially if the fields referenced in the WHERE-clause are indexed.

Will SQLExecutor merge the geometry as well ?

Depends, but you can also cast the geometry into e.g. WKB in the SELECT-statement and then retrieve the geometry in the workspace using a GeometryReplacer. Exactly how you do it depends on if you're using e.g. SDE, SQL Server Spatial, PostGIS, etc.

Example joining using the SQLExecutor:

select a.*, st_asbinary(b.shape) as shape_wkb
from mysmalltable as a
join mylargetabe as b on a.key1 = b.key1 and a.key2 = b.key2

You should then get an attribute shape_wkb (or SHAPE_WKB in Oracle) that you can pass into the GeometryReplacer.

As long as your join keys are indexed, this should be very efficient. Just be aware that both tables must reside in the same database for this to work.

select a.*, st_asbinary(b.shape) as shape_wkb
from mysmalltable as a
join mylargetabe as b on a.key1 = b.key1 and a.key2 = b.key2

Hi David.

That's not T-SQL. But luckily the STAsBinary() function do exist in T-SQL.

So what you're saying, is that FME will recognize such a construct, and automagically insert this as the new feature geometry ?

Because my features (w/o geometry) are not in the database, they're extracted from a spreadsheet. I need to insert a geometry on them from the database.

Hi, you're right! Since I don't know what backend you're using, it's a general example off the top of my head, untested ;-)

In this case the geometry will get back to FME as a regular attribute containing binary data (BLOB), you'll have to use the GeometryReplacer after the SQLExecutor to transform it back to an FME geometry.

Apparently there are cases where the SQLExecutor can recognize a geometry column by itself, but the exact requirements are unclear to me, so I prefer going the explicit route. Casting the geometry back and forth to WKB does have a small performance penalty, however, but for 200-something records I don't think it should be very noticable.

select a.*, st_asbinary(b.shape) as shape_wkb
from mysmalltable as a
join mylargetabe as b on a.key1 = b.key1 and a.key2 = b.key2

Hi David.

Actually it turned out to be much simpler than using WKB.

The last dropdown in the parameters dialog (Combine Geometry) allows the setting "Result Geometry Only", which was exactly what I needed. I then just had to issue a "SELECT TOP 1  geometry_column] FROM ... WHERE ..." to have the resulting geometry be attached to my feature.

No need for additional conversions and exposed attributes. Neat!

Thanks for your help.

Excellent, thanks for the tip!