Solved

Convert SQL WHERE clause to Tester test clauses

  • 13 October 2023
  • 11 replies
  • 128 views

Userlevel 4
Badge +17

Hi everyone,

 

I have an Excel file containing SQL WHERE clauses used as Definition Queries in an ArcGIS project and I want to use the same SQL WHERE clauses in my workspace to create subsets of my data.

 

Is there a way to "convert" these SQL clauses to Tester test clauses? I managed to do a lot with some StringReplacers, to the point where regular (att1= 'this') and list clauses (att2 IN ('this', 'that')) are covered, but I also have a lot of LIKE clauses (att3 LIKE 'th%') and I can't seem to get these translated to Tester's Begins With .

 

I know about the capabilities of the FeatureReader's Constraints, but in my analysis I need both the entire dataset I am querying AND the SQL-clause-derived-subset. And I want to prevent reading in the data twice, since that slows down my workspace a lot.

 

Thanks in advance everyone!

icon

Best answer by geomancer 19 October 2023, 16:31

View original

11 replies

Userlevel 2
Badge +21

Could the InlineQuerier help you with this issue?

 

https://docs.safe.com/fme/html/FME-Form-Documentation/FME-Transformers/Transformers/inlinequerier.htm

 

Maybe it is easier than transforming the queries into tester clauses.

Userlevel 5
Badge +40

Have you looked at the SQLExecutor? You can use Case expressions to create extra variables from the where clauses from your Excel file.

Make sure the where clauses are written as FME expects them (like enclosing field names in double quotes).

In FME add the where clauses to a list, so only one feature triggers the SQLExecutor.

Then add case expressions to the SQL Statement, like

case when @Value(WhereClauses{0}.WhereClause) then 'True' end as TEST0

In my tests this did not work in combination with select *, so you will have to explicitly name the fields to read. Also explicitly add the geometry field if you have one, and expose the attributes.

SQLExecutor_Case_whenAfterwards you can simply test on the extra attributes (like TEST1 = 'True').

Userlevel 4
Badge +17

Could the InlineQuerier help you with this issue?

 

https://docs.safe.com/fme/html/FME-Form-Documentation/FME-Transformers/Transformers/inlinequerier.htm

 

Maybe it is easier than transforming the queries into tester clauses.

Thanks for the reply. I think the InlineQuerier might indeed be the solution, but I can't seem to figure out how to extract the clause-value from the Excel file and implement it dynamically in the InlineQuerier.

Userlevel 4
Badge +17

Have you looked at the SQLExecutor? You can use Case expressions to create extra variables from the where clauses from your Excel file.

Make sure the where clauses are written as FME expects them (like enclosing field names in double quotes).

In FME add the where clauses to a list, so only one feature triggers the SQLExecutor.

Then add case expressions to the SQL Statement, like

case when @Value(WhereClauses{0}.WhereClause) then 'True' end as TEST0

In my tests this did not work in combination with select *, so you will have to explicitly name the fields to read. Also explicitly add the geometry field if you have one, and expose the attributes.

SQLExecutor_Case_whenAfterwards you can simply test on the extra attributes (like TEST1 = 'True').

If I understand the documentation correctly the SQLExecutor executes queries against a database, but I am trying to execute them against features that are already in my workspace. Therefore I don't think the SQLExecutor is of help for me. Good looking out with the part about double quotes, I would have missed that for sure.

Userlevel 5
Badge +40

Have you looked at the SQLExecutor? You can use Case expressions to create extra variables from the where clauses from your Excel file.

Make sure the where clauses are written as FME expects them (like enclosing field names in double quotes).

In FME add the where clauses to a list, so only one feature triggers the SQLExecutor.

Then add case expressions to the SQL Statement, like

case when @Value(WhereClauses{0}.WhereClause) then 'True' end as TEST0

In my tests this did not work in combination with select *, so you will have to explicitly name the fields to read. Also explicitly add the geometry field if you have one, and expose the attributes.

SQLExecutor_Case_whenAfterwards you can simply test on the extra attributes (like TEST1 = 'True').

You are right the SQLExecutor reads features from a database. You would have to modify your workspace to accomodate the SQLExecuter.

Userlevel 2
Badge +21

Thanks for the reply. I think the InlineQuerier might indeed be the solution, but I can't seem to figure out how to extract the clause-value from the Excel file and implement it dynamically in the InlineQuerier.

Being honest, I have never used that transformer. But, if you open de SQL Query parameter, it should offer you the excel attributes to build the query:

 

