Global Address Data:Database Layouts: Difference between revisions

From Melissa Data Wiki
Jump to navigation Jump to search
Created page with "{{MelissaAddressTableNav}} {{CustomTOC}} ==Master US Addresses== {|class="alternate01 sortable" cellspacing="0" !Field Name!!Data Type!!Data Len!!% |- |MAK||bigint|| ||99.92..."
 
No edit summary
 
(9 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{MelissaAddressTableNav}}
{{GlobalAddressDataNav}}


{{CustomTOC}}
{{CustomTOC}}


==Master US Addresses==
==US Address Field Names==
{|class="alternate01 sortable" cellspacing="0"
{|class="alternate01 sortable" cellspacing="0"
!Field Name!!Data Type!!Data Len!!%
!Column Name!!Data Type!!Data Len!!Definition
|-
|-
|MAK||bigint|| ||99.92%
|MAK||bigint||8||10-digit random key assigned to each address.
|-
|-
|Active||bit|| ||99.18%
|Active||bigint||8||Specifies if the address is active. True (1) or False (0).
|-
|-
|Address||varchar||50||99.92%
|Address||varchar||50||Street Address
|-
|-
|Suite||varchar||20||26.29%
|Suite||varchar||20||Suite Number
|-
|-
|Urbanization||varchar||28||0.30%
|Urbanization||varchar||28||Urbanization Indicator
|-
|-
|City||varchar||35||99.92%
|City||varchar||35||City
|-
|-
|State||char||2||99.92%
|State||char||2||Two-character State Abbreviation
|-
|-
|AddressKey||char||11||99.92%
|AddressKey||char||11||Address Key is made of Zipcode/Plus4/DPV
|-
|-
|ParsedRange||varchar||10||99.90%
|ParsedRange||varchar||10||House Number. For Example: '6450'
|-
|-
|ParsedPreDir||varchar||5||20.32%
|ParsedPreDir||varchar||5||Direction. For Example: 'E'
|-
|-
|ParsedName||varchar||40||99.92%
|ParsedName||varchar||40||Street Name. For Example: 'Side'
|-
|-
|ParsedSuffix||varchar||10||80.81%
|ParsedSuffix||varchar||10||For Example: 'Dr'
|-
|-
|ParsedPostDir||varchar||5||5.46%
|ParsedPostDir||varchar||5||For Example: 'NE'
|-
|-
|ParsedSuiteName||varchar||10||26.18%
|ParsedSuiteName||varchar||10||For Example: 'Apt'
|-
|-
|ParsedSuiteRange||varchar||10||26.29%
|ParsedSuiteRange||varchar||10||For Example: 'Q'
|-
|-
|ParsedGarbage||varchar||30||0.00%
|BaseMAK|| || ||the ID of the record’s parent for suites, apartments and PO Boxes
|-
|-
|BaseMAK||bigint|| ||37.53%
|AddressType||char||1||Address Type Indicator
*A - Alias
*F - Firm or Company address
*G - General Delivery address
*H - High Rise or Business complex
*P - PO Box address
*R - Rural Route address
*S - Street or Residential address
*U or Z - Unique
|-
|-
|MergeMAK||bigint|| ||0.12%
|RBDI||char||1||Business/Residential indication
|-
|-
|POBoxMAK||bigint|| ||0.00%
|Latitude||decimal||9||To the level of rooftop or interpolated rooftop for most address
|-
|-
|AddressType||char||1||98.79%
|Longitude||decimal||9||To the level of rooftop or interpolated rooftop for most address
|-
|-
|RBDI||char||1||99.92%
|CensusKey||varchar||16||Census Key statefp/Countyfp/tractce/blockce/suffixlce
|-
|-
|FIPSAPN||varchar||45||0.00%
|DateAdded||datetime||8||Updated Date
|-
|-
|CensusKey||varchar||16||90.41%
|GeoLevel||varchar||1||Geo Level
*B - Record was coded to rooftop level.
*A - Record was coded to interpolated rooftop level.*
*9 - Record was coded to the ZIP + 4 centroid.
*7 - Record was coded to the ZIP + 2 centroid.
*5 - Record was coded to the 5-digit ZIP Code centroid.
*X - Record was not coded
|}
 
 
==Global Address Field Names==
Available countries: Canada, France, Germany, Italy, Spain, Portugal, and Slovenia
 
{|class="alternate01 sortable" cellspacing="0"
!Column Name!!Data Type!!Data Len!!Definition
|-
|Mak||bigint||8||Unique Address Record ID
|-
|BaseMak||bigint||8||This will give you the unique MAK number for the building
|-
|BaseRecord||bit||1||Records with Secondary addresses attached to them.
|-
|Active||bit||1||Deliverable Addresses
|-
|ISO2||nvarchar||2||'''03166_1_Alpha2'''. ISO 3166 2-character country code.
|-
|AddressLine1||nvarchar||240||Address lines should include, at minimum, the delivery address. It can also contain the full address if you do not have the area information (locality, administrative area, postal code) parsed into their individual fields.
|-
|SubPremises||nvarchar||240||'''Suite'''. Alphanumeric code identifying an individual location.
|-
|DoubleDependentLocality||nvarchar||160||Smallest population center data element.
|-
|DependentLocality||nvarchar||200||'''Urbanization'''. Smaller population center data element
|-
|Locality||nvarchar||200||'''City'''. Most common population center data element.
|-
|SubAdministrativeArea||nvarchar||160||'''County'''. Smallest geographic data element.
|-
|AdministrativeArea||nvarchar||160||'''State'''. Most common geographic data element.
|-
|PostalCode||nvarchar||20||'''Zip (Plus 4)'''. Complete postal code for a particular delivery point.
|-
|AddressType||nvarchar||1||
|-
|AddressIndicator||varchar||1||One-character code for the type of address coded.
|-
|SubNationalArea||nvarchar||200||Arbitrary administrative region below that of the sovereign state.
|-
|Thoroughfare||nvarchar||200||'''Street'''. Most common street or block data element.
|-
|ThoroughfarePreDirection||nvarchar||80||'''Street PreDirection'''
|-
|ThoroughfareLeadingType||nvarchar||80||Leading thoroughfare type indicator within the Thoroughfare field.
|-
|ThoroughfareName||nvarchar||200||'''Street Name'''. Name indicator within the Thoroughfare field.
|-
|ThoroughfareTrailingType||nvarchar||80||'''Street Suffix'''. Trailing thoroughfare type indicator within the Thoroughfare
|-
|ThoroughfarePostDirection||nvarchar||80||'''Street Post Direction'''
|-
|DependentThoroughfare||nvarchar||200||'''Dependent street''' or block data element.
|-
|DependentThoroughfarePreDirection||nvarchar||80||Prefix directional contained within the Dependent Thoroughfare field.
|-
|DependentThoroughfareLeadingType||nvarchar||80||Leading thoroughfare type indicator within the Dependent Thoroughfare
|-
|DependentThoroughfareName||nvarchar||200||Name indicator within the Dependent Thoroughfare field.
|-
|DependentThoroughfareTrailingType||nvarchar||80||Trailing thoroughfare type indicator within the Dependent Thoroughfare
|-
|DependentThoroughfarePostDirection||nvarchar||80||Postfix directional contained within the Dependent Thoroughfare field.
|-
|Building||nvarchar||200||Descriptive name identifying an individual location.
|-
|PremisesType||nvarchar||1||Leading premise type indicator within premises field.
|-
|PremisesNumber||nvarchar||20||Alphanumeric indicator within premises field.
|-
|SubPremisesType||nvarchar||15||'''Suite Name'''. Sub premises type indicator within premises field.
|-
|SubPremisesNumber||nvarchar||20||'''Suite Number'''. Sub premises number indicator within premises field.
|-
|PostBox||nvarchar||250||Post box information for a particular delivery point.
|-
|Latitude||decimal||9||Geographic coordinate for a particular delivery point.
|-
|Longitude||decimal||9||Geographic coordinate for a particular delivery point.
|-
|-
|Results||varchar||50||99.87%
|DateAdded||datetime||8|| Date data was added.
|-
|-
|DateAdded||datetime|| ||99.92%
|GeoLevel||varchar||1||  
Geo Level of geocoding
*B - Record was coded to rooftop level.
*A - Record was coded to interpolated rooftop level.*
*9 - Record was coded to the ZIP + 4 centroid.
*7 - Record was coded to the ZIP + 2 centroid.
*5 - Record was coded to the 5-digit ZIP Code centroid.
*X - Record was not coded
|-
|-
|GeoLevel||varchar||1||90.41%
|GeoUpdateDate||datetime||8||  
|-
|-
|GeoUpdateDate||datetime|| ||90.43%
|CensusKey||varchar||16||  
|-
|-
|BaseRecord||bit|| ||2.45%
|RBDI||varchar||1||Residential/Business Indicator
|}
|}




