Pseudo-Columns

The MI SQL language is a powerful query language that builds upon the SQL language in order to provide spatial queries. Like all other SQL dialects MI SQL has support for pseudo-columns. These pseudo-columns allow easy construction of queries. The case of the pseudo-column is irrelevant and unless otherwise stated all pseudo-columns can be used in all constructs of an MI SQL statement.

Obj

The Obj pseudo-column represents the spatial attribute in the data source regardless of the name of that spatial attribute at the data source. This pseudo-column cannot be used with the ORDER BY, LIMIT and OFFSET constructs of an MI SQL statement.

Examples

; select the spatial attribute from all the features in the World 
; table
SELECT Obj FROM World
		
; select the spatial attribute from all features whose area is greater 
; than 1000 miles
SELECT Obj FROM World WHERE MI_AREA(Obj, 'sq mi', 'Spherical') > 1000

MI_GEOMETRY

This is the same as the Obj pseudo-column. It has the same features and restrictions as Obj.

Examples

; select the spatial attribute from all the features in the World 
; table
SELECT mi_geometry FROM World

; select the spatial attribute from all features whose area is greater 
; than 1000 miles
SELECT mi_geometry FROM World WHERE MI_AREA(mi_geometry, 'sq
      mi', 'Spherical') > 1000

MI_STYLE

In MI SQL style is a valid data type. The MI_STYLE pseudo-column represents the attribute in the data source that holds the style. The style attribute and therefore the MI_STYLE pseudo-column have certain restrictions. For example, it cannot be used in the WHERE, ORDER BY, GROUP BY, LIMIT and OFFSET clauses. It is only valid to use the MI_STYLE pseudo-column from within the SELECT clause.

Examples

; select the style attribute from all the features in the World 
	; table
	SELECT mi_style FROM World

MI_KEY

Most data sources have the concept of a primary key that uniquely identifies a specific row in the data set. MI SQL has support for primary keys either via using the attributes that define the primary key or by using the MI_KEY pseudo-column. The primary key pseudo-column is an attribute regardless of the number of attributes used to define the primary key. For a primary key that is composed of multiple attributes in the data source the primary key value is a string that is composed of the values of the primary key. The format of this string is a number that represents the length of the following value followed by a colon followed by a string representation of the value as many values as needed.

Examples

; select the primary key from all features in the World table
SELECT MI_KEY from World

; select the POP_1990 attribute from the feature whose primary key
; equals 1
SELECT POP_1990 from World WHERE MI_KEY=1

; select the POP_1990 attribute from all the features in the World 
; table and sort the results by primary key
SELECT POP_1990 from World ORDER BY MI_KEY

; select the POP_1990 attribute from all the features in the World
; table and GROUP BY the primary key
SELECT POP_1990 from World GROUP BY MI_KEY

; select the POP_1990 attribute from the World table features with the 
; specified list of primary keys
SELECT POP_1990 from World WHERE MI_KEY in (1, 3, 4, 5, 76)

; select the POP_1990 attribute from the World table whose first primary key attribute equals 5 and second primary key equals “Hello”
SELECT POP_1990 from World WHERE MI_KEY='1:5:2:Hello'

Mapinfo_ID

MapInfo_ID can be used in MI SQL queries. The TAB Data Provider publishes MapInfo_ID as a primary key for a table. DescribeTableRequest returns information in metadata:

<ns4:KeyDefinition keyType="Implicit">
<ns4:AttributeRef>MapInfo_ID</ns4:AttributeRef>
</ns4:KeyDefinition>

Examples

;select state from States where MapInfo_ID=2
SELECT state FROM States WHERE MapInfo_ID='2'