Pitney Bowes MapXtreme - Manual (Page 225)

Chapter 11: Accessing Data from a DBMS
Defining Mappable Tables in Server Table Queries
MapXtreme v7.1 232 Developer Guide
SQL Server 2008 Spatial function example:
select location_id, geography::Point(lat, long, 4326 /*WGS84*/) as geog
from dbo.store_locations
Spatialware function examples:
select sw_member, ST_Buffer(sw_geometry, 66.0, 0.1) from rdbsdata
select ST_Overlap(flood100.sw_geometry, lake.sw_geometry) from flood100,
lake where ST_Overlaps(flood100.sw_geometry, lake.sw_geometry)
The Key Column(s)
A key column(s) must be returned in the query to enable it to be opened as a table. This is what
enables your MapXtreme application to identify each row in the result set to perform shading,
selection, and label operations on the layer.
The key column does not need to be specified in the query in most cases.
Your MapXtreme application can look up and determine the best key column(s) to use in order to
uniquely reference a row in the result set, and then add them to the query if they are not present. In
most cases, this is the primary key/unique index.
For Oracle Spatial tables, the MI_PRINX may be used.
For some queries, it is not possible for your MapXtreme application to identify the key. This is the
case in a query on a view or a synonym. The view or synonym must appear in the MapInfo
MapCatalog. They also must be registered as required with the underlying Spatial index system in
most cases. Since MapXtreme cannot determine the key on these, a mechanism is provided to allow
the application developer/query writer to identify the key column in the result set. The key must be a
single column and must be a distinct value in the result set. To identify the column that is to be used
as the key column, you can specify column alias of prinx or mi_prinx, (e.g., select custid mi_prinx,
custname, Obj from mycust).
Example
Select customer_id mi_prinx, obj from customer_view
The column alias “mi_prinx” is used to identify and use the customer_id column as the key column
for the table. You can alternately alias the desired key column in the create view statement to identify
the key column automatically for any query on that view.
Example
Create view customer_view as select customer_id mi_prinx, geoloc from
customer
In general, if a column name or column alias of prinx, or mi_prinx is found in the result set, that
column is used as the key column for the table. This enables the application/query writer to specify
the key column they desire.