[[Category:Melissa Address Table]]
[[Category:Global Address Data]]

Latest revision as of 22:50, 20 October 2023

← Global Address Data

Global Address Data Navigation
Introduction
Database Layouts
Available Countries



US Address Field Names

Column Name Data Type Data Len Definition
MAK bigint 8 10-digit random key assigned to each address.
Active bigint 8 Specifies if the address is active. True (1) or False (0).
Address varchar 50 Street Address
Suite varchar 20 Suite Number
Urbanization varchar 28 Urbanization Indicator
City varchar 35 City
State char 2 Two-character State Abbreviation
AddressKey char 11 Address Key is made of Zipcode/Plus4/DPV
ParsedRange varchar 10 House Number. For Example: '6450'
ParsedPreDir varchar 5 Direction. For Example: 'E'
ParsedName varchar 40 Street Name. For Example: 'Side'
ParsedSuffix varchar 10 For Example: 'Dr'
ParsedPostDir varchar 5 For Example: 'NE'
ParsedSuiteName varchar 10 For Example: 'Apt'
ParsedSuiteRange varchar 10 For Example: 'Q'
BaseMAK the ID of the record’s parent for suites, apartments and PO Boxes
AddressType char 1 Address Type Indicator
  • A - Alias
  • F - Firm or Company address
  • G - General Delivery address
  • H - High Rise or Business complex
  • P - PO Box address
  • R - Rural Route address
  • S - Street or Residential address
  • U or Z - Unique
