Location Attributes

These attributes provide precise geographic location and location-related metadata for properties.

Coordinates

latitude

• Type: DOUBLE
• Description: Geographic latitude in decimal degrees (WGS84)
• Example: 37.7749
• Range: -90.0 to 90.0
• Required: No
• Indexed: Yes (spatial index)
• Precision: Typically 6-8 decimal places (~10cm accuracy)

longitude

• Type: DOUBLE
• Description: Geographic longitude in decimal degrees (WGS84)
• Example: -122.4194
• Range: -180.0 to 180.0
• Required: No
• Indexed: Yes (spatial index)
• Precision: Typically 6-8 decimal places (~10cm accuracy)

s2_cell_id

• Type: BIGINT
• Description: Google S2 geometry cell ID for geospatial indexing
• Example: 1234567890123456789
• Required: No
• Indexed: Yes
• Use Case: Efficient spatial queries and proximity searches

Location Details

neighborhood

• Type: VARCHAR(100)
• Description: Neighborhood or district name
• Example: "Mission District", "Ponsonby", "Bondi"
• Required: No
• Indexed: Yes
• Use Case: Neighborhood-based searches and analysis

subdivision

• Type: VARCHAR(100)
• Description: Subdivision or development name
• Example: "Sunset Hills", "Oceanview Estates"
• Required: No
• Use Case: Subdivision-specific searches

school_district

• Type: VARCHAR(100)
• Description: School district name
• Example: "San Francisco Unified School District"
• Required: No
• Use Case: School district filtering for families

time_zone

• Type: VARCHAR(50)
• Description: IANA timezone identifier
• Example: "America/Los_Angeles", "Pacific/Auckland"
• Required: No
• Use Case: Time-based queries and scheduling

Physical Characteristics

elevation_meters

• Type: INT
• Description: Elevation above sea level in meters
• Example: 52
• Required: No
• Unit: Meters
• Use Case: Flood risk assessment, views

flood_zone

• Type: VARCHAR(20)
• Description: FEMA flood zone designation (US) or equivalent
• Example: "AE", "X", "100-year"
• Required: No
• Use Case: Insurance requirements, risk assessment

Usage Examples

Find Properties Near Coordinates

SELECT * FROM properties
WHERE latitude BETWEEN 37.7 AND 37.8
AND longitude BETWEEN -122.5 AND -122.4
AND status = 'active';

Calculate Distance (Haversine formula)

SELECT
  id,
  latitude,
  longitude,
  6371 * acos(
    cos(radians(37.7749)) *
    cos(radians(latitude)) *
    cos(radians(longitude) - radians(-122.4194)) +
    sin(radians(37.7749)) *
    sin(radians(latitude))
  ) AS distance_km
FROM properties
WHERE latitude IS NOT NULL
AND longitude IS NOT NULL
ORDER BY distance_km
LIMIT 10;

Filter by Neighborhood

SELECT * FROM properties
WHERE neighborhood = 'Mission District'
AND status = 'active'
ORDER BY price_current;

Group by School District

SELECT
  school_district,
  COUNT(*) as property_count,
  AVG(price_current) as avg_price
FROM properties
WHERE school_district IS NOT NULL
GROUP BY school_district
ORDER BY property_count DESC;

Find Properties in Flood Zones

SELECT * FROM properties
WHERE flood_zone IN ('AE', 'VE', 'A')
ORDER BY elevation_meters;

Search by Elevation Range

SELECT * FROM properties
WHERE elevation_meters BETWEEN 50 AND 200
ORDER BY elevation_meters;

Geospatial Queries

Using S2 Cell ID for Proximity

S2 cell IDs enable efficient proximity searches:

-- Find properties in same or adjacent S2 cells
SELECT * FROM properties
WHERE s2_cell_id IN (
  SELECT s2_cell_id FROM properties WHERE id = 'target-property-id'
)
OR s2_cell_id IN (
  -- Adjacent cells (requires S2 library functions)
);

Best Practices

1. Always geocode addresses: Use the geocoding system to populate coordinates
2. Validate coordinate ranges: Ensure latitude (-90 to 90) and longitude (-180 to 180)
3. Set s2_cell_id: Calculate S2 cell IDs for efficient spatial queries
4. Keep location data current: Update coordinates if address changes
5. Use spatial indexes: Ensure spatial indexes are created for latitude/longitude

Coordinate Systems

All coordinates use WGS84 (World Geodetic System 1984), the standard GPS coordinate system:

• Latitude: -90° (South Pole) to +90° (North Pole)
• Longitude: -180° (International Date Line West) to +180° (International Date Line East)
• Equator: Latitude 0°
• Prime Meridian: Longitude 0° (Greenwich, UK)

Related Attributes

• Address Attributes - Address components used for geocoding
• Views & Distances - Proximity metrics and scores
• The Mill API - Geocoding - How addresses are geocoded