Idox - iManage Gazetteer

Owned by Kim Stimpson
Last updated: 02 Jan, 2024 by Andrew Bailey
System CategoryAddressing
Source

Corporate Land & Property Gazetteer

Available since
2005
Organisation

The following details the database information required for using the iManage Gazetteer as the source for your Astun Location Lookup. This information should be used in conjunction with the Configure Location Lookups topic.

Requires the Astun PG Installer applied to the Data Share database with the Location Search and Symphony or Symphony (Welsh) options selected.

Database table for BuildAddresses


Field

Description

UPRN

Unique Property Reference Number

EASTING

Coordinate

NORTHING

Coordinate

ORGANISATION

Organisation

LPI_USRN

Unique Street Reference Number

POSTCODE_REF

Postcode Reference

POSTCODE

Postcode

POSTTOWN_REF

Post Town Reference

POSTTOWN

Post Town

SAON_START_NUM

Secondary Addressable Object Start Number

SAON_START_SUFFIX

Secondary Addressable Object Start Suffix

SAON_END_NUM

Secondary Addressable Object End Number

SAON_END_SUFFIX

Secondary Addressable Object End Suffix

SAON_TEXT

Secondary Addressable Object Text

PAON_START_NUM

Primary Addressable Object Start Number

PAON_START_SUFFIX

Primary Addressable Object Start Suffix

PAON_END_NUM

Primary Addressable Object End Number

PAON_END_SUFFIX

Primary Addressable Object End Suffix

PAON_TEXT

Primary Addressable Object Text

DESCRIPTION

Street text

TOWN_REF

Town Reference

TOWN

Town

LOC_REF

Locality Reference

LOC

Locality

COUNTY_REF

County Reference

COUNTY

County

Tables Used

This uses a number of tables BLPU, LPI, LU_LOCALITY, LU_POSTCODE, LU_POSTTOWN, LU_TOWN and STREET_DESCRIPTOR.

SQL Statements

In iShare ETL version 6 issues may be detected with boolean fields and text fields contain datetime strings. For these reasons more complex SQL statements are required than normal. The statements below are from a two language system, moving data via the ETL into the SDW's datashare_gis schema. 

