Add zeros in real numbers

Hello FME'ers,

This question-of-the-week comes from GIS/StackExchange; the first one I've taken from there I believe.

The question was asked by Jochen in Germany, who wanted to know how to carry out an INSERT on selected records only.

Q) I have a PostGIS table of, lets say, addresses, for example

id | adr            | geom
----------------------------
1  | Rathausplatz 1 | POINT
2  | Dorfstraße 2   | POINT
3  | Kirchenweg 3   | POINT

With FME I would like to insert features to my adresses table but only features that do not exists (based on non-equality of attribute adr), i.e. for incoming features

adr            | geom
----------------------------
Rathausplatz 1 | POINT
Dorfstraße 2   | POINT
Schlossallee 42| POINT
Elisenstraße 8 | POINT

only Schlossallee 42 and Elisenstraße 8 should be INSERTed, the first two should be rejected.

How to achieve this?

A) It's sort of an inverse update required here. Instead of updating a record that exists, we're inserting a record that doesn't. Sadly there isn't a set method for that. 

One way would be to set up the database with a unique ID constraint, pass every record as an INSERT, and let the database deal with the fallout of pre-existing IDs.

But in FME, to find if a record doesn't already exist, we must query the database. So what's the best way to do this?

One answer - that was accepted very quickly - suggested using a FeatureMerger:

My instinct is that this is not a good answer. It won't work very quickly because the FeatureMerger is an older (and slower) transformer, plus why would you want to read all of your data into the workspace unnecessarily?

Having earlier this week updated our main Performance Tuning article on the Knowledgebase, I was in a good position to try out some of the suggested techniques. 

I set up a scenario where I have a table on PostGIS (PostalAddress) and I have the same dataset in Geodatabase. I want to find which addresses in the Geodatabase already exist in PostGIS.

Then I tried a variety of solutions...

The first two methods I tried involve - as in the accepted answer - reading all the data and doing a match

Obviously we keep the unmerged records and then do the INSERT. There aren't any unmerged records here because my two datasets are the same, but that's OK. I just wanted to evaluate the process.

Firstly I used the FeatureMerger, then I tried with the FeatureJoiner.

But are there any other methods?

Since we're using a database, I next tried to use SQL to solve the requirement.

My instinct told me a SQLExecutor would actually be the best solution, to create a count of how many records match. So each Geodatabase record sends the query:

select count(*) as mycount from "public"."PostalAddress" where "PostalAddress"."PostalAddressId" = ''

Then I would test for mycount >= 1 and drop those features.

Then we're absolutely not reading more data than necessary.

The other SQL method that occurred to me was to read data with a SQLCreator (rather than a reader)

select "PostalAddressId" from "public"."PostalAddress"

Then I wouldn't need to read the spatial component, just the match keys. I would follow this up with a FeatureMerger/Joiner, as above, to do the actual comparison.

Finally I tried two methods that compared records directly, without reading all of the PostGIS data.

The first of these is a FeatureReader with a WHERE clause

WHERE "PostalAddress"."PostalAddressId" = ''

The second of these is a DatabaseJoiner using matching keys

Table Key = "PostalAddress"."PostalAddressId" and Attribute Key = ''

The DatabaseJoiner is fine, because it has an "Unjoined" output port. Plus it has some nice optimizing parameters, where I can choose to read the remote data as a local cache to compare against.

That optimizing was important, as you'll see below.

The FeatureReader is awkward because it only tells us what IS matching, and we need what IS NOT matching. So after the FeatureReader I still need a second process to compare its output against the source, probably a FeatureMerger/Joiner again.

As mentioned, my datasets both consisted of the same 13,597 records. They would all match, so I figured it's a "worst case scenario". Let's see what I got (sorted in the order I tried them)...

OK. This really surprised me. The fastest results were the ones that read all the data from the PostGIS table and processed it locally:

Notes:

