Civica - FastGazetteer (Innogistic)

System Category	Addressing
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

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.