TableSQL Statement
BLPUSELECT CAST(UPRN AS bigint) AS UPRN, CAST(BLPU_VER AS integer) AS BLPU_VER, CAST(BLPU_CUR AS integer) AS BLPU_CUR, CAST(ENTRY_DATE AS integer) AS ENTRY_DATE, CAST(LAST_UPDATE_DATE AS integer) AS LAST_UPDATE_DATE, CAST(START_DATE AS integer) AS START_DATE, CAST(END_DATE AS integer) AS END_DATE, CAST(EASTING AS varchar(255)) AS EASTING, CAST(NORTHING AS varchar(255)) AS NORTHING, CAST(RPA AS varchar(255)) AS RPA, CAST(LOCAL_CUSTODIAN AS integer) AS LOCAL_CUSTODIAN, CAST(LOGICAL_STATUS AS varchar(255)) AS LOGICAL_STATUS, CAST(ORGANISATION_MATCH_STATUS AS text) AS ORGANISATION_MATCH_STATUS, CAST(PARTIAL_MATCH_STATUS AS text) AS PARTIAL_MATCH_STATUS, CAST(MAPINFO_ID AS integer) AS MAPINFO_ID, CAST(PRO_ORDER AS integer) AS PRO_ORDER, CAST(CHANGE_TYPE AS text) AS CHANGE_TYPE, CAST(ISPARENT AS integer) AS ISPARENT, CAST(ISCHILD AS integer) AS ISCHILD, CAST(IE_NO AS integer) AS IE_NO, CAST(NEVERCHILD AS integer) AS NEVERCHILD, CAST(NEVEREXPORT AS integer) AS NEVEREXPORT, CAST(UPDATED AS integer) AS UPDATED, CAST(BLPU_STATE AS integer) AS BLPU_STATE, CAST(BLPU_STATE_DATE AS integer) AS BLPU_STATE_DATE, CAST(BLPU_CLASS AS text) AS BLPU_CLASS, CAST(PARENT_UPRN AS varchar(255)) AS PARENT_UPRN, CAST(ORGANISATION AS text) AS ORGANISATION, CAST(WARD_CODE AS text) AS WARD_CODE, CAST(PARISH_CODE AS text) AS PARISH_CODE, CAST(CUSTODIAN_ONE AS integer) AS CUSTODIAN_ONE, CAST(CUSTODIAN_TWO AS integer) AS CUSTODIAN_TWO, CAST(CAN_KEY AS text) AS CAN_KEY, CAST(LAST_UPDATED AS datetime2) AS LAST_UPDATED, CAST(CROSS_REF_DETAILS AS text) AS CROSS_REF_DETAILS, CAST(USAGE AS text) AS USAGE, CAST(SITE_SURVEY AS integer) AS SITE_SURVEY, CAST(INSERTED_TIMESTAMP AS datetime2) AS INSERTED_TIMESTAMP, CAST(INSERTED_USER AS text) AS INSERTED_USER, CAST(BLPU_LEVEL AS varchar(255)) AS BLPU_LEVEL FROM BLPU
LPISELECT CAST(UPRN AS bigint) AS UPRN, CAST(START_DATE AS integer) AS START_DATE, CAST(ENTRY_DATE AS integer) AS ENTRY_DATE, CAST(LAST_UPDATE_DATE AS integer) AS LAST_UPDATE_DATE, CAST(END_DATE AS integer) AS END_DATE, CAST(SAON_START_NUM AS integer) AS SAON_START_NUM, CAST(SAON_START_SUFFIX AS text) AS SAON_START_SUFFIX, CAST(SAON_END_NUM AS integer) AS SAON_END_NUM, CAST(SAON_END_SUFFIX AS text) AS SAON_END_SUFFIX, CAST(SAON_TEXT AS text) AS SAON_TEXT, CAST(PAON_START_NUM AS integer) AS PAON_START_NUM, CAST(PAON_START_SUFFIX AS text) AS PAON_START_SUFFIX, CAST(PAON_END_NUM AS integer) AS PAON_END_NUM, CAST(PAON_END_SUFFIX AS text) AS PAON_END_SUFFIX, CAST(PAON_TEXT AS text) AS PAON_TEXT, CAST(LPI_USRN AS integer) AS LPI_USRN, CAST(POSTCODE_REF AS integer) AS POSTCODE_REF, CAST(POSTTOWN_REF AS integer) AS POSTTOWN_REF, CAST(LPI_LEVEL AS text) AS LPI_LEVEL, CAST(OFFICIAL_ADDRESS_MAKER AS text) AS OFFICIAL_ADDRESS_MAKER, CAST(LPI_CUR AS integer) AS LPI_CUR, CAST(LOGICAL_STATUS AS varchar(255)) AS LOGICAL_STATUS, CAST(ORGANISATION_MATCH_STATUS AS text) AS ORGANISATION_MATCH_STATUS, CAST(PARTIAL_MATCH_STATUS AS text) AS PARTIAL_MATCH_STATUS, CAST(LPI_CHECK_STATUS AS text) AS LPI_CHECK_STATUS, CAST(MAPINFO_ID AS integer) AS MAPINFO_ID, CAST(PRO_ORDER AS integer) AS PRO_ORDER, CAST(CHANGE_TYPE AS text) AS CHANGE_TYPE, CAST(DONTDELETE AS integer) AS DONTDELETE, CAST(IE_NO AS integer) AS IE_NO, CAST(NEVEREXPORT AS integer) AS NEVEREXPORT, CAST(LANGUAGE AS text) AS LANGUAGE, CAST(POSTALLY_ADDRESSABLE AS text) AS POSTALLY_ADDRESSABLE, CAST(CUSTODIAN_ONE AS integer) AS CUSTODIAN_ONE, CAST(CUSTODIAN_TWO AS integer) AS CUSTODIAN_TWO, CAST(CAN_KEY AS text) AS CAN_KEY, CAST(LPI_KEY AS text) AS LPI_KEY, CAST(LAST_UPDATED AS datetime2) AS LAST_UPDATED, CAST(ADDRESS AS text) AS ADDRESS, CAST(BS7666_ADDRESS AS text) AS BS7666_ADDRESS, CAST(SAON_DETAILS AS text) AS SAON_DETAILS, CAST(PAON_DETAILS AS text) AS PAON_DETAILS, CAST(TEMP_LINKED AS text) AS TEMP_LINKED, CAST(SEARCH_ADDRESS AS text) AS SEARCH_ADDRESS, CAST(INSERTED_TIMESTAMP AS datetime2) AS INSERTED_TIMESTAMP, CAST(INSERTED_USER AS text) AS INSERTED_USER FROM LPI WHERE LANGUAGE='ENG' AND LOGICAL_STATUS<>'8'
LU_COUNTYSELECT COUNTY_REF, COUNTY, CAST(HISTORIC AS integer) AS HISTORIC, LANGUAGE, LINKED_REF FROM LU_COUNTY WHERE LANGUAGE='ENG'
LU_LOCALITYSELECT LOC_REF, LOC, CAST(HISTORIC AS integer) AS HISTORIC, LANGUAGE, LINKED_REF FROM LU_LOCALITY WHERE LANGUAGE='ENG'
LU_POSTCODESELECT POSTCODE_REF, POSTCODE, CAST(HISTORIC AS integer) AS HISTORIC FROM LU_POSTCODE
LU_TOWNSELECT TOWN_REF, TOWN, CAST(HISTORIC AS integer) AS HISTORIC, LANGUAGE, LINKED_REF FROM LU_TOWN WHERE LANGUAGE='ENG'
LU_POSTTOWNSELECT POSTTOWN_REF, POSTTOWN, CAST(HISTORIC AS integer) AS HISTORIC, LANGUAGE, LINKED_REF FROM LU_POSTTOWN WHERE LANGUAGE='ENG'
STREET_DESCRIPTORSELECT USRN, LANGUAGE, DESCRIPTION, LOC_REF, TOWN_REF, COUNTY_REF, PK_ID, CAST(NEVEREXPORT AS integer) AS NEVEREXPORT, CHANGE_TYPE, IE_NO, PRO_ORDER, CAST(LAST_UPDATED AS datetime2) AS LAST_UPDATED, CAST(INSERTED_TIMESTAMP AS datetime2) AS INSERTED_TIMESTAMP, INSERTED_USER, ISLAND FROM STREET_DESCRIPTOR WHERE LANGUAGE='ENG'

