Query builder

Introduction

This tool is accessible to registered users with role that extends "analyst" role and allows you to filter data from a layer to specified subset using conditions. This tool allows you to filter your data to a subset of it and create simple analysis scenarios. You can also download your results in one of the available formats for example CSV and open them in Excel to create a chart for your results.

Click on the Query builder() tool. Query builder should appear in the Left panel with the following form:

Layer. Currently selected layer, conditions data table and match are displayed for this layer. If you need to display on the map 2 or more filtered layers for example: show all features from roads layer that are intersecting with features from buildings layer and show all features from buildings layer that are intersecting with features from roads layer you need to apply 1 rule (mentioned above) to each layer (buildings and roads).

Show results on map. Whether after a successful query results should be displayed on map

Affected layers. Shows what layers are affected by specified conditions, this means that your conditions will be applied to this layers when drawing the map, getting information, searching and so on. You can filter multiple layers by selecting the layer and specifying conditions and match type for it.

Match. All - every condition should match, Any - at least one condition should match

Conditions data table. Add conditions by clicking on (). A condition at it's basic form consists of "field", "function", "operator" and "value" that create an expression and if that expression evaluates to true it is added to the results. You can remove a condition by clicking on ().

Click the Submit button to display query results. You can also Download the query results in one of the available formats: CSV, KML, ESRI Shapefile, GML, MapInfo File. Click on a row of the data table to zoom into the object extents. You can sort the results in ascending or descending order by clicking on the column header.

Click the Clear button to clear all conditions for all layers.

Click the Back button to alter previously defined conditions.

Geometry functions

If the selected field is a geometry field then the following functions are available:

• ST_Crosses - Returns TRUE if the supplied geometries have some, but not all, interior points in common.
• ST_Contains - Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.
• ST_Disjoint - Returns TRUE if the Geometries do not "spatially intersect" - if they do not share any space together.
• ST_Equals - Returns true if the given geometries represent the same geometry. Directionality is ignored.
• ST_Intersects - Returns TRUE if the Geometries/Geography "spatially intersect" - (share any portion of space) and FALSE if they don't (they are Disjoint). For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect)
• ST_IsValid - Returns true if the ST_Geometry is well formed.
• ST_Overlaps - Returns TRUE if the Geometries share space, are of the same dimension, but are not completely contained by each other.
• ST_Touches - Returns TRUE if the geometries have at least one point in common, but their interiors do not intersect.
• ST_Within - Returns true if the geometry A is completely inside geometry B.
• ST_Area - Returns the area of the surface if it is a polygon or multi-polygon. For "geometry" type area is in SRID units. For "geography" area is in square meters.
• ST_Distance - For geometry type Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units. For geography type defaults to return spheroidal minimum distance between two geographies in meters.
• ST_Perimeter - Return the length measurement of the boundary of an ST_Surface or ST_MultiSurface value. (Polygon, Multipolygon)

Examples

Equality operator

Get all the results with field "iso_a2" equal to "MD"

Match: All

• Layer: Worldboundaries
Condition 1
• Field: "World boundaries"."iso_a2"
• Function:
• Operator: "="
• Value: "MD"

Result: Moldova

Like operator and multiple conditions

Get all the results with field "name" beginning with "United" and "dial" < 50

Match: All

• Layer: Worldboundaries
Condition 1
• Field: "World boundaries"."name"
• Function:
• Operator: "ILIKE"
• Value: "United%"
Condition 2
• Field: "World boundaries"."dial"
• Function:
• Operator: "<"
• Value: "50"

Result: United States of America, United Kingdom

Multiple conditions with match type "any"

Get all the results with field "name" equal to Moldova and Romania

Match: Any

• Layer: Worldboundaries
Condition 1
• Field: "World boundaries"."name"
• Function:
• Operator: "="
• Value: "Moldova"
Condition 2
• Field: "World boundaries"."name"
• Function:
• Operator: "="
• Value: "Romania"

Result: Moldova, Romania

IN operator

Get all the results where "type" field has "primary" or "secondary" value

Match: All

• Layer: Roads
Condition 1
• Field: "Roads"."type"
• Function:
• Operator: "IN"
• Value: "primary,secondary"

Intersection

Get all the results for "Roads" layer that intersect "Buildings" layer of type "theatre". "Roads" and "Buildings" layers are added using Upload data and create layers tutorial.

Match: All

• Layer: Roads
Condition 1
• Field: "Roads"."Geometry"
• Function: "ST_Intersects"
• Operator:
• Value: "Buildings"."Geometry"
Condition 2
• Field: "Buildings"."Type"
• Function:
• Operator: "="
• Value: "theatre"

Area

Get all the results for "Buildings" layer with area bigger than 10000. "Buildings" layer is added using Upload data and create layers tutorial.

Match: All

• Layer: Buildings
Condition 1
• Field: "Buildings"."Geometry"
• Function: "ST_Area"
• Operator: ">"
• Value: "10000"