RBDI char 1 Business/Residential indication
Latitude decimal 9 To the level of rooftop or interpolated rooftop for most address
Longitude decimal 9 To the level of rooftop or interpolated rooftop for most address
CensusKey varchar 16 Census Key statefp/Countyfp/tractce/blockce/suffixlce
DateAdded datetime 8 Updated Date
GeoLevel varchar 1 Geo Level
  • B - Record was coded to rooftop level.
  • A - Record was coded to interpolated rooftop level.*
  • 9 - Record was coded to the ZIP + 4 centroid.
  • 7 - Record was coded to the ZIP + 2 centroid.
  • 5 - Record was coded to the 5-digit ZIP Code centroid.
  • X - Record was not coded


Global Address Field Names

Available countries: Canada, France, Germany, Italy, Spain, Portugal, and Slovenia

Column Name Data Type Data Len Definition
Mak bigint 8 Unique Address Record ID
BaseMak bigint 8 This will give you the unique MAK number for the building
BaseRecord bit 1 Records with Secondary addresses attached to them.
Active bit 1 Deliverable Addresses
ISO2 nvarchar 2 03166_1_Alpha2. ISO 3166 2-character country code.
AddressLine1 nvarchar 240 Address lines should include, at minimum, the delivery address. It can also contain the full address if you do not have the area information (locality, administrative area, postal code) parsed into their individual fields.
SubPremises nvarchar 240 Suite. Alphanumeric code identifying an individual location.
DoubleDependentLocality nvarchar 160 Smallest population center data element.
DependentLocality nvarchar 200 Urbanization. Smaller population center data element
Locality nvarchar 200 City. Most common population center data element.
SubAdministrativeArea nvarchar 160 County. Smallest geographic data element.
AdministrativeArea nvarchar 160 State. Most common geographic data element.
PostalCode nvarchar 20 Zip (Plus 4). Complete postal code for a particular delivery point.
AddressType nvarchar 1
AddressIndicator varchar 1 One-character code for the type of address coded.
SubNationalArea nvarchar 200 Arbitrary administrative region below that of the sovereign state.
Thoroughfare nvarchar 200 Street. Most common street or block data element.
ThoroughfarePreDirection nvarchar 80 Street PreDirection
ThoroughfareLeadingType nvarchar 80 Leading thoroughfare type indicator within the Thoroughfare field.
ThoroughfareName nvarchar 200 Street Name. Name indicator within the Thoroughfare field.
ThoroughfareTrailingType nvarchar 80 Street Suffix. Trailing thoroughfare type indicator within the Thoroughfare
ThoroughfarePostDirection nvarchar 80 Street Post Direction
DependentThoroughfare nvarchar 200 Dependent street or block data element.
DependentThoroughfarePreDirection nvarchar 80 Prefix directional contained within the Dependent Thoroughfare field.
DependentThoroughfareLeadingType nvarchar 80 Leading thoroughfare type indicator within the Dependent Thoroughfare
DependentThoroughfareName nvarchar 200 Name indicator within the Dependent Thoroughfare field.
DependentThoroughfareTrailingType nvarchar 80 Trailing thoroughfare type indicator within the Dependent Thoroughfare
DependentThoroughfarePostDirection nvarchar 80 Postfix directional contained within the Dependent Thoroughfare field.
Building nvarchar 200 Descriptive name identifying an individual location.
PremisesType nvarchar 1 Leading premise type indicator within premises field.
PremisesNumber nvarchar 20 Alphanumeric indicator within premises field.
SubPremisesType nvarchar 15 Suite Name. Sub premises type indicator within premises field.
SubPremisesNumber nvarchar 20 Suite Number. Sub premises number indicator within premises field.
PostBox nvarchar 250 Post box information for a particular delivery point.
Latitude decimal 9 Geographic coordinate for a particular delivery point.
Longitude decimal 9 Geographic coordinate for a particular delivery point.
DateAdded datetime 8 Date data was added.
GeoLevel varchar 1

Geo Level of geocoding

  • B - Record was coded to rooftop level.
  • A - Record was coded to interpolated rooftop level.*
  • 9 - Record was coded to the ZIP + 4 centroid.
  • 7 - Record was coded to the ZIP + 2 centroid.
  • 5 - Record was coded to the 5-digit ZIP Code centroid.
  • X - Record was not coded
GeoUpdateDate datetime 8
CensusKey varchar 16
RBDI varchar 1 Residential/Business Indicator