OpenAddresses

Overview

OpenAddresses is an open source project that compiles free and open global address data, aggregating crowdsourced and publicly available datasets to create a comprehensive, unified resource.

Key Attributes

Geographic Coverage	United States
Entity Level	Address, Point of Interest
Release Frequency	Weekly, Sunday ~3:30pm ET

Notes

Each address record includes latitude and longitude coordinates for geolocation. These coordinates can be combined with the geospatial boundaries included in the geography_characteristics table. Geospatial boundaries are provided as GeoJSON and WKT polygons and are represented as coordinates. These geospatial boundaries are also referred to as ”shapefiles”, “geographic boundaries,” “bounding coordinates,” and “geographic area coordinates.”

Each POI includes the name, location, category or type of place or business, and a POI_ID unique identifier. POIs can be mapped back to addresses using the relationships table.

Address Normalization: In the us_addresses table, Cybersyn normalizes the street names, city, state, and zip codes for each address line in the dataset using our geography_index table to create consistency across city names (e.g., “Saint Paul, MN” vs. “St. Paul, MN”) and to verify accuracy. Street abbreviations are also standardized in the data (e.g., “Rd” -> “Road”).

Zip codes are determined using the address coordinates in combination with geospatial data from the US Census Bureau and are validated using data from the US Postal Service (USPS).

Point of Interest to Address Mapping: Note that more than one point of interest can map to a single address. For example, a fast food restaurant might share a location with a gas station or numerous doctors might have their own practices at a single address.

The addresses for a small fraction of locations are incorrectly parsed. In particular, addresses of non-standard format such street intersections may be parsed incorrectly. The STATE, CITY, ZIP, and coordinates for these addresses are generally correct, but the STREET, NUMBER and UNIT may contain errors in these cases.

Cybersyn Products

Tables above are available in the following Cybersyn data products:

Sample Queries

Find addresses within a zip code to send direct mail campaign to

Query US addresses by zip code to find relevant addresses in your target area

SELECT NUMBER, STREET, STREET_TYPE, CITY, STATE, ZIP
FROM CYBERSYN.US_ADDRESS
WHERE ZIP = '02114'
LIMIT 5;

Reverse geocoding

Query addresses near longitude and latitude coordinates to find nearby addresses

SELECT LONGITUDE, LATITUDE, NUMBER, STREET, STREET_TYPE, CITY, STATE, ZIP
FROM CYBERSYN.US_ADDRESS
WHERE LONGITUDE BETWEEN  -74.5 AND -74
    AND LATITUDE BETWEEN 40.0 AND 40.5
LIMIT 5;

Use geographic boundaries to filter addresses

Query addresses in the largest zip code within a US state (e.g., Florida) by total tabulation area

WITH zip_areas AS (
    SELECT
        geo.geo_id,
        geo.geo_name AS zip,
        states.related_geo_name AS state,
        countries.related_geo_name AS country,
        ST_AREA(TRY_TO_GEOGRAPHY(value)) AS area
    FROM cybersyn.geography_index AS geo
    JOIN cybersyn.geography_relationships AS states
        ON (geo.geo_id = states.geo_id AND states.related_level = 'State')
    JOIN cybersyn.geography_relationships AS countries
        ON (geo.geo_id = countries.geo_id AND countries.related_level = 'Country')
    JOIN cybersyn.geography_characteristics AS chars
        ON (geo.geo_id = chars.geo_id AND chars.relationship_type = 'coordinates_geojson')
    WHERE geo.level = 'CensusZipCodeTabulationArea'
),

zip_area_ranks AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY country, state ORDER BY area DESC, geo_id) AS zip_area_rank
    FROM zip_areas
)

SELECT addr.number, addr.street, addr.street_type, addr.city, addr.state, addr.zip, areas.country
FROM cybersyn.us_addresses AS addr
JOIN zip_area_ranks AS areas
    ON (addr.id_zip = areas.geo_id)
WHERE addr.state = 'FL' AND areas.country = 'United States' AND areas.zip_area_rank = 1
LIMIT 10;

Disclaimers

Cybersyn is not endorsed by or affiliated with any of these providers. Contact snowflake-public-data@snowflake.com for questions.

Overview​

Key Attributes​

Notes​

Cybersyn Products​

Sample Queries​

Find addresses within a zip code to send direct mail campaign to​

Reverse geocoding​

Use geographic boundaries to filter addresses​

Disclaimers​