SqlString

The SqlString property is used to specify which data and in which order will be selected from the database. The SqlString property requests a valid SQL statement. It is used to:

  • Select the relevant attributes. If the names in the database do not correspond with the tree item names use the AS keyword to relate these names.
  • Select the table(s), view(s)/query(ies) with a FROM clause
  • If needed, make a selection with a WHERE condition
  • Define the sort order with the ORDER BY. This is important, as if no ORDER BY clause is configured, the sort order of the records read is undefined. As the GeoDMS calculates with arrays, the sequence is relevant. Therefor it is is important to always specify the ORDER BY clause in the SqlString property.

The SqlString property is used to select data. Use only the following SQL key words: SELECT, AS, FROM, WHERE and ORDER BY. If a SQL Statement with a JOIN or a GROUP BY is needed, define this as a view or query in the database and use it as source for the GeoDMS.

We advice to make complex selections, for instance with substrings, within the GeoDMS and not in the WHERE clause.

The SqlString property can not be used to modify data, UPDATE, SELECT INTO and DELETE statements are not allowed.

NOTE: SQL does not like it when the FROM table name starts with a number. To circumvent this, you can double-quote it. See this example: ``` unit CSV : StorageName = "%DataDir%/20221207_Planniveau_bewerk_20221208.csv" , StorageType = "gdal.vect" , StorageReadOnly = "true" , SqlString = "SELECT * FROM \"20221207_Planniveau_bewerk_20221208\" WHERE rdnewx <> ''" { } ``` </details> ## Examples ### Example 1 ``` unit Read_Utrecht_gpkg_org : StorageName = "%dataDir%/Plan_2022/Plan_openbaar_na_join.gpkg" , StorageType = "gdal.vect" , StorageReadOnly = "true" , SqlString = "SELECT * FROM Plan_openbaar_na_join WHERE status =='Hard' OR status =='Zacht' OR status =='Onbekend'"; ``` ### Example 2 ``` container Import := for_each_nxs(rest_red/naam_underscore, uint32, rest_red/SqlString) , StorageName = "%RSLDataDir%/Energie/RES/Analysekaarten/3_elektriciteit_aanbod_potentieel/wind/merge_wnd_hard_wt56_nat_ana_v3.gdb" , StorageReadOnly = "True" , SyncMode = "Attr" , StorageType = "gdal.vect" , SqlString = "=rest_red/SqlString" { unit Beperkt_kwetsbare_gebouwen : sqlstring="SELECT * FROM merge_wnd_hard_wt56_nat_ana_v3 WHERE rest_red = 'Beperkt kwetsbare gebouwen'" { attribute geometry(poly); } } ``` ### Example 3 ``` container Read_Roads_pbf : StorageName = "= dir + '/'+Regio+'-'+date+'.osm.pbf'" , StorageType = "gdal.vect" , StorageReadOnly = "True" , SyncMode = "None" , DialogData = "wgs84" { unit multilinestrings : sqlstring = "SELECT * FROM multilinestrings" { attribute geometry (arc); attribute other_tags; } unit lines : sqlstring = "SELECT * FROM lines" { attribute geometry (arc); attribute highway; attribute other_tags; attribute z_order; } } ```