Skip to main content
  • EN

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 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

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.

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

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

30,108
Posts
114,295
Replies
37,745
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.