• The SQLCreator is probably faster than a reader because it doesn't need to include geometry. 
• The FeatureJoiner creates a join per match, so it wouldn't be helpful when it's not a 1:1 match
• The DatabaseJoiner was optimized to download the entire table into a cache before starting.

   

The worst results (the ones that put me in Agony City, as painter Bob Ross liked to say) were the ones that read the data selectively:

Notes:

• Here the DatabaseJoiner wasn't optimized manually, but probably did its own caching and optimization.
• The SQLExecutor and FeatureReader methods I let run for 10% of the records, then extrapolated.

So why were my instincts so wrong?

In training, tutorials, and the knowledgebase, we always emphasize that reading all of your database table into FME is excessive. Performance is a measure of useful work carried out in a certain time. If you're reading and processing excess records, then that's not useful work.

However, our performance tuning article also mentions two important facts:

• Individual queries to a database are very expensive
• Best Practice is to make full use of the available system resources

The solutions I thought would be best tried to minimize use of system resources, by making thousands of database queries. In reality I had more than enough system resources to download the entire table with a single query, and process it locally.

So just how expensive are individual queries? That depends on where your database sits, how it's indexed, and what network speed you have. I was calling out to a Postgres/PostGIS database on Amazon RDS. I'm not sure what region, but my guess is Oregon.

The SQLExecutor returning a count of each matching record, took 0.5 seconds per feature. That's not an FME overhead, it's a cost in waiting for the database to return a result; and it's actually way worse than it sounds.

Note: The same overheads could also apply to writing - so check out the options for chunk size (Features per Bulk Write) and Transaction Interval in your database writers.

And how much memory did I use reading all 13,597 features at once? Not much. With the Reader/FeatureMerger FME logged a maximum of 134MB. Remember, that's both data and whatever memory FME itself takes.

Sticking in Cloners and extrapolating a bit, plus with my machine spec, I figure I can consume 5m records before getting close to physical memory limits (15m before hitting the FME virtual memory limit).

Admittedly addresses have a minimal geometry load, but that's still a pretty good amount of features that my 8-year-old machine can cope with.

To summarize: I wasn't just wrong, I'm shocked at how wrong I was!

I did not realize quite how slow a single database query can be. Consider, there are only 86,400 seconds in a day. With overheads like above, I could easily spend all day waiting for the database to process just 100-200,000 queries!

I hadn't considered before that a bad design can perform better simply because it's being greedy with its use of resources. Finding that out was... well to quote Bob Ross again:

Obviously, your mileage may vary. But this showed me that even when you design for efficiency, you need to consider less efficient methods. So the two takeaways today are:

• Be bold with your use of system resources. 
  • There's no point in having GB of memory available and not using it
• Be wary of database queries. 
  • A single query - multiplied over lots of features - can be a crippling bottleneck in any translation

So there you go.

Today I cover a frequent issue: how to run FME from the command line, specifically with Windows Scheduler.

Question of the Week

This question was asked in Live Chat by a user with a problem with database connections and Windows Scheduler.

Q) In my workspace I use a writer that connects to SQL Server via a SQL Server account. In Workbench, the workspace runs and data is inserted. But run through Windows Task Scheduler, it fails with SQL Server issues. 

I've set the username and password in the writer, but in the log file it appears to be not set!

A) This type of issue appears quite often. In short: if a workspace works in Workbench, but fails in a different environment, it's 95% likely to be a permissions issue.

Let's look at a few sticking points and - in particular - this scenario, which is harder because there's a database connection involved.

Who Are You?

The absolute number one issue when running a workspace from a command line is the account used to do that. It's important to realize that "you" (the user logged in) is different to the default account that Windows uses when it starts a process automatically.

For example, your account might have permission to read a specific file, while the Windows account used by Task Scheduler does not. In that scenario, the workspace fails to run:

ERROR | [Format] Reader: Failed to open file [Filename] for reading

You fix that by either setting the account that Task Scheduler will use, or by giving that Windows account the same privileges that you have. Here's an answer to a question asked last year:

"Fixed by ensuring the account used to run the script in Task Scheduler had Full Control permissions on the folder containing the script, the script itself, and any folders/files the script touches. Previously my batch file was in a dropbox location. I relocated it to C: drive and it works perfectly."

Back to this question, the user mentioned that:

"I'm using a domain user account with privileges to run the task on the windows machine."

So it looks like the Windows account is not directly the issue here...

Handling Connections

Database (and web) connections add a layer of complexity, because account permissions must be provided in FME.

This article outlines the steps involved. To quote its opening passage:

"The default for database and web connections is that connections stored in the default location are available to the current user only. If connections need to be accessible to any user in an organization, it is necessary to move both the connection storage file and the public key file to a shared location, and make the required connections public."

So step one is to put connections in a shared location available to all users. You can choose (in Tools > FME Options) whether each connection is private to you, or public to everyone. If the account running the task is different, then it needs to be public.

However, that may not be enough. The Windows default account also needs to know where the shared location is. You do that by setting the registry, as explained by this article

For this situation, the registry must be set manually in HKLM, which is where FME.exe looks if the keys are not present in HKLU.

In short, make your connections public and then - depending on which account is being used - set the registry to point to the public connections file.

I wouldn't - as this user did - embed connection information, unless you are sure the workspace file is secure.

Anyway, when the user in question found these articles, they were able to resolve the problem. But are there other issues?

Related Issues

Yes, there are plenty of other issues but - unsurprisingly - they're mostly related to accounts and permissions.

• In this question, the user found that a scheduled task needs the same process priority as the Workbench user.
• In this question, the user found that you need to run the process "in" the same folder as FME_HOME
• For some formats, FME needs access to ArcGIS functionality, and in this question the user discovered that the scheduler account may not see ArcGIS the same way.
• In this question, the user found that you have to be specific about which .exe file to use when multiple FME versions are installed.
• In this question, the user found you have to log into the service account at least once (to create the AppData folders) and that network drives need the UNC path set (presumably the Windows account has different drives).
• In this question, the user found that you don't need to run a BAT file in the Scheduler; you can use the FME Command-Line directly.

...and I suspect there are plenty more.

Won't Get Fooled Again

To reiterate, the key for running a workspace under Windows Task Scheduler is to ensure the user running the task (which IS NOT the same as you) has sufficient privileges and access rights.

This includes read/write permissions, access to connections, network drive access, elevated priority, and the ability to locate dependent applications such as ArcGIS.

If a scheduled FME process fails, then think: permissions, permissions, permissions! 

Or switch to FME Server/Cloud, which is designed to automate solutions without these oddities!

Other Notable Questions

Let's see:

• Reading an Oracle View as a new feature type
  • @whkarto asks if you can add an Oracle View as a feature type in Workbench. A view is like a pre-processed set of data - a "virtual table". My belief is you can read a view; I've even heard of manually adding a metadata entry for the view. You can't spatially index a view, but Oracle will recognize and use indexing on the underlying table, though you get warnings and errors.
  • You might also get better results with a Materialized View, over a standard View.
• Excessive data returned in a StringSearcher regex
  • User @jayqueue asks why their regex returns a longer string than expected. @ebygomm solves the issue by noticing that the regex is "greedy". 
  • For those not in the know, a greedy regex searches for as long a string as possible. So if I searched my name (Mark Ireland) for a string beginning with "a" and ending in "r", I believe a non-greedy search would return "ar", but a greedy search would return "ark Ir" - i.e. extends the result as far as it can.

declare namespace gml="http://www.opengis.net/gml/3.2";
for $s in //gml:pos
return replace value of node $s with {
    for $v in fn:tokenize($s/text(), ' ')
    return fn:format-number(xs:double($v), '0.000000')
}

declare namespace gml="http://www.opengis.net/gml/3.2";
for $s in //gml:pos
return replace value of node $s with {
    for $v in fn:tokenize($s/text(), ' ')
    return if (fn:matches($v, '\.\d{6,}')) then $v
        else fn:format-number(xs:double($v), '0.000000')
}