Question

Use of WHERE Clause in Smallworld and SBS Reader

  • 31 July 2019
  • 4 replies
  • 15 views
L
Badge

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

4 replies

erik_jan
Rank
Userlevel 2
Badge +12
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
Badge

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
Rank
Userlevel 2
Badge +12

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
Badge

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!

Reply


Community Stats

28,670
Posts
109,365
Replies
36,531
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.