Skip to main content

I use a SQL creator on a Oracle Spatial database extracting around 40 columns.

I would want to score the fid of this extraction in order to use it in the where clause of a post-gis reader. This to avoid the post-gis reader to read all 1 000 000 records when I only need 300 of those.

How can I do this ? I did some research but seems I am using the wrong key words as I can't find it.

Thank you

 

 

Rather than using the regular reader, you could simply connect your SQLCreator to a FeatureReader, with the FID in the where-clause.


Hi @tsirkuse, I would use the DatabaseJoiner after the SQLCreator to read the PostGIS database.


A nice trick on how to create the where IN clause (something I learnt a long time ago from @erik_jan and I am still using) is:

If you have >1000 features (ID's) you will need to do the following:

  • Add a Counter starting with 0
  • Add an ExpressionEvaluator with floor(@Value(_count)/1000,0).
  • Add an Aggregator with group by on _result and concatenate the attributes you want in the where clause.

Hope this helps.

Itay


Hi @tsirkuse, I would use the DatabaseJoiner after the SQLCreator to read the PostGIS database.

Ah, tried that first, not possible cause what I want to join is the geometry. Except if there is a work around for that problem?


Rather than using the regular reader, you could simply connect your SQLCreator to a FeatureReader, with the FID in the where-clause.

I don't see how that helps me not reading half a milion post gis records I don't need.... Maybe I am missing something, but I really don't see it. I want to use the id's selected by my sqlcreator (oracle) in my post-gis reader. So I need to somehow store the values in a list that I can use in the where clause...


I've always found the most efficient way to do this is to write a temporary table in the POSTGIS database then use an sqlexecutor to return the matched records.

 

 


Ah, tried that first, not possible cause what I want to join is the geometry. Except if there is a work around for that problem?

Aha @tsirkuse, then you should use the FeatureReader. This transformer is the same as the DatabaseJoiner except that you can also do a spatial join..


I've always found the most efficient way to do this is to write a temporary table in the POSTGIS database then use an sqlexecutor to return the matched records.

 

 

I agree, in particular if there's a non-trivial number of records to join. Although it does depend on having sufficient rights.


I don't see how that helps me not reading half a milion post gis records I don't need.... Maybe I am missing something, but I really don't see it. I want to use the id's selected by my sqlcreator (oracle) in my post-gis reader. So I need to somehow store the values in a list that I can use in the where clause...

This is what I had i mind, it will prevent you from reading the entire postgis table and as long as the FID column is indexed it should be fairly quick:

Although if you have a "large" number of FIDs, then you should look into the suggestion from @egomm, it will scale much better (but requires that you have sufficient rights).


Reply