Skip to main content
Question

Building dynamic SQL

  • November 14, 2012
  • 3 replies
  • 55 views
S
Forum|alt.badge.img
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

 

 

 

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

3 replies

david_r
Celebrity
  • david_rCelebrity
  • November 14, 2012
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
Safer
Forum|alt.badge.img+21
  • fmelizardSafer
  • Safer
  • November 14, 2012
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
Forum|alt.badge.img
  • stian
  • Author
  • November 16, 2012
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

Community Stats

32,207
Posts
122,305
Replies
39,891
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.