Civica - FastGazetteer (Innogistic)

System CategoryAddressing
Source

Corporate Land & Property Gazetteer

Available since
2008
Organisation

v6.0.0

These stored procedures have been deprecated from v6.0.0

With Innogistic sites running on SQL Server we have a 'proxy' type SQL Server database, normally called iShare, that references all the other SQL Server databases. This way if a customer needs to create custom views etc. they have one point of call.

The following details the database information required for using the Civica FastGazetteer 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 Innogistic options selected.

Database view for BuildAddresses


Field

Description

UPRN

Unique Property Reference Number

USRN

Unique Street Reference Number

EASTING

Coordinate

NORTHING

Coordinate

POSTCODE

Postcode

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

POSTTOWN

Town

TOWN

Town

LOC

Locality

DISTRICT

County

COUNTY

County

SQL View in the iShare Database

Please note this presumes the proxy database is iShare and other databases are named such as [FastGazetteer5Live]

This should be a Data Share Data Source called vwAddresses_iShare

USE [iShare]
GO
/****** Object:  View [dbo].[vwAddresses_iShare]    Script Date: 10/28/2009 17:55:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwAddresses_iShare]
AS
SELECT     FastGAZETTEER5Live.dbo.LPI.Uprn AS UPRN, CAST(FastGAZETTEER5Live.dbo.DBA_StreetIdentifier.USRN AS VARCHAR) AS USRN, 
                      CAST(FastGAZETTEER5Live.dbo.BLPU.Xref AS VARCHAR) AS EASTING, CAST(FastGAZETTEER5Live.dbo.BLPU.YRef AS VARCHAR) AS NORTHING, 
                      UPPER(FastGAZETTEER5Live.dbo.LPI.Postcode) AS POSTCODE, CASE WHEN [FastGazetteer5Live].dbo.LPI.SAOStartNumber = 0 THEN NULL 
                      ELSE CAST(RTRIM(LTRIM([FastGazetteer5Live].dbo.LPI.SAOStartNumber)) AS VARCHAR) END AS SAON_START_NUM, 
                      LOWER(RTRIM(LTRIM(FastGAZETTEER5Live.dbo.LPI.SAOStartSuffix))) AS SAON_START_SUFFIX, 
                      CASE WHEN [FastGazetteer5Live].dbo.LPI.SAOEndNumber = 0 THEN NULL ELSE CAST(RTRIM(LTRIM([FastGazetteer5Live].dbo.LPI.SAOEndNumber)) 
                      AS VARCHAR) END AS SAON_END_NUM, LOWER(RTRIM(LTRIM(FastGAZETTEER5Live.dbo.LPI.SAOEndSuffix))) AS SAON_END_SUFFIX, 
                      RTRIM(LTRIM(FastGAZETTEER5Live.dbo.LPI.SAOPropertyName)) AS SAON_TEXT, 
                      CASE WHEN [FastGazetteer5Live].dbo.LPI.PAOStartNumber = 0 THEN NULL ELSE CAST(RTRIM(LTRIM([FastGazetteer5Live].dbo.LPI.PAOStartNumber)) 
                      AS VARCHAR) END AS PAON_START_NUM, LOWER(RTRIM(LTRIM(FastGAZETTEER5Live.dbo.LPI.PAOStartSuffix))) AS PAON_START_SUFFIX, 
                      CASE WHEN [FastGazetteer5Live].dbo.LPI.PAOEndNumber = 0 THEN NULL ELSE CAST(RTRIM(LTRIM([FastGazetteer5Live].dbo.LPI.PAOEndNumber)) 
                      AS VARCHAR) END AS PAON_END_NUM, LOWER(RTRIM(LTRIM(FastGAZETTEER5Live.dbo.LPI.PAOEndSuffix))) AS PAON_END_SUFFIX, 
                      RTRIM(LTRIM(FastGAZETTEER5Live.dbo.LPI.PAOPropertyName)) AS PAON_TEXT, 
                      CASE WHEN DBA_StreetRef.RefType = 1 THEN RTRIM(LTRIM([FastGazetteer5Live].dbo.DBA_StreetIdentifier.StreetDescriptor)) ELSE NULL 
                      END AS DESCRIPTION, FastGAZETTEER5Live.dbo.LPI.PostTown AS POSTTOWN, 
                      CASE WHEN [FastGazetteer5Live].dbo.DBA_TownIdentifier.TownName = [FastGazetteer5Live].dbo.LPI.PostTown THEN NULL 
                      ELSE RTRIM(LTRIM([FastGazetteer5Live].dbo.DBA_TownIdentifier.TownName)) END AS TOWN, 
                      CASE WHEN [FastGazetteer5Live].dbo.DBA_AreaIdentifier.AreaName NOT LIKE '(%' THEN RTRIM(LTRIM([FastGazetteer5Live].dbo.DBA_AreaIdentifier.AreaName))
                       ELSE NULL END AS LOC, NULL AS DISTRICT, NULL AS ORGANISATION, FastGAZETTEER5Live.dbo.DBA_Counties.CountyName AS COUNTY, 
                      FastGAZETTEER5Live.dbo.LPILogicalStatus.LogicalStatus AS LOGICALSTATUS
FROM         FastGAZETTEER5Live.dbo.DBA_Towns INNER JOIN
                      FastGAZETTEER5Live.dbo.DBA_TownIdentifier ON 
                      FastGAZETTEER5Live.dbo.DBA_Towns.TownRef = FastGAZETTEER5Live.dbo.DBA_TownIdentifier.TownRef INNER JOIN
                      FastGAZETTEER5Live.dbo.DBA_StreetIdentifier INNER JOIN
                      FastGAZETTEER5Live.dbo.DBA_StreetRef ON 
                      FastGAZETTEER5Live.dbo.DBA_StreetIdentifier.USRN = FastGAZETTEER5Live.dbo.DBA_StreetRef.StreetRefID INNER JOIN
                      FastGAZETTEER5Live.dbo.BLPU INNER JOIN
                      FastGAZETTEER5Live.dbo.LPI ON FastGAZETTEER5Live.dbo.BLPU.UPRN = FastGAZETTEER5Live.dbo.LPI.Uprn INNER JOIN
                      FastGAZETTEER5Live.dbo.LPILogicalStatus ON FastGAZETTEER5Live.dbo.LPI.Logical_status = FastGAZETTEER5Live.dbo.LPILogicalStatus.ID ON 
                      FastGAZETTEER5Live.dbo.DBA_StreetRef.StreetRefID = FastGAZETTEER5Live.dbo.LPI.Usrn INNER JOIN
                      FastGAZETTEER5Live.dbo.DBA_Areas ON 
                      FastGAZETTEER5Live.dbo.DBA_StreetRef.AreaRef = FastGAZETTEER5Live.dbo.DBA_Areas.AreaRef INNER JOIN
                      FastGAZETTEER5Live.dbo.DBA_AreaIdentifier ON 
                      FastGAZETTEER5Live.dbo.DBA_Areas.AreaRef = FastGAZETTEER5Live.dbo.DBA_AreaIdentifier.AreaRef ON 
                      FastGAZETTEER5Live.dbo.DBA_Towns.TownRef = FastGAZETTEER5Live.dbo.DBA_Areas.TownRef LEFT OUTER JOIN
                      FastGAZETTEER5Live.dbo.DBA_Counties ON 
                      FastGAZETTEER5Live.dbo.DBA_Counties.CountyRef = FastGAZETTEER5Live.dbo.DBA_StreetRef.CountyRef
WHERE     (FastGAZETTEER5Live.dbo.BLPU.Logical_Status <= 6) AND (FastGAZETTEER5Live.dbo.LPI.Post_Addressable = 'y') AND 
                      (FastGAZETTEER5Live.dbo.LPI.Pao_Desc NOT LIKE 'street r%') AND (FastGAZETTEER5Live.dbo.LPI.Logical_status <= 6)
 
GO

Postgres

This will be called from the worklow procedure at_wkf_build_from_innogistic. which in turn calls a procedure called at_ls_buildfrom_innogistic to perform formatting of source data and it requires a iShare Data Source called vwAddresses_iShare.