Q&A - Question

Filter SQL layer

Question
FabD asked on December 22, 2016, keyword: Developer Kernel
Hi.
I would like to know if there is a way to filter a SQL layer using a complex query, something like:

SELECT * FROM table1 WHERE id_table2 IN (SELECT id FROM table2)

I've tried to set layer.Params.Query:

layer.Param.Query = "id_table2 IN (SELECT id FROM table2)"

but it does not seem to work. It works only with a simpler filter, like "id_table2 = 2" or like "id_table2 IN (1, 2, 3)"

I've also tried the ViewFeature property (http://www.tatukgis.com/Support/KB/Items/KB10872.aspx):

layer.ViewFeature = "SELECT * FROM table1 WHERE id_table2 IN (SELECT id FROM table2)"

(table1 is the same table used to create the layer), but I can not make it work, in the map I continue to see all the table items. Also using a simpler query (es. layer.ViewFeature = "SELECT * FROM table1 WHERE id = 5") it doesn't seem to work.

Thank you.
Answers
Artur Redzko (TatukGIS) replied December 14, 2016
I'm not sure, but you can try :

layer.Scope  = "id_table2 IN (SELECT id FROM table2)"
FabD replied December 14, 2016
Sorry but it seems that the Scope parameter works only with simpler query, like "id_table2 = 1".
Using a complex query the whole layer disappers
Artur Redzko (TatukGIS) replied December 14, 2016
Yes, that's true. It was designed for simple sql where clauses. But I will check how this can be improved in the future.
FabD replied December 14, 2016
Thank you.
And what about ViewFeature? From the Knowledge Base it seems exactly what I need, but I can't make it work. How does it work? The documentation is not very clear.
 
Artur Redzko (TatukGIS) replied December 14, 2016
I made a simple test. In our sample database in C:\Users\Public\Documents\TatukGIS\Data\Samples11\Samples\SQLLayers directory I altered the gistest.mdb database by adding Tabela1 table with column Pole1. Then I populated this column with values from 1 to 10. In our sql native layer config I added :

Scope=(FNODE_ in (select Pole1 from Tabela1))

and it worked - showed only few rivers matching filter. So it looks like all depends on the data and layer format. Another trick can be adding FEA. prefix before layer column names to define whether a column is from FEA or GEO table. Which layer format (class) do you use?

ViewFeature is an option to replace standard shapes attributes (from table columns, e.g. _FEA) with another table or a view. This is read-only mode, you cannot alter attributes.
FabD replied December 16, 2016
Thank you for your answer.
I use TGIS_LayerSqlPgisLibpq to create a Postgis layer from a table with a geometry column.

I've done some tests. The Scope layer property works only with a simple query. Using a complex query the layer disappears.

I've tried also to set the scope in the layer path:

Scope=(id_test_table2 IN (SELECT id FROM test_table2 WHERE name = 'a1'))
Storage=PostGIS
Layer=test_table1
Dialect=POSTGRESQL
GeometryColumn=geom
GeometryFieldCast=Native
Libpq=host=localhost port=5432 dbname=mydb user=myuser password=mypsw client_encoding=WIN1250

In this case the Scope parameter has no effect (the whole layer is visible), even with a simple query.
Artur Redzko (TatukGIS) replied December 16, 2016
First of all Scope cannot be used inside .ttkls file. You need to create .ini file, e.g. mylayer.ttkls.ini and there put :

[TatukGIS Layer]
Scope=(bo_id in (select del_id from ver_deklaag))

I made another test using TGIS_LayerSqlPgisLibpq layer and it works too using the scope above.
If this don't help you, please write at support@tatukgis.com and describe the problem with sample data or script and steps how to reproduce it.
FabD replied December 16, 2016
Thank you.
For my application this solution isn't feasible because I don't use a ttkls file, but I set all parameters as a single string to the layer Path property. Moreover the filter must be set dinamically to the layer and not only at creation time. I don't know if there is a way to set the ini file to the layer dinamically.
Artur Redzko (TatukGIS) replied December 16, 2016
You can just set the layer Scope property from code :

lv.Scope := '(bo_id in (select del_id from ver_deklaag))' ;

 
FabD replied December 16, 2016
I know this, but as we already said in the first-second answer, this doesn't work with complex queries.
Artur Redzko (TatukGIS) replied December 16, 2016
I think you confound layer.Params.Query with layer.Scope. First one is limited to simple queries and works on client site (rendering), second is sent to a database and works with complex queries. 
Tomasz Kosinski (TatukGIS) replied December 16, 2016
Please continue this thread in support@ emails.
FabD replied December 22, 2016
Thank to the support I have solved the problem.
In this the scope works if the I set SmartSize=0.
We can set the SmartSize in the ini file:

Area.SmartSize=0

or in the code:

layer.Params.Area.SmartSize = 0; //for polygons
layer.Params.Line.SmartSize = 0; //for polylines
If you would like to answer the question please Sign In.
*
*
Please review our recent Privacy Policy.
If you have any questions or requests, please contact us.
Rules
The Questions and Answers (Q & A) is intended to provide a means of communication between TatukGIS customers.
 
  1. Licensed users (with active maintenance play) of TatukGIS products may contribute to the Q & A content. Read-only access is available to anyone.
  2. Keep the content positive and professional.
  3. Be courteous to others by posting information when a question or issue asked on the Q & A is answered or resolved by other means (such as with help from TatukGIS technical support). Offer others at least a hint how the posted question was answered or the issue was resolved.
  4. The Q & A is not a replacement for TatukGIS technical support. TatukGIS team may or may not regularly follow or contribute content.