Touchstone 4.0 Database Documentation
tLocation Table
Description

Contains geographical attributes and primary risk characteristics for each location. It serves as a reference point between Location and Contract entities.  All locations entities are tied together by the same Location SID.

Properties
Creation Date5/31/2016 9:46 AM
File GroupPRIMARY
Text File GroupPRIMARY
System Object
Published for Replication
Rows0
Data Space Used0.00 KB
Index Space Used0.00 KB
Columns
 Column NameDescriptionDatatypeLengthAllow NullsDefaultFormula
Primary Key LocationSIDUnique system-generated sequential identifier for the location record.Integer4   
 ContractSID

Unique system-generated sequential identifier for the contract record.

For a complete list of records, see tContract Table.

Integer4   
 ExposureSetSID

Unique system-generated sequential identifier for the exposure set record.

For a complete list of records, see tExposureSet Table.

Integer4   
 ISOBIN

Unique ISO Building Identification Number (BIN).

VarChar14  
 LocationIDUnique user-defined identifier for the location.VarChar100   
 LocationNameUser-defined name for the location.VarChar100  
 ReplacementValueACoverage A replacement value for the location.Double9  
 ReplacementValueBCoverage B replacement value for the location.Double9  
 ReplacementValueCCoverage C replacement value for the location.Double9  
 ReplacementValueDCoverage D replacement value for the location.Double9  
 ReplacementValueDaysCoveredNumber of days covered by the Replacement Value D amountInteger4  
 PerDiemRatePer diem rate for the time element. This value is calculated using the following equation: PerDiemRate = ReplacementValueD / ReplacementValueDaysCovered. 
Note: When the ReplacementValueDaysCovered value is zero, the PerDiemRate is 365.
Double9 
([ReplacementValueD]/case when isnull([ReplacementValueDaysCovered],(0))=(0) then (365.0) else [ReplacementValueDaysCovered] end)
 TotalReplacementValueTotal replacement value for all coverage combined.  This value is calculated using the following equation: Total Replacement Value = ReplacementValueA+ReplacementValueB+ReplacementValueC+ReplacementValueD(Annualized)Double9 
((([ReplacementValueA]+[ReplacementValueB])+[ReplacementValueC])+case when [ReplacementValueDaysCovered]=(0) then [ReplacementValueD] else ((365.0)*[ReplacementValueD])/[ReplacementValueDaysCovered] end)
 PremiumPremium amount. Any values that are less than or equal to 1 will be interpreted as percentages (e.g., 0.12 as 12%).Double9  
 CurrencyCode

ISO 3-character code for the currency.

For a complete list of codes, see tCurrency Table.

Char3   
 TerritoryUser-defined territory.VarChar60  
 IsTenant

Indicates whether the location is covered under a tenant policy.

  • 1 = Yes (covered)
  • 0 = No (not covered)
Boolean1  
 LocationGroupName of the location group.VarChar60  
 IsPrimaryLocation

Indicates whether the location is the primary location in a location group.

  • 1 = Yes (primary location)
  • 0 = No (not primary location)
Boolean1  
 ParentLocationSIDUnique system-generated sequential identifier of the primary location.Integer4  
 GeographySID

Unique system-generated sequential identifier for the geographic record.

For a complete list of records, see tGeography Table.

Integer4  
 CountryCode

ISO 2-character code for the country in which the location resides. 

For a complete list of codes, see tCountry Table.

VarChar15   
 CountryName

Name of the country.

This name corresponds to the CountryCode field.

VarChar255   
 CRESTACode

CRESTA code for the location, when applicable. If CRESTA is not applicable for this country,use the AreaCode column.

For a complete list of codes, see tGeography Table.

VarChar15  
 CRESTAName

Name of the CRESTA.

This name corresponds to the CRESTACode field.

VarChar255  
 AreaCode

Area code for the location, when applicable. If an Area Code is not applicable for this country, use the CRESTACode column.

For a complete list of codes, see tGeography Table.

VarChar15  
 AreaName

Name of the area.

This name corresponds to the AreaCode field.

VarChar255  
 SubareaCode

Subarea code for the location.

For a complete list of codes, see tGeography Table.

VarChar15  
 SubareaName

Name of the subarea.

This name corresponds to the SubareaCode field.

VarChar255  
 PostalCode

Postal code for the location.

For a complete list of codes, see tGeography Table.

VarChar15  
 PostalName

Name for the postal code.

When applicable, this name corresponds to the PostalCode field.

VarChar255  
 Subarea2Code

Area level code at a finer resolution than postal.  This is populated when a country has more than four codes. This field is used by three countries:

  • Mexico: the PostalCode, which refers to the LocalityCode
  • New Zealand: contains CrestaPostalAreaUnit
  • Japan: contains Sonpo Codes, which are remapped to a Prefecture, KU, or Yubin

For a complete list of codes, see tGeography Table.

VarChar15  
 Subarea2Name

Name for the Subarea2 code.

This name corresponds to the Subarea2Code field.

VarChar255  
 AddressStreet address for the location.VarChar255  
 CityCity in which the location resides.VarChar255  
 Zip9Nine-digit ZIP Code (ZIP+4 Code) for the location.VarChar20  
 LatitudeLatitude coordinate for the city.  Values will be positive for locations north of the equator and negative south of the equator.Numeric9 (9,6)  
 LongitudeLongitude coordinate for the city.  Values will be negative for locations west of the prime meridian up to the international date line. They will be positive for locations east of the prime meridian.Numeric9 (9,6)  
 ValidatorMatchCodeCode for the level at which the address validation occurred.VarChar10  
 EnhancedGeoMatchLevelCode