imageThere is a section called "Database Tables" that should contain the attributes of the incoming features.

Userlevel 5
Badge +40

Thanks for the reply. I think the InlineQuerier might indeed be the solution, but I can't seem to figure out how to extract the clause-value from the Excel file and implement it dynamically in the InlineQuerier.

As far as I have seen, it is not possible to use dynamic SQL in the InlineQuerier.

Userlevel 5
Badge +40

It is possible, with a main workspace containing an InlineQuerier and User Parameters, and a runner workspace containing a WorkspaceRunner.

 

Sample main workspace with an InlineQuerier:

imageSample runner workspace reading the SQL Where clauses from Excel, and passing them to the main workspace:

imageThe runner workspace passes the Where clauses to published User Parameters in the main workspace, where they become part of the queries that are executed.

 

You can test the main workspace by entering the Where clauses directly in the User Parameters.

 

--------------------

(Note: I wanted to test what happens when the InlineQuerier in the main workspace contains 4 queries, and only 3 where clauses are passed. In that case the main workspace will fail. Therefore I added a 4th query in the runner workspace with value 1=0, which results in a query that returns no features.)

Userlevel 4
Badge +17

It is possible, with a main workspace containing an InlineQuerier and User Parameters, and a runner workspace containing a WorkspaceRunner.

 

Sample main workspace with an InlineQuerier:

imageSample runner workspace reading the SQL Where clauses from Excel, and passing them to the main workspace:

imageThe runner workspace passes the Where clauses to published User Parameters in the main workspace, where they become part of the queries that are executed.

 

You can test the main workspace by entering the Where clauses directly in the User Parameters.

 

--------------------

(Note: I wanted to test what happens when the InlineQuerier in the main workspace contains 4 queries, and only 3 where clauses are passed. In that case the main workspace will fail. Therefore I added a 4th query in the runner workspace with value 1=0, which results in a query that returns no features.)

Whoa this is nice, thank you! Wasn't expecting another update on this thread. I really appreciate you going the extra length.

I guess your username checks out! Have a great weekend.

Badge +7

Just to add to the best answer by Geomancer - you can also wrap the InlineQuerier inside a custom transformer and supply user parameters that way instead of having to set off a workspace runner.

Badge +7

Just to add to the best answer by Geomancer - you can also wrap the InlineQuerier inside a custom transformer and supply user parameters that way instead of having to set off a workspace runner.

Scrap that - wrapping it in a custom transformer doesn’t work when passing in an attribute unfortunately.

The workaround I’ve used is to use a PythonCaller to roughly convert SQL to a python expression. This will only work for simple expressions but it’s good enough for my use-case.

import fme
import fmeobjects

class FeatureProcessor(object):
def __init__(self):
pass

def input(self, feature):
# Extract the SQL WHERE clause from the user parameter
where_clause = feature.getAttribute('SQL Where Clause') #Add your WHERE Clause attribute here

# Build a function to evaluate the WHERE clause
def evaluate_where_clause(feature, clause):
# Use regex to split the clause into tokens while keeping parentheses and quotes
tokens = re.findall(r'(\b\w+\b|[><=!]+|["\'][^"\']*["\']|[()])', clause)
#tokens = clause.split() # need spaces between operators and att names/values
expression = ""
i = 0
while i < len(tokens):
token = tokens[i]

#Convert the SQL operators to python syntax
if token.upper() in ['AND', 'OR']:
expression += f" {token.lower()} "
elif token == '=':
expression += " == "
elif token == '!=':
expression += " != "
elif token in ['<', '>', '<=', '>=']:
expression += f" {token} "

# Check if the token is an attribute or a value
else:
#This is a value and is wrapped in a single '
if token.startswith("'") and token.endswith("'"):
expression += token

#This is an attribute and is wrapped in a double "
elif token.startswith('"') and token.endswith('"'):
attribute_name = token.strip('"')
attribute_value = feature.getAttribute(attribute_name)

if isinstance(attribute_value, str):
expression += f"'{attribute_value}'" # string expression e.g. "Att" = 'Apples' (requires quotes)
else:
expression += f"{attribute_value}" # numerical expression e.g. "Att" >= 2 (no quotes)
i += 1
# Evaluate the constructed expression
#print(expression)
return eval(expression)

# Evaluate the feature against the WHERE clause
if evaluate_where_clause(feature, where_clause):
# If the feature passes the WHERE clause, output it
self.pyoutput(feature)

def close(self):
pass

 

Reply