Address Attributes

These attributes store the complete address information for a property, broken down into structured components.

Street Address

address_street

• Type: VARCHAR(200)
• Description: Street name and number
• Example: "123 Main Street", "45 High St"
• Required: No
• Indexed: Yes
• Use Case: Street-level searches and filtering

address_unit

• Type: VARCHAR(50)
• Description: Unit, apartment, or suite number
• Example: "Apt 4B", "Unit 12", "Suite 200"
• Required: No
• Use Case: Multi-unit buildings

Geographic Components

address_city

• Type: VARCHAR(100)
• Description: City or town name
• Example: "San Francisco", "Auckland", "Sydney"
• Required: No
• Indexed: Yes
• Use Case: City-based filtering and aggregation

address_state

• Type: VARCHAR(50)
• Description: State, province, or region
• Example: "California", "NSW", "Auckland"
• Required: No
• Indexed: Yes
• Use Case: Regional filtering

address_county

• Type: VARCHAR(100)
• Description: County or administrative district
• Example: "San Francisco County", "Cook County"
• Required: No
• Use Case: County-level analysis

address_country

• Type: VARCHAR(50) NOT NULL
• Description: Country name
• Example: "United States", "New Zealand", "Australia"
• Required: Yes (database constraint)
• Indexed: Yes
• Use Case: Multi-country filtering and statistics

address_postal_code

• Type: VARCHAR(20)
• Description: ZIP code, postal code, or postcode
• Example: "94102", "1010", "2000"
• Required: No
• Indexed: Yes
• Use Case: Postal code searches and regional analysis

Complete Address

address_full

• Type: VARCHAR(500)
• Description: Complete formatted address string
• Example: "123 Main Street, San Francisco, CA 94102, United States"
• Required: No
• Use Case: Display and geocoding input

Usage Examples

Search by City

SELECT * FROM properties
WHERE address_city = 'San Francisco'
AND status = 'active';

Filter by Postal Code

SELECT * FROM properties
WHERE address_postal_code LIKE '941%'
ORDER BY price_current;

Group by Country

SELECT
  address_country,
  COUNT(*) as property_count,
  AVG(price_current) as avg_price
FROM properties
GROUP BY address_country
ORDER BY property_count DESC;

Find Properties in State

SELECT * FROM properties
WHERE address_state = 'California'
AND address_city IN ('San Francisco', 'Oakland', 'Berkeley');

Search by Street Name

SELECT * FROM properties
WHERE address_street LIKE '%Main Street%'
OR address_street LIKE '%Main St%';

Build Full Address

SELECT
  id,
  CONCAT_WS(', ',
    address_street,
    address_city,
    address_state,
    address_postal_code,
    address_country
  ) AS formatted_address
FROM properties
WHERE address_street IS NOT NULL;

Address Normalization

Addresses are normalized during ingestion:

1. Geocoding: Addresses are parsed and geocoded using the geocoding system
2. Component Extraction: Structured components are extracted from free-form addresses
3. Standardization: Addresses are standardized to consistent formats
4. Deduplication: address_hash is computed from normalized address

Best Practices

1. Provide complete addresses: Include city, state, and country for best geocoding results
2. Use structured components: Prefer individual fields over address_full for queries
3. Normalize formats: Use consistent abbreviations (St vs Street)
4. Handle units separately: Store unit numbers in address_unit, not in address_street
5. Keep address_full updated: Maintain formatted address for display

Related Attributes

• Location Attributes - Geographic coordinates and location metadata
• Identification Attributes - Address hash for deduplication
• The Mill API - Geocoding - How addresses are parsed and geocoded