SQL Associated Query Example

This paper takes the average temperature of July in the provincial capital city stored in different Datasets as an example to illustrate how to carry out Relate Table Settings and realize the association query between different Datasets.

Brief introduction of data

(1) In the Temperature _ July _ P Dataset, there is the average temperature data (Temperature Average) of each city weather station in July, and the weather Site Name (Name) includes the name of the provincial capital city.

(2) There is a capital city (Name) in the Province Capital _ P Dataset.

Train of thought introduction:

Query the average temperature information of the provincial capital city in July through the provincial capital city name field and the associated Temperature _ July _ P Dataset, and generate the New Dataset.

Example implementation:

The first step is to open the Sample DataWorks pace: Temperature. Smwu, open the Average Temperature Distribution Map of China from 1981 to January 2010.

Step 2: Click the Spatial Analysis-> Query-> SQL Query button to pop up the "SQL Query" dialog box.

Step 3: Set "Select Dataset:" as the ProvinceCapital _ P Dataset. Click the "Set Associated Field.." "Button at the bottom left of the dialog box to open the" Relate Table Settings "" dialog box.

Step 4: Make the following settings in the Relate Table Settings "dialog box: External Table Temperature _ July _ P, Field Name, Related Field Name, Connection Method, Left Connection. After setting, in the SQL Query dialog box, Select Fields of Relation is added in "Select Fields", as shown below:

Step 5. The other Parameter Settings in the SQL Query "dialog box are as follows:

  • Query Mode: Query Spatial and Attributes;
  • Query field: ProvinceCapital _ P. Name, Temperature _ Jan _ P. Temperature Average as Temperature Average _ Jan;
  • Query Conditions is not set, which means unconditional;
    • Check Highlight in Map in the
  • Show Results area;
  • Save Results: Set the Datasource and Dataset Name to be saved.
See the following figure:

Step 6: Browse the Search Result.

The Search Result is a Dataset (Point Dataset) of the same type as the ProvinceCapital _ P, and its property table stores the name of the provincial capital and the average temperature in January, as well as the System Field: (the field starting with Sm).

Map will automatically switch to "Associated Browse" mode, and the property sheets of Untitled Map and Search Result will be displayed side by side; Meanwhile, the Search Result will be highlighted in the Untitled Map. Select a city in the attribute table, and the Map will automatically locate the city. As shown in the following figure:

Remark

  1. In the Join Type:

    Relate Table Settings ", when Join Type is left join, the number of result records is the same as the ProvinceCapital _ P Dataset. Regardless of whether there is a matching record in the Temperature _ Jan _ P Dataset. There are matching records, giving Select Fields in Temperature _ Jan _ P, otherwise null. For example, in Shijiazhuang and Shanghai in the figure, there is no average temperature, but it appears in the Search Result, and the average temperature field is empty.

    When

    Join Type is an inner join, the number of resulting records is equal to the number of records whose ProvinceCapital _ P exactly matches the Temperature _ Jan _ P Dataset. The Search Result removes records without average temperature information.

    Examples are shown in the following table:

    2. Source Dataset Properties table Relation property sheet Left Join Query Result Table Inner Join Query Result Table
    Field Field 2
    1 1A
    2 2B
    3 3C
    4 4D
    Related Field Field 3
    1 10
    2 20
    3 30
    5 50
    Associated field Field 2 Field 3
    1 1A 10
    2 2B 20
    3 3C 30
    4 4D
    Associated field Field 2 Field 3
    1 1A 10
    2 2B 20
    3 3C 30
  2. Associated field:

    In the Relate Table Settings ", when the Field is not unique, that is, there are multiple records with the same" Field "value in the Source Dataset, the Search Result keeps multiple records in the Source Dataset, corresponding to the same record in the external table. For example, if the Source Dataset is the country and province information and the external table is the country information, there will be multiple provinces corresponding to one country, that is, "many-to-one", but it is meaningful.

    In

    The Relate Table Settings ", when the Related Field is not unique, that is, the Related Field" values of multiple records in Relation are the same. The Search Result retains the last record corresponding to "Field" in the Relation.

    In order to ensure the correctness and predictability of use, it is suggested that the associated field should be unique in Relation to ensure one-to-one correspondence.