Query Dialog

This dialog allows you to create or edit complex queries. The queries can contain Filter, Joins and Aggregation.

To create a new query, proceed as follows:

Add all tables that your query requires.
Connect the tables using Joins.
Set the columns to be included in the result table.
Optionally, set Data Aggregation.
Set Filter to select only specific data.

Add Table

Choose Add Tables and then select from the existing external and internal tables those whose data you want to include in the query. Results from other queries can also be used.

To select a table or query, double-click the corresponding row. When all tables or queries are selected, click Close.

Joins and other connections

If you use several tables, you must define how these tables are to be linked together in the result. If you do not specify anything here, you get the Cartesian product of both tables (full join), which is usually not the intention.

Joins types

Use joins if you want to append columns from several tables to each other.

Create Joins

Create a join by clicking with the mouse on the field of one table and then dragging it to a field of the other table with the mouse button held down. These two columns are joined, that is, only those data records are combined in which the contents of both fields are the same.
The created join is displayed as a line between the two tables.

Editing Joins

Double-click the connection line that indicates the join or click the line and then click the Edit Join button.
The Connection Settings dialog opens where you can specify the settings in detail.

Spatial Joins

You can create a spatial link to add spatial information to data.

Spatial connection/Joins

To be able to add a spatial link, you need a column in your table to be enriched that contains geographic information.
If this is given, click Add Spatial Connection in the Edit query window.
Now select your tables. The "left table" is always the table that you want to enrich with geographical information. The "Right table" will then provide you with this information. Important: In the Coordinate field, always select the column that contains geographic information. For more information on how this must look, click on the i. When you have set everything correctly, click OK.
Then double-click the columns you want to display in the final query.

Example:

Enrichment of a customer master table with information from an accessibility analysis.

Union - connection

If you want to edit the spatial connection, just click on the dashed connection line and select Edit Connection from the Edit Query window menu at the top.
With a click on Delete connection you can delete the spatial connection again.

Unions

Union connections can be used to make one table out of two tables with the same contents. All records from both tables are displayed below each other.

Add Unions

In the Edit Query window, click Add Union to get to the same window.
The dialog Add Union opens where you can specify the two tables to be joined.
Now set the corresponding columns of the two tables. easymap already suggests connections after selecting the tables. If you want to change them, open the drop-down menu. If you don't want to connect these columns or you want to delete connections again, mark the row on the left margin and then press "delete" on your keyboard. Only the columns that correspond to both tables will be included in the union.

Example:
The union will now be added to your query as a separate "table object" and you can decide, as with a "normal" table, which columns to add to the query and whether to make further connections to other tables.

Edit Unions

If you want to edit the union, just click the pencil icon next to the union title in the Edit Query window.

Add Column

After you have inserted tables, you can include columns from these tables in the query result. Even if you want to filter or group by a column, you must add that column.

To do this, double-click on the individual fields or on Add column. The selected columns now appear in the lower part of the dialog.
Double-click the line that displays the join or click the line and then click the Edit join button. Delete column

Edit columns

All inserted columns are displayed in the lower part of the dialog. You can set the following properties of the columns:

Table: The table from which this column originates
Column: The column from this table that is to be included in the result.
Caption: You can assign a new name to the results column. If you do not specify anything here, easymap automatically generates a generic name.
Function: This property is only displayed if you have previously activated Data Aggregation.
Display: Here you can specify whether the column should be included in the result table. If the column is only used for filtering or grouping, it may be useful to remove the check mark.
Criteria: Here you can specify Criteria for filtering data.

Note: Filtering takes place before aggregation (a WHERE section in SQL, unlike the HAVING section, which is not supported in easymap queries). When entering the filter criterion, a special syntax must be used. Several lines are available for criteria. These are evaluated as alternative filter criteria (OR operation), i.e. the data record is included if one of the criteria is true.

Add Calculated Column

In the query wizard you have the possibility to calculate columns. The functionality of the calculation mode is explained here.

Data aggregation

If you want to combine data records, you must activate data aggregation.

After clicking on the sum sign Σ you can apply aggregation functions to the columns. The Group function is used by default.

As soon as an aggregation function is selected for at least one column, an aggregation function must also be selected for every other column.

Aggregation Types

Grouped: All data records with the same values are combined in one result data record. If several columns are grouped together, each combination of these values occurs only once in the result.

Not grouped: To calculate a result from the many data, one of the available aggregate functions is used.

Filtering data

Use the criteria line to specify a data filter. This must be specified in a special syntax.

With criteria you can use selection criteria for each column. The query result then only contains the data records for which all affected data columns meet the selection criteria.
You can also combine several filter expressions. Criteria that are entered in the same line are linked with and. Criteria that are entered in different rows are linked with or.

If several criteria are used in one cell, it is necessary to specify the column name in the filter expression.

Save preview and query

After a click on preview easymap shows a preview of the query result. If this does not yet correspond to your expectations, you can change the query again.
After a click on OK you will be prompted to give the query a name. The query is saved under this name. At the same time, a table view of the query result is opened.