Skip to main content
  • EN
Hi

 

 

I have a Spatial query that combines multiple tables in a postgis database. The individual tables consist of millions of records so breaking each one down with a spatial query vastly improves speed of execution. I have listed only the first part of the query here below: (there are three more join tables)

 

 

SELECT m.fid, m.featurecode, m.polygon, m.physicallevel, m.make, z.descriptivegroup, y.descriptivterm, x.theme

 

FROM

 

topographicarea as m

 

LEFT JOIN

 

(SELECT a.fid, array_to_string(array_agg(c.theme), ','::text) as theme

 

FROM topographicarea as a, topoarea_theme as c 

 

WHERE a.primary_key=c.fkey_topographicarea

 

AND a.polygon && ST_MakeEnvelope ( 245850, 642853, 245871, 642917, 27700 )

 

group by a.fid) as x

 

ON m.fid=x.fid

 

LEFT JOIN

 

(SELECT a.fid, array_to_string(array_agg(b.descriptiveterm), ','::text) as descriptiveterm

 

FROM topographicarea as a, topoarea_descriptiveterm as b

 

WHERE a.primary_key = b.fkey_topographicarea

 

AND a.polygon && ST_MakeEnvelope ( 245850, 642853, 245871, 642917, 27700 )

 

 

 

Questions

 

 

(1) How can I pass this query back to postgres every time the project is run so that the features extracted by this query are my source data?

 

 

(2) I want to pass in the bounding box (minx, miny, maxx, maxy) so that I can run the project from the command line:

 

 

      $ fme project.fmw

 

        --DestDataset c:\\data\\test.dwg

 

        --MINX "245850" 

 

        --MINY "642853"

 

        --MAXX "245871"

 

        --MAXY "642917"

 

 

So that when the project is run the ST_MakEnvelope () part of the sql can be replace with the values I pass in at runtime e.g:

 

 

ST_MakeEnvelope ( $(MINX). $,(MINY), $(MAXX), $(MAXY), 27700) 

 

 

 

Any help much appreciated!!!

 

 

Mike

 

 

Hi Mike,

 

 

The easieat way would be to contain the SQL query in a text file that is read and passed into the SQLExecutor.

 

This will enable you to edit and maintain the query outside the workbench.

 

For the second question, when you preform the query, have a look at how FME is passing it to the database in the log. Then just create your boudingbox coords parameters to pass to the SQLExecutor.

 

Hope this helps,

 

 

Itay
An oracle example:

 

MDSYS.SDO_RELATE(A.GEOMETRIE, mdsys.sdo_geometry(2003,90112,null,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array($(bbox_minx),$(bbox_miny),$(bbox_maxx),$(bbox_maxy))),'mask=ANYINTERACT querytype=WINDOW') = 'TRUE'
Hi,

 

 

If you want your select query to be the input to your workspace, have a look at the SQLCreator. It will act like a Reader but will enable you to specify your own select-block with joins, etc, just like above.

 

 

For the bounding box, you can create 4 published parameters that you reference in your SQLCreator. Example:

 

 

select *

 

from mytable a

 

where a.polygon && ST_MakeEnvelope ( $(MINX), $(MINY), $(MAXX), $(MAXY), 27700 )

 

 

David
Hey 

 

 

Thanks for the quick response! 

 

 

The SQL Creator was the one that worked for me.

 

 

Thanks,

 

 

Mike

Reply


Community Stats

30,099
Posts
114,259
Replies
37,731
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.