SearchAddresses.sql

/*
    Installs fast address search for dbo.AddressLookup.

    Run this script after loading or replacing AddressLookup because --replace
    publishes a new physical table. Full-Text Search must be installed on the
    SQL Server instance.

*/

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

IF OBJECT_ID(N'dbo.AddressLookup', N'U') IS NULL
    THROW 51000, 'dbo.AddressLookup does not exist. Run the G-NAF loader first.', 1;

IF FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') <> 1
    THROW 51001, 'SQL Server Full-Text Search is not installed.', 1;
GO

IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = N'GnafAddressSearch')
    CREATE FULLTEXT CATALOG GnafAddressSearch AS DEFAULT;
GO

IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID(N'dbo.AddressLookup'))
BEGIN
    DECLARE @KeyIndex sysname =
    (
        SELECT TOP (1) [name]
        FROM sys.indexes AS candidate
        WHERE candidate.object_id = OBJECT_ID(N'dbo.AddressLookup')
          AND candidate.is_unique = 1
          AND candidate.has_filter = 0
          AND candidate.[type] IN (1, 2)
          AND 1 =
          (
              SELECT COUNT(*)
              FROM sys.index_columns AS keyColumn
              WHERE keyColumn.object_id = candidate.object_id
                AND keyColumn.index_id = candidate.index_id
                AND keyColumn.key_ordinal > 0
          )
        ORDER BY candidate.is_primary_key DESC, candidate.index_id
    );

    IF @KeyIndex IS NULL
        THROW 51002, 'AddressLookup requires a non-filtered unique single-column index for Full-Text Search.', 1;

    DECLARE @CreateFullTextIndex nvarchar(max) =
        N'CREATE FULLTEXT INDEX ON dbo.AddressLookup
          (
              AddressLabel LANGUAGE 3081
          )
          KEY INDEX ' + QUOTENAME(@KeyIndex) + N'
          ON GnafAddressSearch
          WITH (CHANGE_TRACKING = AUTO, STOPLIST = OFF);';

    EXEC sys.sp_executesql @CreateFullTextIndex;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SearchAddresses
    @Search nvarchar(150),
    @Top int = 20
