Question

Inlinequerier stops without error message

  • 15 April 2014
  • 7 replies
  • 0 views
B
Badge
Hi!

 

 

I am using the inlinequerier to merge data  from 6 tables. In total there is around 11 milion point features that need to be merged.

 

 

The inlinequerier SQL is the following:

 

 

SELECT * FROM AQ_ADRESSES

 

LEFT OUTER JOIN AQ_CP_ADRESSES ON AQ_ADRESSES.IdAdr = AQ_CP_ADRESSES.IdAdr

 

LEFT OUTER JOIN AQ_ODONYMES ON AQ_ADRESSES.Seqodo = AQ_ODONYMES.Seqodo

 

LEFT OUTER JOIN AQ_REFERENTIEL ON AQ_ADRESSES.NoSqNoCivq = AQ_REFERENTIEL.NoSqNoCivq

 

LEFT OUTER JOIN AQ_MUNICIPALITES ON AQ_ADRESSES.CodeMun = AQ_MUNICIPALITES.CodeMun

 

LEFT OUTER JOIN AQ_ARRONDISSEMENTS ON AQ_ADRESSES.CodeArr = AQ_ARRONDISSEMENTS.CodeArr

 

 

After running for 4-5 hours the translation stops without any error message or warnings. When I run the same transformer with a subset of 100000 features, the result is correct.

 

Im using FME 2014 64bit on Windows 7 64bit with 8g RAM

7 replies

H
Badge +1
Wow scary amounts of data.

 

A couple of possible approaches.......

 

1. If you have access to a database program, import tables into the database. Set up views (queries) in the database (eg to join the tables). This approach is making the database to do all the hard work. You can still usually use the views (queries) in FME if required further down your data processing line eg using the Joiner and other Transformers. You may even at this point have created a new table containing all merged sub tables. Using FME alone to do the hard work will hammer your PC with such large amounts of data. I suspect your machine is running out of memory.

 

2. Point cloud Transformers in FME may (should) be optimised to handle huge datasets. Point clouds are essentially text files of XYZ coordinates. You may be able to utilise these Transformers somehow to help process your data. You may have to create some new fields first in your sub tables (eg XYZ).

 

..............

 

Hope this helps

 

Howard L'
B
Badge
Hi Howard,

 

 

I though the InlineQuerier was actually creating a SQLite db and that the join was performed only inside that db.

 

 

I imported the same tables in a spatialite db outside FME, and did the same query. It was successful in 3 minutes.

 

 

I dont understand why InlineQuerier would take 4 hours (and stop without error message) when the same query in SQLite took 3 minutes.

 

 

Any idea?

 

 

david_r
Rank
Userlevel 4
Hi,

 

 

sounds eerily familiar to some similar problems I experienced with the InlineQuerier in FME 2013. I suggest you report them to Safe, just as I did.

 

 

David
B
Badge
Hi David,

 

 

Thank you for your feedback. I have reported this issue to my local FME supplier from whom I receive technical support.

 

 

Does Safe has provided solution or any information when you did report a similar issue?
david_r
Rank
Userlevel 4
Hi,

 

 

unfortunately, Safe were unable to reproduce the issue. Also, I couldn't send them any test data, for various reasons, so I had to let it go.

 

 

It would be great if you could contribute to resolving this!

 

 

David
B
Badge
HI,

 

 

I have reported the issue to Safe and their explanation about memory problem is explained by the fact that the InlineQuerier puts all field (geometry and description) in a single blob field instead of recreating the table schema in the SQLite db. I don't think they will put their investigations any further. They suggest to run the transformer in 64bits with more memory. I am running on 64bit with 8Go of RAM but that seems not enough. I think the solution then would be to import the tables in SQLite myself and do the join into SQLite, or to use chained FeatureMergers in FME.
david_r
Rank
Userlevel 4
Hi,

 

 

that's very interesting, thanks for posting this followup.

 

 

I think you're on a good track in your thoughts about using SQLite like this. Although I would recommend you do your joins within SQLite as much as possible, it will probably be an order of magnitude quicker than using chained FeatureMergers. It will probably also be a lot easier on your memory.

 

 

David

Reply


Community Stats

28,723
Posts
109,511
Replies
36,560
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.