Options

General Tab

Table 1. Query Spatial Data General Options

Option

Description

MISQL

 To enter an MI SQL query, begin typing. An auto-completion pop-up window displays a list of functions and aggregations supported by Spectrum Spatial, filtering the list based on what you type. All MI SQL keywords are highlighted in blue. See the MapInfo SQL Language Reference for detailed information on using MI SQL.
  • Press Tab to insert a function.
  • Type $ to list any upstream input fields. If you use input fields you must verify that they exist when you are designing the query.
  • To see the table structure, you can use SELECT * to see all the columns and types in a table.
  • You can name a stage field using the As keyword; for example, SELECT Obj As Geom...
  • Click Verify to see the fields in the data and the Query Spatial Data input fields, as well as to verify the validity of the query. These stage fields are now available for the downstream stage.

Examples

select count (*) from "/Polygon" where MI_Contains(Obj, ${Geometry} )

This is based on the typical query used for Point in Polygon where “/Polygon” is the named table that contains polygons to check for containment, Obj is the geometry column in the “/Polygon” named table that contains the polygons, and ${Geometry} is the field that will contain the incoming point to check for containment. This counts the number of polygons that the incoming point is contained within.

select Country, Capital, MI_Within(obj, ${Geometry}) from "/Samples/NamedTables/WorldcapTable"

This query returns each country and capital from the WorldcapTable named table that is contained within a specified geometry.

select Count(*) from "/Samples/NamedTables/WorldcapTable" Group by Country

This query returns the number of records per country.

Note: A named table must be present in the repository before using it with MISQL in this stage.
Verify Tests the query. A message confirms if the query was executed successfully.
Override Tables From Input Field checkbox Check to override tables from the Input field using an input string from the drop-down selection list. The value of the input string overrides the From clause in the MISQL query at runtime. This lets you replace the search table for each of the records as you go.

When replacing a search table, the replacement table must have the same columns as in the Select clause. You may replace the entire From clause with the Input field.

MISQL Example 1

select Country from "/Samples/NamedTables/WorldTable" limit 1
  • Value in the selected field is "/Samples/NamedTables/WorldcapTable"
  • Actual query at runtime is

    select Country from "/Samples/NamedTables/WorldcapTable" limit 1

MISQL Example 2

select A.Pop_Urban as UrbanPop, B.Cap_Pop as CapitalPop from "/Samples/NamedTables/WorldTable" A, "/Samples/NamedTables/WorldcapTable" B where A.Obj contains B.Obj
  • Value in the selected field is "/Samples/NamedTables/WorldTable2000" A, "/Samples/NamedTables/WorldcapTable2000" B
  • Actual query at runtime is

    select A.Pop_Urban as UrbanPop, B.Cap_Pop as CapitalPop from "/Samples/NamedTables/WorldTable2000" A, "/Samples/NamedTables/WorldcapTable2000" B where A.Obj contains B.Obj

Include checkbox

The Include checkbox allows you to select which columns to include as fields on the output record. By default, all fields are selected. Once you start building the query, only columns in the Select clause will be shown.
Table Column Represents the column name for the SQL query. This field is not editable. If you have an alias in the SQL query then the alias will appear in this column.
Output Field Name Represents the output field. If selected, this name will be written to the stage output. This field is editable; for example, a field called Latitude could be named PointOfInterest_Latitude (a unique name is required).
Type Represents the column type. This field is not editable.

Coordinate System Tab

Note: This tab is only available for dataflows created prior to version 11.1 of the Spectrum Technology Platform. If you require a coordinate system transform use either the MI_Transform MI SQL function or the Coordinate System Transform option in the Spatial Calculator.
Table 2. Query Spatial Data General Options

Option

Description

Coordinate system

The coordinate system for the geometry object returned by the query. Query Spatial Data transforms the geometry to the coordinate system you specify here.

To change the default coordinate system, clear the default value then start typing the name of a coordinate system or an EPSG code to see a drop-down list of systems that begin with or contain those characters (the list appears after you have entered three alphanumeric characters). You can also click the browse button to open the Select Coordinate System dialog and select one from the list. Regardless of which coordinate system you select, the descriptive name will appear in the field and the respective EPSG code is saved for the stage.

For more information on EPSG codes, see www.spatialreference.org. To retrieve a list of supported codespaces for EPSG, you can submit the SOAP request List Supported CoordSys by Code Space from the Geometry Service Demo page at http://<server>:<port>/Spatial/GeometryService/DemoPage.html.