Skip to main content
Question

Use of WHERE Clause in Smallworld and SBS Reader

  • July 31, 2019
  • 4 replies
  • 162 views
L
Forum|alt.badge.img

Hello,

I use the standard FME Smallworld reader/writer and/or the SBS-plugin to read and write data from and to Smallworld 4.3. A lot of the read actions take a lot of time, due to the very large quantities of objects in our databases.

 

To make this process a lot more efficient, I want to make use of the 'WHERE Clause'- or 'Where'-function. The idea is, for example, to only read the water mains that belong to the network 'Distributie'.

I first searched this site for some info/advice on how to do this correctly. I found some info and a few examples (example: WHERE Clause: [Electricity] cable where Status = "Accepted"). I also consulted the 'SPATIALBIZ Plugin Usage Guide'. In that guide I found one example of a predicate (example: "gis.hotel where type = 'Hotel'; gis.min_road where road_type = 'A-Road'").

Not really knowing which predicate format is the correct one, I tried a few different predicates ("water_supply.ws_main_section where network = 'Distributie'" and [water_supply] ws_main_section where network = "Distributie"), but with no success. The readers continue to read all data in the database.

Can someone help me out?

Thanks!

Regards,

Luc

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.

4 replies

erik_jan
Contributor
Forum|alt.badge.img+23
  • erik_janContributor
  • Contributor
  • July 31, 2019
This is the section in the Spatialbiz Plugin user guide explaining the where clause for tables:Predicated Data Export

All feature types can be exported based on a selection predicate, defined as a feature parameter. The Export Predicate expects an SQL like statement which will be applied to the retrieval set to restrict the export results.

 

 

A very simplistic SQL syntax is supported with the following grammar (see the magik CASE_PARSER) for more information:

 

predicate

'(' <predicate> ')' |

'not' <predicate> |

<simple-predicate> <bin-op> <predicate> |

<simple-predicate>

bin-op

'and' |

'or'

simple-predicate

<field> <op> <value>

op

= | <> | > | < | >= | <= | like |

is | is null | is not null

String wildcards

% - Any number of unknown characters

 

_ - A single unknown character

 

Examples:

name= ‘Test’ and route is not null

L
Forum|alt.badge.img
  • lucvdberghe
  • Author
  • August 1, 2019

Hello @erik_jan,

Thanks for your answer.

Next to the WHERE tests I did on the entire workspace, I also tested this way of restrictive exporting => see attachments.

None of these tests seem to have any effect. Smallworld offers 40 water mains with 3 different networks, and all 40 water mains get true the reader.

Not really clear what to try next to hopefully get the expected result.

erik_jan
Contributor
Forum|alt.badge.img+23
  • erik_janContributor
  • Contributor
  • August 1, 2019

Hello @erik_jan,

Thanks for your answer.

Next to the WHERE tests I did on the entire workspace, I also tested this way of restrictive exporting => see attachments.

None of these tests seem to have any effect. Smallworld offers 40 water mains with 3 different networks, and all 40 water mains get true the reader.

Not really clear what to try next to hopefully get the expected result.

Hi @lucvdberghe,

Can you send me your workspace by email (erik.bodewitz@spatialbiz.com) and I will have a look what might be causing the issue.

Erik

L
Forum|alt.badge.img
  • lucvdberghe
  • Author
  • August 28, 2019

Hello,

With some help and a bit of testing I got it to work.

What causes this 'problem' is the following.

 

In most of my workspaces, I place (often pre-queried) data/objects in the SW Explorer under 'Export to FME' (standard plug-in) and/or FME Export (SBS plug-in) => see screenshots.

 

Applying a WHERE predicate doesn't work once any data is placed there. Not placing any data at all at these locations gets the WHERE -function to work. The plug-in finds the neccesary objects in the correct datastore(s) and applies the WHERE predicate on them.

Problem solved!

Community Stats

32,154
Posts
122,043
Replies
39,812
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.