AS
BEGIN
    SET NOCOUNT ON;

    IF NULLIF(LTRIM(RTRIM(@Search)), N'') IS NULL
        THROW 51003, '@Search cannot be empty.', 1;

    IF @Top < 1 OR @Top > 1000
        THROW 51004, '@Top must be between 1 and 1000.', 1;

    -- Work in the same casing as the G-NAF labels and remove punctuation that
    -- SQL Server's word breaker treats as token separators.
    DECLARE @Input nvarchar(4000) = UPPER(LTRIM(RTRIM(@Search)));
    SET @Input = N' ' + @Input + N' ';
    SET @Input = REPLACE(@Input, N' CUL-DE-SAC ', N' CSAC ');
    SET @Input = REPLACE(@Input, N' CUL DE SAC ', N' CSAC ');
    SET @Input = REPLACE(@Input, N' RIGHT OF WAY ', N' ROFW ');
    SET @Input = REPLACE(@Input, N' SERVICE WAY ', N' SVWY ');
    SET @Input = REPLACE(@Input, N'.', N' ');
    SET @Input = REPLACE(@Input, N',', N' ');
    SET @Input = REPLACE(@Input, N';', N' ');
    SET @Input = REPLACE(@Input, N':', N' ');
    SET @Input = REPLACE(@Input, N'/', N' ');
    SET @Input = REPLACE(@Input, N'\', N' ');
    SET @Input = REPLACE(@Input, N'-', N' ');
    SET @Input = REPLACE(@Input, N'(', N' ');
    SET @Input = REPLACE(@Input, N')', N' ');
    SET @Input = REPLACE(@Input, N'[', N' ');
    SET @Input = REPLACE(@Input, N']', N' ');
    SET @Input = REPLACE(@Input, N'"', N' ');

    WHILE CHARINDEX(N'  ', @Input) > 0
        SET @Input = REPLACE(@Input, N'  ', N' ');

    SET @Input = LTRIM(RTRIM(@Input));

    DECLARE @Normalised nvarchar(4000) = N'';
    DECLARE @FullTextQuery nvarchar(4000) = N'';
    DECLARE @Position int;
    DECLARE @Token nvarchar(150);

    -- Convert common full Australian street types to the abbreviations stored
    -- in AddressLabel. Existing abbreviations pass through unchanged.
    WHILE LEN(@Input) > 0
    BEGIN
        SET @Position = CHARINDEX(N' ', @Input);
        IF @Position = 0
        BEGIN
            SET @Token = @Input;
            SET @Input = N'';
        END
        ELSE
        BEGIN
            SET @Token = LEFT(@Input, @Position - 1);
            SET @Input = LTRIM(SUBSTRING(@Input, @Position + 1, 4000));
        END;

        SET @Token = CASE @Token
            WHEN N'ACCESS' THEN N'ACCS'
            WHEN N'AIRWALK' THEN N'AWLK'
            WHEN N'ALLEY' THEN N'ALLY'
            WHEN N'ALLEYWAY' THEN N'ALWY'
            WHEN N'AMBLE' THEN N'AMBL'
            WHEN N'APPROACH' THEN N'APP'
            WHEN N'ARCADE' THEN N'ARC'
            WHEN N'ARTERIAL' THEN N'ARTL'
            WHEN N'ARTERY' THEN N'ARTY'
            WHEN N'AVENUE' THEN N'AV'
            WHEN N'BEACH' THEN N'BCH'
            WHEN N'BOARDWALK' THEN N'BWLK'
            WHEN N'BOULEVARD' THEN N'BVD'
            WHEN N'BRACE' THEN N'BR'
            WHEN N'BRANCH' THEN N'BRAN'
            WHEN N'BREAK' THEN N'BRK'
            WHEN N'BRIDGE' THEN N'BDGE'
            WHEN N'BROADWALK' THEN N'BRDWLK'
            WHEN N'BROADWAY' THEN N'BDWY'
            WHEN N'BYPASS' THEN N'BYPA'
            WHEN N'BYWAY' THEN N'BYWY'
            WHEN N'CAUSEWAY' THEN N'CSWY'
            WHEN N'CENTRE' THEN N'CTR'
            WHEN N'CENTREWAY' THEN N'CNWY'
            WHEN N'CHASE' THEN N'CH'
            WHEN N'CIRCLE' THEN N'CIR'
            WHEN N'CIRCUIT' THEN N'CCT'
            WHEN N'CIRCUS' THEN N'CRCS'
            WHEN N'CLOSE' THEN N'CL'
            WHEN N'COLONNADE' THEN N'CLDE'
            WHEN N'COMMON' THEN N'CMMN'
            WHEN N'CONCOURSE' THEN N'CON'
            WHEN N'CORNER' THEN N'CNR'
            WHEN N'CORSO' THEN N'CSO'
            WHEN N'COURT' THEN N'CT'
            WHEN N'COURTYARD' THEN N'CTYD'
            WHEN N'COVE' THEN N'COVE'
            WHEN N'CRESCENT' THEN N'CR'
            WHEN N'CREST' THEN N'CRST'
            WHEN N'CROSSING' THEN N'CRSG'
            WHEN N'CRUISEWAY' THEN N'CUWY'
            WHEN N'CUTTING' THEN N'CUTT'
            WHEN N'DEVIATION' THEN N'DE'
            WHEN N'DISTRIBUTOR' THEN N'DSTR'
            WHEN N'DRIVE' THEN N'DR'
            WHEN N'DRIVEWAY' THEN N'DVWY'
            WHEN N'ENTRANCE' THEN N'ENT'
            WHEN N'ESPLANADE' THEN N'ESP'
            WHEN N'EXPRESSWAY' THEN N'EXP'
            WHEN N'FAIRWAY' THEN N'FAWY'
            WHEN N'FIREBREAK' THEN N'FBRK'
            WHEN N'FIRELINE' THEN N'FLNE'
            WHEN N'FIRETRACK' THEN N'FTRK'
            WHEN N'FOOTWAY' THEN N'FTWY'
            WHEN N'FORESHORE' THEN N'FSHR'
            WHEN N'FORMATION' THEN N'FORM'
            WHEN N'FREEWAY' THEN N'FWY'
            WHEN N'FRONTAGE' THEN N'FRTG'
            WHEN N'GARDEN' THEN N'GDN'
            WHEN N'GARDENS' THEN N'GDNS'
            WHEN N'GATEWAY' THEN N'GWY'
            WHEN N'GLADE' THEN N'GLDE'
            WHEN N'GRANGE' THEN N'GRA'
            WHEN N'GROVE' THEN N'GR'
            WHEN N'GULLY' THEN N'GLY'
            WHEN N'HEIGHTS' THEN N'HTS'
            WHEN N'HIGHROAD' THEN N'HIRD'
            WHEN N'HIGHWAY' THEN N'HWY'
            WHEN N'INTERCHANGE' THEN N'INTG'
            WHEN N'JUNCTION' THEN N'JNC'
            WHEN N'LANDING' THEN N'LDG'
            WHEN N'LANEWAY' THEN N'LNWY'
            WHEN N'LOOKOUT' THEN N'LKT'
            WHEN N'MOTORWAY' THEN N'MTWY'
            WHEN N'OUTLOOK' THEN N'OTLK'
            WHEN N'PARADE' THEN N'PDE'
            WHEN N'PARKWAY' THEN N'PWY'
            WHEN N'PASSAGE' THEN N'PSGE'
            WHEN N'PATHWAY' THEN N'PWAY'
            WHEN N'PLACE' THEN N'PL'
            WHEN N'PLAZA' THEN N'PLZA'
            WHEN N'POCKET' THEN N'PKT'
            WHEN N'POINT' THEN N'PNT'
            WHEN N'PROMENADE' THEN N'PROM'
            WHEN N'QUADRANT' THEN N'QDRT'
            WHEN N'QUAY' THEN N'QY'
            WHEN N'QUAYS' THEN N'QYS'
            WHEN N'RAMBLE' THEN N'RMBL'
            WHEN N'RESERVE' THEN N'RES'
            WHEN N'RETREAT' THEN N'RTT'
            WHEN N'RIDGE' THEN N'RDGE'
            WHEN N'RISE' THEN N'RISE'
            WHEN N'ROAD' THEN N'RD'
            WHEN N'ROADS' THEN N'RDS'
            WHEN N'ROADWAY' THEN N'RDWY'
            WHEN N'ROUTE' THEN N'RTE'
            WHEN N'SERVICEWAY' THEN N'SVWY'
            WHEN N'SQUARE' THEN N'SQ'
            WHEN N'STEPS' THEN N'STPS'
            WHEN N'STREET' THEN N'ST'
            WHEN N'SUBWAY' THEN N'SBWY'
            WHEN N'TERRACE' THEN N'TCE'
            WHEN N'THOROUGHFARE' THEN N'THFR'
            WHEN N'TOLLWAY' THEN N'TLWY'
            WHEN N'TRACK' THEN N'TRK'
            WHEN N'TRAIL' THEN N'TRL'
            WHEN N'TRAVERSE' THEN N'TVSE'
            WHEN N'UNDERPASS' THEN N'UPAS'
            WHEN N'VIADUCT' THEN N'VIAD'
            WHEN N'VIEW' THEN N'VIEW'
            WHEN N'VIEWS' THEN N'VWS'
            WHEN N'VISTA' THEN N'VSTA'
            WHEN N'WALKWAY' THEN N'WKWY'
            WHEN N'WHARF' THEN N'WHRF'
            ELSE @Token
        END;

        SET @Normalised = CONCAT(@Normalised, CASE WHEN @Normalised = N'' THEN N'' ELSE N' ' END, @Token);
        SET @FullTextQuery = CONCAT(
            @FullTextQuery,
            CASE WHEN @FullTextQuery = N'' THEN N'' ELSE N' AND ' END,
            N'"', REPLACE(@Token, N'"', N''),
            CASE WHEN @Input = N'' THEN N'*"' ELSE N'"' END);
    END;

    -- Escape LIKE metacharacters. This prefix preference improves ranking but
    -- the candidate set itself comes from the full-text index.
    DECLARE @LikePrefix nvarchar(4000) = REPLACE(@Normalised, N'~', N'~~');
    SET @LikePrefix = REPLACE(@LikePrefix, N'%', N'~%');
    SET @LikePrefix = REPLACE(@LikePrefix, N'_', N'~_');
    SET @LikePrefix = REPLACE(@LikePrefix, N'[', N'~[');

    -- Initial full-text population is asynchronous. Searching it before the
    -- population completes silently omits rows that have not yet been indexed.
    -- The ordinary AddressLabel index can still provide a fast prefix seek.
    IF FULLTEXTCATALOGPROPERTY(N'GnafAddressSearch', N'PopulateStatus') <> 0
    BEGIN
        SELECT TOP (@Top)
            address.AddressDetailPid,
            address.AddressLabel,
            address.FlatType,
            address.FlatNumber,
            address.StreetNumberFirst,
            address.StreetNumberLast,
            address.StreetName,
            address.StreetType,
            address.LocalityName,
            address.[State],
            address.Postcode,
            address.Longitude,
            address.Latitude,
            0 AS SearchRank
        FROM dbo.AddressLookup AS address
        WHERE address.AddressLabel LIKE @LikePrefix + N'%' ESCAPE N'~'
        ORDER BY address.AddressLabel;

        RETURN;
    END;

    DECLARE @CandidateLimit int = @Top * 20;

    SELECT TOP (@Top)
        address.AddressDetailPid,
        address.AddressLabel,
        address.FlatType,
        address.FlatNumber,
        address.StreetNumberFirst,
        address.StreetNumberLast,
        address.StreetName,
        address.StreetType,
        address.LocalityName,
        address.[State],
        address.Postcode,
        address.Longitude,
        address.Latitude,
        matches.[RANK] AS SearchRank
    FROM CONTAINSTABLE(dbo.AddressLookup, AddressLabel, @FullTextQuery, @CandidateLimit) AS matches
    INNER JOIN dbo.AddressLookup AS address
        ON address.AddressDetailPid = matches.[KEY]
    ORDER BY
        CASE WHEN address.AddressLabel LIKE @LikePrefix + N'%' ESCAPE N'~' THEN 0 ELSE 1 END,
        matches.[RANK] DESC,
        address.AddressLabel;
END;
GO