TB_AddressGeocodeCache

<None>

This utility table stores address -> longitude/latitude mappings for addresses. Stores also a hash for the address. The addresses that are stored are the original, unclean addresses used to generate the hash. Lookups may be done based on the address, but since none of them are currently indexed, this could be slow. The hash is better.

Because different databases potentially compute hashes differently, this table has both a TB_Hash and TB_HashType field. Both together are the primary key. If the hash alone were the primary key, we couldn't use different types of hashes.

If one is using SQL Server, it is recommended that the following function (defined in this model) be used to compute the hash: F_CalculateAddressSHA1XSumVarChr. The hash type should be set to 'SQL Server HashBytes SHA1'.

See also the ETL code that populates this table, which defines some appropriate hash-calculating functions for use with SQL Server (the SSIS ETL package is TBGeoCache.dtsx).

Finally, see documentation on VW_AddressGeoCodeCache, which describes various approaches for using the data here.

SQL Server functions useful for calculating checksums for use in maintaining this table are also included in this model, but the function definitions must be uploaded to the SQL Server instance you are fetching address data from, and permission must be granted to execute them, as in:

grant execute on dbo.F_CALCULATEADDRESSSHA1XSUMVARCHR to [ADS\<groupname>];
grant execute on dbo.F_CALCULATEADDRESSSHA1XSUM to [ADS\<groupname>];