Note: spatial selection only works with database layers, not with files. If you want perform spatial queries please import the files into the database first. Also, make sure that multigeometry is exploded into the geometry.

 

Spatial selection allows you to create database queries for layers that reside in the database. This tool allows you to select any database feature from any database layer in your Map View. You can select features by attributes or by location depending on what do you need.

 

For information how to make basic spatial selection queries, click here.

To perform Spatial Selection on tutorial data click here to open map in the Map Viewer.

 

Example 1. Select town with population 5000.

  1. Click Feature tab and Spatial Selection icon.
  2. Select table and_cities and column number.
  3. Write =5000
  4. Click Select.

ex2

Example 2. Select town with the name Andorra la Vella.

  1. Select layer and_adm1 and column name_1.
  2. Write (don’t copy and paste) =’Andorra la Vella’.
  3. Note: attributes are case sensitive.

  4. Click Select.

ex1

Note: Make sure that you put single quotes (i.e.’giscloud’) for String (text) attributes.

Example 3. Select counties that belong to the ‘U’ zone have and area less then 0.012511.

  1. Select layer and_adm1 and column validfr_1.
  2. Write (don’t copy and paste) =’U’.
  3. Write AND operator.
  4. Select layer and_adm1 and column shape_area.
  5. Write <0.012511.
  6. Click Select.

U_and_area

Example 4. Select both primary and secondary routes.

  1. Select layer roads and column rtt_descri.
  2. Write (don’t copy and paste) =’Primary Route’.
  3. Write OR operator.
  4. Again select layer roads and column rtt_descri.
  5. Write (don’t copy and paste) =’Secondary Route’.
  6. Click Select.

ex5

Example 5. Select towns inside Canillo county.

  1. Select layer roads and column rtt_descri.
  2. Select ST_Within() operator.
  3. Select and_cities and wkb_geometry column.
  4. Note: to add another table click “+” button.

  5. Select layer and_adm1 and column wkb_geometry.

    Query ST_Within(and_cities.wkb_geometry,and_adm1.wkb_geometry) will select all point features within all polygon features. We want to specify county (polygon) with name (attribute) ‘Canillo’:

  6. Write AND operator.
  7. Select and_adm1 and name_1 column.
  8. Write (don’t copy and paste) =’Canillo’.
  9. Click Select.

Note: wkb_geometry field has information about geometry in the binary form.

town_within_county1

Example 6. Select roads that intesect Andorra la Vella county.

  1. Select or write St_Intersects().
  2. Select layer and_roads and column wkb_geometry.
  3. Click + to add another table.
  4. Select layer and_adm1 and column wkb_geometry.
  5. Write operator AND.
  6. Select layer and_adm1 and column name_1.
  7. Write (don’t copy and paste) =’Andorra la Vella’.
  8. Click Select.

roads_within_county

 

Here is a map with the spatial selection examples results:

 

 

 

Time&Date Selection

CREATED and MODIFIED fields are automatically generated fields with information about when is feature created and last edited.

To activate CREATED and MODIFIED fields:

  1. Click on a layer in the Layer list.
  2. Open Layer tab and Edit layer button.
  3. Open Attributes tab.
  4. Check CREATED and/or MODIFIED checkbox.
  5.  

    created

     

  6. Click Save.
  7. Refresh Data Grid.
  8.  

    created1

     

Note: time and date has to be written in this format:

my_db_layer.__created=’YYYY-MM-DDTHH:mm:ss+ZZ’::timestamp with time zone

  • YYYY: 4-digit year e.g. 2015
  • MM: month e.g. 04 means April
  • DD: date e.g. 21 meaning the 21st
  • T: a separator between the date and time part
  • HH:mm:ss: hours, minutes and seconds in 24 hour clock system e.g. 18:54:00 meaning 6:54 PM
  • ZZ: timezone shift indicator, two digit timezone offset e.g. +01 meaning GMT+1
  • Make sure that you type ::timestamp with time zone.

Allowed operators:

  • = equal
  • <> not equal
  • < less than
  • <= less than or equal to
  • >= greater than or equal to
  • < greater than

 

 

Example 1. Select a feature that is created 10/31/2014 at 2:23 in the time zone GMT+1

  1. Open Spatial Selection window from the Feature tab.
  2. Select your layer and column _created from the dropdown menu.
  3. Write time and date in the previously described way =’2014-10-31T00:02:23+01′::timestamp with time zone.
  4. Click Select.
  5.  

    created2

 

Example 2. Select features created between 10/20/2014 at 2:00 in the time zone GMT+1 and 10/31/2014 at 5:00 in the time zone GMT+1

  1. Open Spatial Selection window from the Feature tab.
  2. Select your layer and column _created from the dropdown menu.
  3. Write > operator.
  4. Write time and date in the previously described way ‘2014-10-20T02:00:00+01’::timestamp with time zone.
  5. Write operator AND.
  6. Select again layer and column _created and write < operator.
  7. Write time and date in the previously described way ‘2014-10-31T05:00:00+01’::timestamp with time zone.
  8. Click Select.
  9.  

    created3