On the GIS server the follow statements are then needed to convert boolean fields back from integer fields. These data share connections store the tables in the datashare schema in the SDW.

TableSQL Statement
BLPUSELECT "UPRN", "BLPU_VER", CAST("BLPU_CUR" AS boolean) AS "BLPU_CUR", "ENTRY_DATE", "LAST_UPDATE_DATE", "START_DATE", "END_DATE", "EASTING", "NORTHING", "RPA", "LOCAL_CUSTODIAN", "LOGICAL_STATUS", "ORGANISATION_MATCH_STATUS", "PARTIAL_MATCH_STATUS", "MAPINFO_ID", "PRO_ORDER", "CHANGE_TYPE", CAST("ISPARENT" AS boolean) AS "ISPARENT", CAST("ISCHILD" AS boolean) AS "ISCHILD", "IE_NO", CAST("NEVERCHILD" AS boolean) AS "NEVERCHILD", CAST("NEVEREXPORT" AS boolean) AS "NEVEREXPORT", CAST("UPDATED" AS boolean) AS "UPDATED", "BLPU_STATE", "BLPU_STATE_DATE", "BLPU_CLASS", "PARENT_UPRN", "ORGANISATION", "WARD_CODE", "PARISH_CODE", "CUSTODIAN_ONE", "CUSTODIAN_TWO", "CAN_KEY", "LAST_UPDATED", "CROSS_REF_DETAILS", "USAGE", CAST("SITE_SURVEY" AS boolean) AS "SITE_SURVEY", "INSERTED_TIMESTAMP", "INSERTED_USER", "BLPU_LEVEL" FROM datashare_gis."BLPU"
LPISELECT "UPRN", "START_DATE", "ENTRY_DATE", "LAST_UPDATE_DATE", "END_DATE", "SAON_START_NUM", "SAON_START_SUFFIX", "SAON_END_NUM", "SAON_END_SUFFIX", "SAON_TEXT", "PAON_START_NUM", "PAON_START_SUFFIX", "PAON_END_NUM", "PAON_END_SUFFIX", "PAON_TEXT", "LPI_USRN", "POSTCODE_REF", "POSTTOWN_REF", "LPI_LEVEL", "OFFICIAL_ADDRESS_MAKER", CAST("LPI_CUR" AS boolean) AS "LPI_CUR", "LOGICAL_STATUS", "ORGANISATION_MATCH_STATUS", "PARTIAL_MATCH_STATUS", "LPI_CHECK_STATUS", "MAPINFO_ID", "PRO_ORDER", "CHANGE_TYPE", CAST("DONTDELETE" AS boolean) AS "DONTDELETE", "IE_NO", CAST("NEVEREXPORT" AS boolean) AS "NEVEREXPORT", "LANGUAGE", "POSTALLY_ADDRESSABLE", "CUSTODIAN_ONE", "CUSTODIAN_TWO", "CAN_KEY", "LPI_KEY", "LAST_UPDATED", "ADDRESS", "BS7666_ADDRESS", "SAON_DETAILS", "PAON_DETAILS", "TEMP_LINKED", "SEARCH_ADDRESS", "INSERTED_TIMESTAMP", "INSERTED_USER"
FROM datashare_gis."LPI";
LU_COUNTYSELECT "COUNTY_REF", "COUNTY", CAST("HISTORIC" as boolean) AS "HISTORIC", "LANGUAGE", "LINKED_REF" FROM "datashare_gis"."LU_COUNTY"
LU_POSTCODESELECT "POSTCODE_REF", "POSTCODE", CAST("HISTORIC" AS boolean) AS "HISTORIC" FROM "datashare_gis"."LU_POSTCODE"
LU_TOWNSELECT "TOWN_REF", "TOWN", CAST("HISTORIC" AS boolean) AS "HISTORIC", "LANGUAGE", "LINKED_REF" FROM "datashare_gis"."LU_TOWN"
LU_POSTTOWNSELECT "POSTTOWN_REF", "POSTTOWN", CAST("HISTORIC" AS boolean) AS "HISTORIC", "LANGUAGE", "LINKED_REF" FROM "datashare_gis"."LU_POSTTOWN"
STREET_DESCRIPTORSELECT "USRN", "LANGUAGE", "DESCRIPTION", "LOC_REF", "TOWN_REF", "COUNTY_REF", "PK_ID", CAST("NEVEREXPORT" AS boolean) AS "NEVEREXPORT", "CHANGE_TYPE", "IE_NO", "PRO_ORDER", "LAST_UPDATED", "INSERTED_TIMESTAMP", "INSERTED_USER", "ISLAND" FROM "datashare_gis"."STREET_DESCRIPTOR"
LU_LOCALITYSELECT "LOC_REF", "LOC", CAST("HISTORIC" AS boolean) AS "HISTORIC", "LANGUAGE", "LINKED_REF" FROM "datashare_gis"."LU_LOCALITY"


