• EN
Question

Building dynamic SQL

  • 14 November 2012
  • 3 replies
  • 15 views
S
Badge
Cheers!

 

 

I'm currently modelling a workflow that uses SQL creator for querying a PostGIS DB and a set of published paramteres in order to enable the user to make choices that will be passed on as predicates in the SQL string.

 

 

This works fine for most parameters (to/from date e.g.) but somehow I'm not able to figure out how to prompt for an input geometry file (e.g. a Shape file), extract the geometry and use the WKT ("POLYGON((XXXX))")  from this file in the SQLCreator. I've tried using the GeometryExtractor and this extracts the WKT, but how do I pass this on as a predicate in the SQL creator?

 

 

Regards,

 

 

Stian Aamot

 

 

 

3 replies

david_r
Rank
Userlevel 5
Hi Stian

 

 

perhaps you could consider replacing the SQLCreator with an SQLExecutor? That would make it simple to read and process the geometry before executing your SQL code with, the attribute containing the WKT as part of the SQL.

 

 

Håper dette gjør at du kommer litt videre ;-)

 

 

David
fmelizard
Rank
Userlevel 4
Badge +13
Hi Stian,

 

Take a look at a recent article posted on the FMEPedia site. This uses the WKT string from the GeometryExtractor and passes it into the SQLExecutor to do an ST_Relate on a set of features from a Postgis table.

 

 

http://fmepedia.safe.com/articles/How_To/How-to-use-native-spatial-database-SQL-commands-to-perform-spatial-queries-in-the-SQLExecutor-SQLCreator

 

 

 

Good luck,

 

Robyn Rennie

 

www.safe.com/supportrequest
S
Badge
Thak you for for your input! Very useful indeed and my workflow is now almost complete.

 

 

However theres one challenge left to solve for me. I'm trying to use a "Choice with Alias" published parameter in order to display a prompt to choose on of a total of four different input SQL queries that I intend to pass on to the executor as $(NAME_OF_PARAM). This does not work very well. All the queries are tested directly in the SQLExecutor transformer an works well but when passed on from a published parameter it fails. Any idea why?

 

 

Regards Stian

Reply


Community Stats

28,867
Posts
109,983
Replies
36,671
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.