Code for the level at which the enhanced geocode match occurred.

For a complete list of codes, see tEnhancedGeoMatchLevel Table.

To best interpret the EnhancedGeoMatchLevelCode in regards to your geocoding results, refer to the document Using Geocoding in Touchstone, which is available on the AIR Client Portal. This document provides details on how to use the EnhancedGeoMatchlevelCode together with the GeoMatchLevelCode to understand the quality of your geocoding results.

VarChar10  
 GeoMatchLevelCode

Code for the level at which geocode match occurred.

For a complete list of codes, see tGeoMatchLevel Table.

To best interpret the GeoMatchLevelCode in regards to your geocoding results, refer to the document Using Geocoding in Touchstone, which is available on the AIR Client Portal. This document provides details on how to use the GeoMatchLevelCode together with the EnhancedGeoMatchlevelCode to understand the quality of your geocoding results.

VarChar10  
 GeocoderCode

Code for the utility used to geocode the location.

For a complete list of codes, see tGeocoder Table.

VarChar10  
 InceptionDateInception date of the contract.  Also referred to as the Effective FROM Date.VarWChar10   
 ExpirationDateContract expiration date. Also referred to as the Effective TO Date.VarWChar10   
 AIROccupancyCode

AIR-defined occupancy code.

For a complete list of codes, see tAIROccupancy Table.

SmallInt2  
 AIRConstructionCodeA

AIR-defined construction code for the building (Coverage A).

For a complete list of codes, see tAIRConstruction Table.

SmallInt2  
 AIRConstructionCodeB

AIR-defined construction code for the other structures (Coverage B).

For a complete list of codes, see tAIRConstruction Table.

SmallInt2  
 UserOccupancySchemeCode

Provider of the occupancy scheme codes (i.e. AIR, ISO).

For a complete list of codes, see tUserOccupancyScheme Table.

VarChar10  
 UserOccupancyCode

User-defined occupancy code.

For a complete list of codes, see tUserOccupancy Table.

VarChar10  
 UserConstructionSchemeCode

Provider of the construction scheme codes (i.e. AIR, RMS).

For a complete list of codes, see tUserConstructionScheme Table.

VarChar10  
 UserConstructionCodeA

User-defined construction code for the building (Coverage A).

For a complete list of codes, see tUserConstruction Table.

VarChar10  
 UserConstructionCodeB

User-defined construction code for other structures (Coverage B).

For a complete list of codes, see tUserConstruction Table.

VarChar10  
 RiskCountNumber of risks for the location record.Integer4  
 YearBuiltYear the structure was built.SmallInt2  
 StoriesNumber of stories in the structure.SmallInt2  
 BuildingHeightTotal height of the structure.Integer4  
 BuildingHeightUnitCode

Code for the unit of measure used to express the building's height.

For a complete list of codes, see tUnit Table.

VarChar10  
 GrossAreaTotal area of the structure.Double9  
 GrossAreaUnitCode

Code for the unit of measure used to express the building's gross area.

For a complete list of codes, see tUnit Table.

VarChar10  
 GeoPointSQL spatial attribute for the latitude and longitude coordinates.LongVarBinary16  
 EnteredDateDate and time when the contract was created.DBTimeStamp4 
(getutcdate())
 
 EditedDateDate and time when the contract was last modified.DBTimeStamp4 
(getutcdate())
 
 AuditStatusCode

Code that represents the status of exposure data import.

For a complete list of codes, see tAuditStatus Table.

Integer4  
 StatusCode

System-provided status code for the record in the table.

For a complete list of codes, see tStatus Table.

VarChar10 
('A')
 
 UserDefined1User-defined field 1.VarChar60  
 UserDefined2User-defined field 2.VarChar60  
 UserDefined3User-defined field 3.VarChar60  
 UserDefined4User-defined field 4.VarChar60  
 UserDefined5User-defined field 5.VarChar60  
 RowVersionUsed for version stamping.Binary8   
 NonCATGroundUpLossGround-Up loss value prior to the application of Non-CAT policy terms.Double9  
Objects that depend on tLocation
 Database ObjectObject TypeDescriptionDep Level
tEngineLocPhysicalProperty tabletEngineLocPhysicalPropertyTableStore information about the location's physical properties that were calculated by the engine.1
tLayerConditionLocationXref tabletLayerConditionLocationXrefTable

System reference table used to map the location to the layer condition.

1
tLocFeature tabletLocFeatureTable

Contains detailed building information, including secondary risk characteristics.

1
tLocOffshore tabletLocOffshoreTable

Contains risk characteristics for each offshore location.

1
tLocParsedAddress tabletLocParsedAddressTableContains street-level address details for each location.1
tLocStepFunctionXref tabletLocStepFunctionXrefTableSystem reference table used to determine the step functions that are associated with a location.1
tLocTerm tabletLocTermTable

Contains information about location terms.

1
tLocWC tabletLocWCTable

Contains information about the workers' compensation details for the location.

1
Objects that tLocation depends on
 Database ObjectObject TypeDescriptionDep Level
tContract tabletContractTable

Contains records for primary insurance policies.

2
tExposureSet tabletExposureSetTableContains exposure set information. An exposure set is a collection of contracts, locations, and other related information.3
See Also

Related Objects

AIRExposure Database

 

 


AIR Client Confidential

© 2016. AIR Worldwide is a registered trademark. Touchstone is a registered trademark of AIR Worldwide.

Please contact AIR Worldwide with questions or comments. For database questions. For documentation comments.