In all cases the simplest method is to simply import all the data:

Table

SQL Statement

BLPU

SELECT * from BLPU

LPI

SELECT * FROM LPI

LU_COUNTY

SELECT * FROM LU_COUNTY

LU_LOCALITY

SELECT * FROM LU_LOCALITY

LU_POSTCODE

SELECT * FROM LU_POSTCODE

LU_TOWN

SELECT * FROM LU_TOWN

LU_POSTTOWN

SELECT * FROM LU_POSTTOWN

STREET_DESCRIPTOR

SELECT * FROM STREET_DESCRIPTOR

In the case where multiple language versions of the address are present in the database we need to select the address where the language is English, typically as follows:

Table

SQL Statement

BLPU

SELECT * from BLPU

LPI

SELECT * FROM LPI WHERE LANGUAGE='ENG'

LU_COUNTY

SELECT * FROM LU_COUNTY WHERE LANGUAGE='ENG'

LU_LOCALITY

SELECT * FROM LU_LOCALITY WHERE LANGUAGE='ENG'

LU_POSTCODE

SELECT * FROM LU_POSTCODE

LU_TOWN

SELECT * FROM LU_TOWN WHERE LANGUAGE='ENG'

LU_POSTTOWN

SELECT * FROM LU_POSTTOWN WHERE LANGUAGE='ENG'

