CiA Spatial Layers Mapkey

Overview

Spatial catalogue layers replicated from CiA are designed to be keyed on a unique integer FID value to provide best selection performance. However, this requires additional processing steps in the nightly data replication process that may be a point of failure for your organisation. This manifests as broken search and info panel queries or broken CiA integration, as the FID value (E.g. 85029) may revert to an older, legacy composite mapkey value (E.g. P1PLN_LAND179501POLYGON).

While IntraMaps configuration on SpatialCatalogue selection layers is recommended to use FID, it is possible from the 25B release to enable application linking to CiA using the legacy composite mapkey. This is available for customers wanting or needing to avoid any impacts from a failed CiA replication process for their end users.

Using FID Mapkey

Note: This requires a PR and/or a CES TBGEO_MLSEARCH table be included in your replicated CiA data.

Ensure the following has been run in your CiA environment:

Run the Update Multi-Layer search table task of the Spatial Processing Engine.

In CiA you then need to navigate to Spatial Configuration>Logging and Background Jobs and click on the Background Jobs tile.

You then need to run the 'Update multi-layer search tables' task under the secondary action (screenshot below).

Customers should also raise this item with any T1 staff working on their CiA upgrade to ensure this data is successfully replicated into the Spatial Cloud environment.

FID Queries Method 1

If a customer is using the recommended T1 entity selection layers from CiA in the Spatial Cloud there should be very little additional configuration required. The FID MAPKEY values will flow through to the MAPKEY field in the relevant TBGEO_LOCATION tables.

All existing IntraMaps configuration for these affected selection layers should already join back to these tables for their mapkey queries. If this is the case then the correct data should be returned in the info panel, the search forms will run as expected and application linking to CiA will be successful.

One potential logic update to any queries related to the MAPKEY field is additional criteria may be required to limit results to a particular map_id to prevent duplicate or multiple records being returned. For example:

SELECT * FROM PR.TBGEO_LOCATION WHERE mapkey='@mapkey' AND MAP_ID='LandMap'

SELECT * FROM CES.TBGEO_LOCATION WHERE mapkey='@mapkey' AND MAP_ID='AssetMap'

Custom Selection Layers and Queries

If a customer data update process has been put in place, such as extracting data via ETL on premise and pushing up client built CiA selection layers, there will be additional work to update extract and on premise data processes to use the new FID. Note for these customers, the TBGEO_LOCATION tables will include a MAPKEY_OLD field, populated with the old composite mapkey. This can simplify reconfiguration to only require a change in any JOIN logic to the TBGEO_LOCATION tables to use MAPKEY_OLD 

FID Queries Method 2

The following shows the relationship between the old mapkey point of truth - TBGEO_LOCATION - and the new - TBGEO_MLSEARCH.

These example queries can be used to help rewrite and update any existing SQL queries referencing TBGEO_LOCATION in your IntraMaps configuration that can now reference TBGEO_MLSEARCH.

Take note of the slight differences in logic for PR and CES tables.

CES

SELECT

FROM

CES.TBGEO_LOCATION TBGEO INNER JOIN [CES].[TBGEO_MLSEARCH] ML

TBGEO.[SYSTEM_ID]=ML.[SYSTEM_ID] COLLATE DATABASE_DEFAULT AND

TBGEO.[ENTITY_TYPE]=ML.[ENTITY_TYPE] COLLATE DATABASE_DEFAULT AND

TBGEO.[MAP_ID]=ML.[MAP_ID] COLLATE DATABASE_DEFAULT AND

TBGEO.[ENTITY_KEY_STRING_1]=ML.[ENTITY_KEY_STRING_1] COLLATE DATABASE_DEFAULT AND

TBGEO.[ENTITY_KEY_STRING_2]=ML.[ENTITY_KEY_STRING_2] COLLATE DATABASE_DEFAULT AND

TBGEO.[ENTITY_KEY_STRING_3]=ISNULL(ML.[ENTITY_KEY_STRING_3],'') COLLATE DATABASE_DEFAULT AND

TBGEO.[ENTITY_KEY_STRING_4]=ISNULL(ML.[ENTITY_KEY_STRING_4],'') COLLATE DATABASE_DEFAULT

SELECT

FROM

PR.TBGEO_LOCATION TBGEO INNER JOIN [PR].[TBGEO_MLSEARCH] ML

TBGEO.[SYSTEM_ID]=ML.[SYSTEM_ID] COLLATE DATABASE_DEFAULT AND

TBGEO.[ENTITY_TYPE]=ML.[ENTITY_TYPE] COLLATE DATABASE_DEFAULT AND

TBGEO.[MAP_ID]=ML.[MAP_ID] COLLATE DATABASE_DEFAULT AND

TBGEO.[ENTITY_KEY_STRING_1]=ML.[ENTITY_KEY_STRING_1] COLLATE DATABASE_DEFAULT AND

TBGEO.[ENTITY_KEY_INT_1]=ML.[ENTITY_KEY_INT_1]

NOTE: It is expected the TBGEO_MLSEARCH table contain duplicate mapkey values for a given MAP_ID (e.g. PropertyMap) if T1 entities are spread across different spatial systems or have derived layers created against them. In these cases the join to MLSEARCH should specify the CATLG_ID of the relevant selection layer configured.

Using Legacy Composite Mapkey

If desired IntraMaps-CiA replication can be configured on the legacy composite mapkey value, drawn from the relevant TBGEO_LOCATION table in CiA. This will bypass some data processing CiA replication steps for sites experiencing any ongoing issues with mismatched replicated mapkey values between TBGEOM_MLSEARCH and TBGEO_LOCATION tables and the flow on impacts to end users.

Note this integration option will not work with CiA Maps enabled. Integration with CiA Maps enabled supports configuration with the FID mapkey only.

Requirements for Composite mapkey integration:

Ensure any IntraMaps search and mapkey queries on SpatialCatalogue generated selection layers reference the TBGEO_LOCATION mapkey field.
Raise a Spatial support case requesting your CiA replication process uses the legacy mapkey value.
If using your own generated selection layers, you will need to ensure the mapkey field is populated with the legacy composite mapkey value in your data extracts from CiA.
When ready to switch application integration settings, enable the Use Legacy Mapkey option in CiA under Spatial Configuration>Embedded Mapping>Application Linking