STREET_DESCRIPTOR

SELECT * FROM STREET_DESCRIPTOR WHERE LANGUAGE='ENG'

If implementing a Welsh authority then two sets of data needs to be extracted. The NAMES of the Data Share Data Sources needs to have _WELSH appended as follows:

Do these first otherwise the Studio gets confused

  

Table

SQL Statement

BLPU_WELSH

SELECT * from BLPU

LPI_WELSH

SELECT * FROM LPI WHERE LANGUAGE='CYM'

LU_COUNTY_WELSH

SELECT * FROM LU_COUNTY WHERE LANGUAGE='CYM'

LU_LOCALITY_WELSH

SELECT * FROM LU_LOCALITY WHERE LANGUAGE='CYM'

LU_POSTCODE_WELSH

SELECT * FROM LU_POSTCODE

LU_TOWN_WELSH

SELECT * FROM LU_TOWN WHERE LANGUAGE='CYM'

LU_POSTTOWN_WELSH

SELECT * FROM LU_POSTTOWN WHERE LANGUAGE='CYM'

STREET_DESCRIPTOR_WELSH

SELECT * FROM STREET_DESCRIPTOR WHERE LANGUAGE='CYM'

Notes

The minimum fields required are as follows:


Table

SQL Statement

BLPU

SELECT UPRN, EASTING, NORTHING, ORGANISATION FROM BLPU

LPI

SELECT UPRN, SAON_START_NUM, SAON_START_SUFFIX, SAON_END_NUM, SAON_END_SUFFIX, SAON_TEXT, PAON_START_NUM, PAON_START_SUFFIX, PAON_END_NUM, PAON_END_SUFFIX, PAON_TEXT, LPI_USRN, POSTCODE_REF, POSTTOWN_REF FROM LPI

LU_COUNTY

SELECT COUNTY_REF, COUNTY FROM LU_COUNTY

LU_LOCALITY

SELECT LOC_REF, LOC FROM LU_LOCALITY

LU_POSTCODE

SELECT POSTCODE_REF, POSTCODE FROM LU_POSTCODE

LU_TOWN

SELECT TOWN_REF, TOWN FROM LU_TOWN

LU_POSTTOWN

SELECT POSTTOWN_REF, POSTTOWN FROM LU_POSTTOWN

STREET_DESCRIPTOR

SELECT USRN, DESCRIPTION, LOC_REF, TOWN_REF, COUNTY_REF FROM STREET_DESCRIPTOR