NAV Navbar
python r shell vb

Introduction

Welcome to the StratoDem Analytics API! You can use the API to query economic and geo-demographic data from the StratoDem Analytics database.

We have language bindings in Python, R and VBA (for Excel). You can view code examples in the dark area to the right, and you can switch the programming language of the examples with the tabs in the top right.

Installation

$ pip install strato-query
library(devtools)
devtools::install_github('StratoDem/strato-query')
' Contact the StratoDem Analytics team for an Excel template including necessary query structures
# Just use curl!

For Python or R, install the package from PyPI or GitHub.

For VBA support, contact the StratoDem team for an Excel template.

Authentication

To authorize, use this code:

from strato_query import authenticate_to_api

# Option 1: Pass in the API token as a string
authenticate_to_api('my-api-token')

# Option 2: Pass in the API token via an environment variable at STRATODEM_API_TOKEN
# This would be, e.g., $ STRATODEM_API_TOKEN=my-api-token python my_api_script.py
authenticate_to_api()

# API calls from this point on will now query the API with API_TOKEN
library(stRatoquery)

# This requires an apiToken argument for each submitted API query
df = submit_api_query(query = myQueryParams, apiToken = 'my-api-token')
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"query\": { \"query_type\": \"MEDIAN\", \"data_fields\": [ \"year\", { \"median_value\": \"median_income\" } ], \"table\": \"incomeforecast_us_annual_income_group_age\", \"groupby\": [ \"year\" ], \"data_filters\": [ { \"filter_type\": \"ge\", \"filter_value\": 17, \"filter_variable\": \"age_g\" }, { \"filter_type\": \"between\", \"filter_value\": [ 2010, 2013 ], \"filter_variable\": \"year\" } ], \"aggregations\": [], \"order\": [ \"year\" ], \"median_variable_name\": \"income_g\"}}"
' Pass a valid API token to the query
' If you are using the provided template, you can place the API token in the Configuration!B5 cell

StratoDem Analytics uses API keys to allow access to the API. You can register a new StratoDem Analytics API key from your account page.

How do I create a new API token or find an existing token? →

Submitting your first query

Finds median household income in the US for those 80+ from 2010 to 2013

from strato_query import SDAPIQuery, APIMedianQueryParams
from strato_query.filters import GreaterThanOrEqualToFilter, BetweenFilter

df = SDAPIQuery.query_api_df(
    query_params=APIMedianQueryParams(
        query_type='MEDIAN',
        table='incomeforecast_us_annual_income_group_age',
        data_fields=('year', {'median_value': 'median_income'}),
        median_variable_name='income_g',
        data_filters=(
            GreaterThanOrEqualToFilter(var='age_g', val=17),
            BetweenFilter(var='year', val=[2010, 2013]),
        ),
        groupby=('year',),
        order=('year',),
        aggregations=(),
    )
)
library(stRatoquery)
apiToken = 'my-api-token'

df = submit_api_query(
  query = median_query_params(
    table = 'incomeforecast_us_annual_income_group_age',
    data_fields = api_fields(fields_list = list('year', list(median_value = 'median_hhi'))),
    data_filters = list(
        ge_filter(filter_variable = 'age_g', filter_value = 17),
        between_filter(filter_variable = 'year', filter_value = c(2010, 2013))
    ),
    groupby=c('year'),
    median_variable_name='income_g',
    aggregations=list()
  ),
  apiToken = apiToken)
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"query\": { \"query_type\": \"MEDIAN\", \"data_fields\": [ \"year\", { \"median_value\": \"median_income\" } ], \"table\": \"incomeforecast_us_annual_income_group_age\", \"groupby\": [ \"year\" ], \"data_filters\": [ { \"filter_type\": \"ge\", \"filter_value\": 17, \"filter_variable\": \"age_g\" }, { \"filter_type\": \"between\", \"filter_value\": [ 2010, 2013 ], \"filter_variable\": \"year\" } ], \"aggregations\": [], \"order\": [ \"year\" ], \"median_variable_name\": \"income_g\"}}"

A basic query to the StratoDem Analytics API submits query parameters with authentication via the API token.

Query types

Simple queries

This query returns households by age, income group, and year for all census tracts in Massachusetts starting in 2010

from strato_query import SDAPIQuery, APIQueryParams
from strato_query.filters import EqualToFilter, GreaterThanOrEqualToFilter

SDAPIQuery.query_api_df(
    APIQueryParams(
        data_fields=('year', 'geoid11', 'age_g', 'income_g', 'households'),
        table='incomeforecast_tract_annual_income_group_age',
        data_filters=[
            EqualToFilter('geoid2', 25),
            GreaterThanOrEqualToFilter('year', 2010),
        ],
        aggregations=(),
        groupby=()))
library(stRatoquery)

df = submit_api_query(
  query = api_query_params(
    table = 'incomeforecast_tract_annual_income_group_age',
    data_fields = api_fields(fields_list = list('year', 'geoid11', 'age_g', 'income_g', 'households')),
    data_filters = list(
        eq_filter(filter_variable = 'geoid2', filter_value = 25),
        ge_filter(filter_variable = 'year', filter_value = 2010)
    ),
    aggregations=list(),
    groupby=list()
  ),
  apiToken = apiToken)
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"query\": { \"query_type\": \"COUNT\", \"data_fields\": [ \"year\", \"geoid11\", \"age_g\", \"income_g\", \"households\" ], \"table\": \"incomeforecast_tract_annual_income_group_age\", \"groupby\": [ ], \"data_filters\": [ { \"filter_type\": \"eq\", \"filter_value\": 25, \"filter_variable\": \"geoid2\" }, { \"filter_type\": \"ge\", \"filter_value\": 2010, \"filter_variable\": \"year\" } ], \"aggregations\": [], \"order\": []}}"
Dim query As Dictionary

Set query = apiQueryParameters( _
    table:="incomeforecast_tract_annual_income_group_age", _
    dataFields:=Array("year", "geoid11", "age_g", "income_g", "households"), _
    dataFilters:=Array( _
        equalToFilter(filterVariable:="geoid2", filterValue:=25), _
        greaterThanOrEqualToFilter(filterVariable:="year", filterValue:=2010)), _
    groupby:=Array(), _
    aggregations:=Array())

' Write the results to "Example Sheet" (note that this sheet will need to exist first)
Call writeToSheet(results:=submitAPIQuery(query), sheetName:="Example Sheet")

Basic API queries evaluate like simple SELECT statements from the StratoDem Analytics database.

Queries roughly translate into SQL as SELECT data_fields FROM table WHERE data_filters GROUP BY groupby;

Queries with aggregations

This query returns the number of households age 75+ with at least $50,000 in household income by county and year for each county in California

from strato_query import SDAPIQuery, APIQueryParams
from strato_query.aggregations import SumAggregation
from strato_query.filters import EqualToFilter, GreaterThanOrEqualToFilter

SDAPIQuery.query_api_df(
    APIQueryParams(
        # Passing in a dictionary to data_fields allows for renaming variables in the resulting DataFrame or JSON
        data_fields=['year', 'geoid5', {'households': 'households_age_75plus_hhi_50k_plus'}],
        table='incomeforecast_county_annual_income_group_age',
        data_filters=(
            EqualToFilter('geoid2', 6),
            # Cutoff for age 75+
            GreaterThanOrEqualToFilter('age_g', 16),
            # Cutoff for $50,000+ HHI
            GreaterThanOrEqualToFilter('income_g', 10),
        ),
        groupby=('year', 'geoid5'),
        aggregations=[SumAggregation('households')],
        order=('year', 'geoid5')
    ))
library(stRatoquery)

df = submit_api_query(
  query = api_query_params(
    table = 'incomeforecast_county_annual_income_group_age',
    # Passing in a mapping to data_fields allows for renaming variables in the resulting data.frame or JSON
    data_fields = api_fields(fields_list = list('year', 'geoid5', list(households = 'households_age_75plus_50k_plus'))),
    data_filters = list(
        eq_filter(filter_variable = 'geoid2', filter_value = 6),
        # Cutoff for age 75+
        ge_filter(filter_variable = 'age_g', filter_value = 16),
        # Cutoff for $50,000+ HHI
        ge_filter(filter_variable = 'income_g', filter_value = 10)
    ),
    aggregations = list(sum_aggregation(variable_name = 'households')),
    groupby = list('year', 'geoid5'),
    order = list('year', 'geoid5')
  ),
  apiToken = apiToken)
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"query\": {\"query_type\": \"COUNT\", \"data_fields\": [\"year\", \"geoid5\", {\"households\": \"households_age_75plus_hhi_50k_plus\"}], \"table\": \"incomeforecast_county_annual_income_group_age\", \"groupby\": [\"year\", \"geoid5\"], \"data_filters\": [{\"filter_type\": \"eq\", \"filter_value\": 6, \"filter_variable\": \"geoid2\"}, {\"filter_type\": \"ge\", \"filter_value\": 16, \"filter_variable\": \"age_g\"}, {\"filter_type\": \"ge\", \"filter_value\": 10, \"filter_variable\": \"income_g\"}], \"aggregations\": [{\"aggregation_func\": \"sum\", \"variable_name\": \"households\"}], \"order\": [\"year\", \"geoid5\"]}}"
Dim query As Dictionary

Set query = apiQueryParameters( _
    table:="incomeforecast_county_annual_income_group_age", _
    dataFields:=Array("year", "geoid5", renameVariable(original:="households", renamed:="households_age_75plus_hhi_50k_plus")), _
    dataFilters:=Array( _
        equalToFilter(filterVariable:="geoid2", filterValue:=6), _
        greaterThanOrEqualToFilter(filterVariable:="age_g", filterValue:=16), _
        greaterThanOrEqualToFilter(filterVariable:="income_g", filterValue:=10)), _
    groupby:=Array("year", "geoid5"), _
    order:=Array("year", "geoid5"), _
    aggregations:=Array(sumAggregation(variableName:="households")))

' Write the results to "Example Sheet" (note that this sheet will need to exist first)
Call writeToSheet(results:=submitAPIQuery(query), sheetName:="Example Sheet")

In many cases, we want to aggregate data returned by the API. For example, if we have the SQL query: SELECT year, geoid5, sum(households) FROM incomeforecast_county_annual_income_group_age WHERE income_g >= 5 AND age_g >= 17 GROUP BY year, geoid5 ORDER BY year, geoid5;, we need to group by the year and geoid5 (county) columns, and then get the sum of households for each combination.

The groupby and aggregations arguments tell the API which columns to group on and which aggregations to apply.

Median queries

This query returns the median household income for all households by census tract in Suffolk County, MA in 2019

from strato_query import SDAPIQuery, APIMedianQueryParams
from strato_query.filters import EqualToFilter

SDAPIQuery.query_api_df(
    APIMedianQueryParams(
        median_variable_name='income_g',
        table='incomeforecast_tract_annual_income_group',
        data_fields=('year', 'geoid11', 'median_value'),
        data_filters=[
            EqualToFilter('year', 2019),
            EqualToFilter('geoid5', 25025),
        ],
        aggregations=(),
        groupby=('year', 'geoid11')       
    ))
library(stRatoquery)

df = submit_api_query(
  query = median_query_params(
    table = 'incomeforecast_tract_annual_income_group',
    data_fields = api_fields(fields_list = list('year', 'geoid11', 'median_value')),
    data_filters = list(
        eq_filter(filter_variable = 'year', filter_value = 2019),
        eq_filter(filter_variable = 'geoid5', filter_value = 25025)
    ),
    groupby=c('year', 'geoid11'),
    median_variable_name='income_g',
    aggregations=list()
  ),
  apiToken = apiToken)
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"query\": {\"query_type\": \"MEDIAN\", \"data_fields\": [\"year\", \"geoid11\", \"median_value\"], \"table\": \"incomeforecast_tract_annual_income_group\", \"groupby\": [\"year\", \"geoid11\"], \"data_filters\": [{\"filter_type\": \"eq\", \"filter_value\": 2019, \"filter_variable\": \"year\"}, {\"filter_type\": \"eq\", \"filter_value\": 25025, \"filter_variable\": \"geoid5\"}], \"aggregations\": [], \"median_variable_name\": \"income_g\"}}"
Dim query As Dictionary

Set query = medianQueryParameters( _
    table:="incomeforecast_tract_annual_income_group", _
    dataFields:=Array("year", "geoid11", "median_value"), _
    dataFilters:=Array(equalToFilter(filterVariable:="year", filterValue:=2019), equalToFilter(filterVariable:="geoid5", filterValue:=25025)), _
    aggregations:=Array(), _
    groupby:=Array("year", "geoid11"), _
    medianVariableName:="income_g")

' Write the results to "Example Sheet" (note that this sheet will need to exist first)
Call writeToSheet(results:=submitAPIQuery(query), sheetName:="Example Sheet")

To query median household income, net worth, home values, or other metrics, a special median query is needed.

These queries require a median_variable_name argument, which tells the API service which variable to use to compute the median value.

Mean/average queries

This query returns the median household income for all households by census tract in Suffolk County, MA in 2019

from strato_query import SDAPIQuery, APIMeanQueryParams
from strato_query.filters import EqualToFilter

SDAPIQuery.query_api_df(
    APIMeanQueryParams(
        mean_variable_name='income_g',
        table='incomeforecast_tract_annual_income_group',
        data_fields=('year', 'geoid11', 'mean_value'),
        data_filters=[
            EqualToFilter('year', 2019),
            EqualToFilter('geoid5', 25025),
        ],
        aggregations=(),
        groupby=('year', 'geoid11')       
    ))
library(stRatoquery)

df = submit_api_query(
  query = mean_query_params(
    table = 'incomeforecast_tract_annual_income_group',
    data_fields = api_fields(fields_list = list('year', 'geoid11', 'mean_value')),
    data_filters = list(
        eq_filter(filter_variable = 'year', filter_value = 2019),
        eq_filter(filter_variable = 'geoid5', filter_value = 25025)
    ),
    groupby = c('year', 'geoid11'),
    mean_variable_name = 'income_g',
    aggregations = list()
  ),
  apiToken = apiToken)
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"query\": {\"query_type\": \"MEAN\", \"data_fields\": [\"year\", \"geoid11\", \"mean_value\"], \"table\": \"incomeforecast_tract_annual_income_group\", \"groupby\": [\"year\", \"geoid11\"], \"data_filters\": [{\"filter_type\": \"eq\", \"filter_value\": 2019, \"filter_variable\": \"year\"}, {\"filter_type\": \"eq\", \"filter_value\": 25025, \"filter_variable\": \"geoid5\"}], \"aggregations\": [], \"mean_variable_name\": \"income_g\"}}"
Dim query As Dictionary

Set query = meanQueryParameters( _
    table:="incomeforecast_tract_annual_income_group", _
    dataFields:=Array("year", "geoid11", "mean_value"), _
    dataFilters:=Array(equalToFilter(filterVariable:="year", filterValue:=2019), equalToFilter(filterVariable:="geoid5", filterValue:=25025)), _
    aggregations:=Array(), _
    groupby:=Array("year", "geoid11"), _
    meanVariableName:="income_g")

' Write the results to "Example Sheet" (note that this sheet will need to exist first)
Call writeToSheet(results:=submitAPIQuery(query), sheetName:="Example Sheet")

To query mean household income, net worth, home values, or other metrics, a special mean query is needed.

These queries require a mean_variable_name argument, which tells the API service which variable to use to compute the mean value.

Combining multiple queries

This query joins two results on common columns to compute population density

from strato_query import SDAPIQuery, APIQueryParams
from strato_query.filters import EqualToFilter, LessThanFilter

df = SDAPIQuery.query_api_df(
    query_params=APIQueryParams(
        query_type='COUNT',
        table='populationforecast_metro_annual_population',
        data_fields=('year', 'cbsa', {'population': 'population'}),
        data_filters=(
            LessThanFilter(var='year', val=2015),
            EqualToFilter(var='cbsa', val=14454),
        ),
        aggregations=(dict(aggregation_func='sum', variable_name='population'),),
        groupby=('cbsa', 'year'),
        order=('year',),
        join=APIQueryParams(
            query_type='AREA',
            table='geocookbook_metro_na_shapes_full',
            data_fields=('cbsa', 'area', 'name'),
            data_filters=[EqualToFilter(var='cbsa', val=14454)],
            groupby=('cbsa', 'name'),
            aggregations=(),
            on=dict(left=('cbsa',), right=('cbsa',)),
        )
    )
)

df['POP_PER_SQ_MI'] = df['POPULATION'].div(df['AREA'])
df_final = df[['YEAR', 'NAME', 'POP_PER_SQ_MI']]
library(stRatoquery)

api_query_params(
  table = 'populationforecast_metro_annual_population',
  data_fields = api_fields(fields_list = list('year', 'cbsa', list('population' = 'population'))),
  data_filters = list(lt_filter(filter_variable = "year", filter_value = 2015), eq_filter(filter_variable = "cbsa", filter_value = 14454)),
  aggregations = list(sum_aggregation(variable_name = "population")),
  groupby = c("cbsa", "year"),
  order = c("year"),
  join = api_query_params(
      table = 'geocookbook_metro_na_shapes_full',
      data_fields = api_fields(fields_list = list('cbsa', 'area', 'name')),
      data_filters = list(eq_filter(filter_variable = "cbsa", filter_value = 14454)),
      aggregations = list(),
      groupby = c("cbsa", "name"),
      on = list(left = c('cbsa'), right = c('cbsa')),
      query_type = "AREA"),
  query_type = "COUNT")
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"query\": {\"query_type\": \"COUNT\", \"data_fields\": [\"year\", \"cbsa\", {\"population\": \"population\"}], \"table\": \"populationforecast_metro_annual_population\", \"groupby\": [\"cbsa\", \"year\"], \"data_filters\": [{\"filter_type\": \"lt\", \"filter_value\": 2015, \"filter_variable\": \"year\"}, {\"filter_type\": \"eq\", \"filter_value\": 14454, \"filter_variable\": \"cbsa\"}], \"aggregations\": [{\"aggregation_func\": \"sum\", \"variable_name\": \"population\"}], \"join\": {\"query_type\": \"AREA\", \"data_fields\": [\"cbsa\", \"area\", \"name\"], \"table\": \"geocookbook_metro_na_shapes_full\", \"groupby\": [\"cbsa\", \"name\"], \"data_filters\": [{\"filter_type\": \"eq\", \"filter_value\": 14454, \"filter_variable\": \"cbsa\"}], \"aggregations\": [], \"on\": {\"left\": [\"cbsa\"], \"right\": [\"cbsa\"]}}, \"order\": [\"year\"]}}"
apiQueryParameters( _
    table:="populationforecast_metro_annual_population", _
    dataFields:=Array("year", "cbsa", renameVariable(original:="population", renamed:="population")), _
    dataFilters:=Array(lessThanFilter(filterVariable:="year", filterValue:=2015), equalToFilter(filterVariable:="cbsa", filterValue:=14454)), _
    aggregations:=Array(sumAggregation(variableName:="population")), _
    groupby:=Array("cbsa", "year"), order:=Array("year"), _
    join:=apiQueryParameters( _
        table:="geocookbook_metro_na_shapes_full", _
        dataFields:=Array("cbsa", "area", "name"), _
        dataFilters:=Array(equalToFilter(filterVariable:="cbsa", filterValue:=14454)), _
        aggregations:=Array(), _
        groupby:=Array("cbsa", "name"), _
        joinOn:=joinOnStructure(left:=Array("cbsa"), right:=Array("cbsa")), _
        queryType:="AREA"), _
    queryType:="COUNT")

In many cases, we need to derive data from two or more variables. For example, when calculating population density, we need to take the population and divide by the area. In this case, those two metrics come from different sources in the StratoDem Analytics database, and we need to JOIN two queries using the join argument.

A join takes another APIQueryParams object to join (effectively joining the results of two SELECT statements), with that new APIQueryParams object requiring an on argument, to tell the API service which column(s) to join on.

For example, on={"left": [cbsa], "right": [cbsa]} tells the API to join the two subqueries on the cbsa column.

GeoJSON queries

This query returns a GeoJSON FeatureCollection with median household income stored in properties for all census tracts within a 15-minute drive of the lat-lng

from strato_query import SDAPIQuery, APIGeoJSONQueryParams, APIMedianQueryParams
from strato_query.filters import EqualToFilter, DrivetimeFilter

geojson = SDAPIQuery.query_api_json(
    APIGeoJSONQueryParams(
        properties=[
            'geoid11',
            'name',
            'median_hhi'
        ],
        data_fields=['geoid11', 'geometry', 'name'],
        table='geocookbook_tract_na_shapes_full',
        groupby=(),
        data_filters=(),
        aggregations=(),
        join=APIMedianQueryParams(
            on={'left': ['geoid11'], 'right': ['geoid']},
            table='incomeforecast_tract_annual_income_group',
            median_variable_name='income_g',
            data_fields=[{'geoid11': 'geoid'}, {'median_value': 'median_hhi'}],
            data_filters=[
                EqualToFilter('year', 2019),
                DrivetimeFilter(latitude=42.1, longitude=-120.1, minutes=15, detailed_type='drivetime_unweighted'),
            ],
            groupby=['geoid11'],
            aggregations=(),
        )
    ))
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"query\": {\"query_type\": \"GEOJSON\", \"data_fields\": [\"geoid11\", \"geometry\", \"name\"], \"table\": \"geocookbook_tract_na_shapes_full\", \"groupby\": [], \"data_filters\": [], \"aggregations\": [], \"join\": {\"query_type\": \"MEDIAN\", \"data_fields\": [{\"geoid11\": \"geoid\"}, {\"median_value\": \"median_hhi\"}], \"table\": \"incomeforecast_tract_annual_income_group\", \"groupby\": [\"geoid11\"], \"data_filters\": [{\"filter_type\": \"eq\", \"filter_value\": 2019, \"filter_variable\": \"year\"}, {\"filter_type\": \"drivetime_unweighted\", \"filter_value\": {\"latitude\": 42.1, \"longitude\": -120.1, \"minutes\": 15, \"traffic\": \"disabled\", \"start_time\": null}, \"filter_variable\": \"\"}], \"aggregations\": [], \"on\": {\"left\": [\"geoid11\"], \"right\": [\"geoid\"]}, \"median_variable_name\": \"income_g\"}, \"properties\": [\"geoid11\", \"name\", \"median_hhi\"]}}"

To return a GeoJSON FeatureCollection, use the APIGeoJSONQueryParams. This query structure adds the properties argument, which specifies which fields will show up in each Feature's properties field in the GeoJSON response.

The example to the right returns the GeoJSON FeatureCollection for the one census tract within a 15-minute drive of 42.1,-120.1 (lat, lng).

{"type": "FeatureCollection", "features": [{"type": "Feature", "geometry": {"type": "Polygon", "coordinates": ["DUMMY DATA HERE"]}, "properties": {"geoid11": 41037960100, "name": "Census Tract: 41037960100", "median_hhi": 42182}}]}

Geocoder queries

This query returns the census tract containing the latitude-longitude coordinates

from strato_query import SDAPIQuery, APIGeocoderQueryParams

df_tract = SDAPIQuery.query_api_df(
    APIGeocoderQueryParams(
        data_fields=('geoid11', 'name'),
        # The geometry table for census tracts
        table='geocookbook_tract_na_shapes_full',
        latitude=42.3498224,
        longitude=-71.0521391,
        groupby=(),
        data_filters=(),
        aggregations=(),
    )
)

df_metro = SDAPIQuery.query_api_df(
    APIGeocoderQueryParams(
        data_fields=('cbsa', 'name'),
        # The geometry table for metros
        table='geocookbook_metro_na_shapes_full',
        latitude=42.3498224,
        longitude=-71.0521391,
        groupby=(),
        data_filters=(),
        aggregations=(),
    )
)
# Not implemented yet
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"query\": {\"query_type\": \"GEOCODER\", \"data_fields\": [\"cbsa\", \"name\"], \"table\": \"geocookbook_metro_na_shapes_full\", \"groupby\": [], \"data_filters\": [], \"aggregations\": [], \"latitude\": 42.3498224, \"longitude\": -71.0521391}}"
' Not implemented yet

To identify which geography contains a given location, the APIGeocoderParams take a geographic shapes table, a latitude, and a longitude, and construct a query to return the geographic ID.

The first example to the right returns the census tract ID (GEOID11) for the latitude-longitude pair.

GEOID11 NAME
25025061200 Census Tract: 25025061200

The second example returns the metro ID for the latitude-longitude pair:

CBSA NAME
14454 Boston, MA

Advanced queries

This query returns GDP per capita estimates for counties

from strato_query import SDAPIQuery, APICalculationQueryParams, APIQueryParams
from strato_query.filters import EqualToFilter

df = SDAPIQuery.query_api_df(
    APICalculationQueryParams(
        data_fields=(
            'year',
            'geoid5',
            'real_gp',
            # Take the real_gp column and divide by population to get gdp_per_capita
            {'calculate:gdp_per_capita': 'real_gp / population'}),
        table='',
        aggregations=(),
        groupby=(),
        data_filters=(),
        inner_query=APIQueryParams(
            data_fields=('year', 'geoid5', 'real_gp'),
            data_filters=(EqualToFilter('month', 6),),
            table='grossproduct_county_monthly_forecasts',
            aggregations=(),
            groupby=(),
            join=APIQueryParams(
                data_fields=({'year': 'year_pop'}, {'geoid5': 'geoid5_pop'}, 'population'),
                data_filters=(),
                table='populationforecast_county_annual_population',
                aggregations=(),
                groupby=(),
                on={'left': ['year', 'geoid5'], 'right': ['year_pop', 'geoid5_pop']}
            ))))
# Not yet in R
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"query\": {\"query_type\": \"CALCULATION\", \"data_fields\": [\"year\", \"geoid5\", \"real_gp\", {\"calculate:gdp_per_capita\": \"real_gp / population\"}], \"table\": \"\", \"groupby\": [], \"data_filters\": [], \"aggregations\": [], \"inner_query\": {\"query_type\": \"COUNT\", \"data_fields\": [\"year\", \"geoid5\", \"real_gp\"], \"table\": \"grossproduct_county_monthly_forecasts\", \"groupby\": [], \"data_filters\": [{\"filter_type\": \"eq\", \"filter_value\": 6, \"filter_variable\": \"month\"}], \"aggregations\": [], \"join\": {\"query_type\": \"COUNT\", \"data_fields\": [{\"year\": \"year_pop\"}, {\"geoid5\": \"geoid5_pop\"}, \"population\"], \"table\": \"populationforecast_county_annual_population\", \"groupby\": [], \"data_filters\": [], \"aggregations\": [], \"on\": {\"left\": [\"year\", \"geoid5\"], \"right\": [\"year_pop\", \"geoid5_pop\"]}}}}}"
' Not yet in VB

To compute derivative values, a APICalculationQueryParams may be used.

These queries allow for computing values based on multiple columns. For example, GDP per capita estimates may be created as the local market output estimate divided by the population estimate.

Filters

EqualToFilter

Use this filter to restrict data to the year 2019

from strato_query.filters import EqualToFilter

EqualToFilter(
    var='year',
    val=2019)
library(stRatoquery)

eq_filter(filter_variable = 'year', filter_value = 2019)
{"filter_type": "eq", "filter_variable": "year", "filter_value": 2019}
equalToFilter(filterVariable:=year, filterValue:=2019)

The EqualToFilter constructs a filter equivalent to var = val comparisons.

BetweenFilter

Use this filter to restrict data where the age grouping is between 25 and 39 years old (inclusive)

from strato_query.filters import BetweenFilter

BetweenFilter(var='age_g', val=[6, 8])
library(stRatoquery)

between_filter(filter_variable = 'age_g', filter_value = c(6, 8))
{"filter_type": "between", "filter_variable": "age_g", "filter_value": [6, 8]}
betweenFilter(filterVariable:="age_g", filterValue:=Array(6, 8))

The BetweenFilter constructs a filter equivalent to var >= val AND var <= val comparisons.

InFilter

Use this filter to restrict data where the year is one of the allowed values

from strato_query.filters import InFilter

InFilter(var='year', val=[2014, 2019, 2024])
library(stRatoquery)

in_filter(filter_variable = 'year', filter_value = c(2014, 2019, 2024))
{"filter_type": "in", "filter_variable": "year", "filter_value": [2014, 2019, 2024]}
inFilter(filterValue:="year", filterValue:=Array(2014, 2019, 2024))

The InFilter constructs a filter equivalent to var IN (val1, val2, val3) comparisons.

GreaterThanFilter

Use this filter to restrict data where the educational attainment level is greater than high school

from strato_query.filters import GreaterThanFilter

GreaterThanFilter(var='educational_attainment', val=2)
library(stRatoquery)

gt_filter(filter_variable = 'educational_attainment', filter_value = 2)
{"filter_type": "gt", "filter_variable": "educational_attainment", "filter_value": 2}
greaterThanFilter(filterVariable:="educational_attainment", filterValue:=2)

The GreaterThan constructs a filter equivalent to var > val comparisons.

GreaterThanOrEqualToFilter

Use this filter to restrict data where the educational attainment level is greater than or equal to high school

from strato_query.filters import GreaterThanOrEqualToFilter

GreaterThanOrEqualToFilter(var='educational_attainment', val=2)
library(stRatoquery)

gte_filter(filter_variable = 'educational_attainment', filter_value = 2)
{"filter_type": "gte", "filter_variable": "educational_attainment", "filter_value": 2}
greaterThanOrEqualToFilter(filterVariable:="educational_attainment", filterValue:=2)

The GreaterThanOrEqualTo constructs a filter equivalent to var >= val comparisons.

LessThanFilter

Use this filter to restrict data where the educational attainment level is less than high school

from strato_query.filters import LessThanFilter

LessThanFilter(var='educational_attainment', val=2)
library(stRatoquery)

lt_filter(filter_variable = 'educational_attainment', filter_value = 2)
{"filter_type": "lt", "filter_variable": "educational_attainment", "filter_value": 2}
lessThanFilter(filterVariable:="educational_attainment", filterValue:=2)

The LessThan constructs a filter equivalent to var > val comparisons.

LessThanOrEqualToFilter

Use this filter to restrict data where the educational attainment level is less than or equal to high school

from strato_query.filters import LessThanOrEqualToFilter

LessThanOrEqualToFilter(var='educational_attainment', val=2)
library(stRatoquery)

lte_filter(filter_variable = 'educational_attainment', filter_value = 2)
{"filter_type": "lte", "filter_variable": "educational_attainment", "filter_value": 2}
lessThanOrEqualToFilter(filterVariable:="educational_attainment", filterValue:=2)

The LessThanOrEqualTo constructs a filter equivalent to var >= val comparisons.

NotEqualToFilter

Use this filter to restrict data to all years except 2019

from strato_query.filters import NotEqualToFilter

NotEqualToFilter(
    var='year',
    val=2019)
library(stRatoquery)

ne_filter(filter_variable = 'year', filter_value = 2019)
{"filter_type": "ne", "filter_variable": "year", "filter_value": 2019}
notEqualTo(filterVariable:=year, filterValue:=2019)

The NotEqualToFilter constructs a filter equivalent to var <> val comparisons.

NotInFilter

Use this filter to restrict data where the year is not any of the specified values

from strato_query.filters import NotInFilter

NotInFilter(var='year', val=[2014, 2019, 2024])
library(stRatoquery)

nin_filter(filter_variable = 'year', filter_value = c(2014, 2019, 2024))
{"filter_type": "nin", "filter_variable": "year", "filter_value": [2014, 2019, 2024]}
notInFilter(filterValue:="year", filterValue:=Array(2014, 2019, 2024))

The NotInFilter constructs a filter equivalent to var NOT IN (val1, val2, val3) comparisons.

MileRadiusFilter

Use this filter to restrict results to geographies within a mile radius

from strato_query.filters import MileRadiusFilter

MileRadiusFilter(latitude=40.7589, longitude=-73.9937, miles=5)

# This gets all geographies intersecting with the mile radius 
# (any amount of intersection), and does not apply weights
MileRadiusFilter(latitude=40.7590, longitude=-73.9937, miles=5, detailed_type='mile_radius_unweighted')
library(stRatoquery)

mile_radius_filter(latitude = 40.7589, longitude = -73.9937, miles = 5)
{"filter_type": "mile_radius", "filter_variable": "", "filter_value": {"latitude": 40.7590, "longitude": -73.9936, "miles": 5}}
{"filter_type": "mile_radius_simple", "filter_variable": "", "filter_value": {"latitude": 40.7590, "longitude": -73.9936, "miles": 5}}
{"filter_type": "mile_radius_unweighted", "filter_variable": "", "filter_value": {"latitude": 40.7590, "longitude": -73.9936, "miles": 5}}
mileRadiusFilter(latitude:=40.7589, longitude:=-73.9937, miles:=5)

The MileRadiusFilter constructs a filter to restrict results to geographies intersecting/contained by the mile radius buffer

Advanced options

An advanced option for the MileRadiusFilter is the detailed_type argument, which allows one of three options:

DrivetimeFilter

Use this filter to restrict results to geographies within an estimated drive time

from strato_query.filters import DrivetimeFilter

DrivetimeFilter(latitude=40.7589, longitude=-73.9937, minutes=15)
# Compute 15-minute drive with traffic
DrivetimeFilter(latitude=40.7589, longitude=-73.9937, minutes=15, with_traffic=True, start_time='2019-05-25T18:00:00')
# This gets all geographies intersecting with the drive time buffer
# (any amount of intersection) and does not apply weights
DrivetimeFilter(latitude=40.7589, longitude=-73.9937, minutes=15, detailed_type='drivetime_unweighted')
library(stRatoquery)

drivetime_filter(latitude = 40.7589, longitude = -73.9937, minutes = 15)
{"filter_type": "drivetime", "filter_variable": "", "filter_value": {"latitude": 40.7590, "longitude": -73.9936, "minutes": 15}}
{"filter_type": "drivetime_simple", "filter_variable": "", "filter_value": {"latitude": 40.7590, "longitude": -73.9936, "minutes": 15}}
{"filter_type": "drivetime_unweighted", "filter_variable": "", "filter_value": {"latitude": 40.7590, "longitude": -73.9936, "minutes": 15}}
{"filter_type": "drivetime", "filter_variable": "", "filter_value": {"latitude": 40.7590, "longitude": -73.9936, "minutes": 15, "with_traffic": true, "start_time": "2019-05-25T18:00:00"}}
drivetimeFilter(latitude:=40.7589, longitude:=-73.9937, minutes:=5)

The DrivetimeFilter constructs a filter to restrict results to geographies intersecting/contained by the drive time buffer

Advanced options

An advanced option for the DrivetimeFilter is the detailed_type argument, which allows one of three options:

To compute drive time estimates with traffic, two arguments are used: with_traffic and start_time:

IntersectsFilter

Use this filter to restrict results to geographies intersecting a GeoJSON Polygon

from strato_query.filters import IntersectsFilter

IntersectsFilter(
    var='geometry', 
    val={
      "type": "Feature",
      "properties": {},
      "geometry": {
        "type": "Polygon",
        "coordinates": [[
            [-71.13269805908203, 42.298135272741206],
            [-70.99433898925781, 42.298135272741206],
            [-70.99433898925781, 42.385937107381125],
            [-71.13269805908203, 42.385937107381125],
            [-71.13269805908203, 42.298135272741206]
          ]]
      }
    })
# Not implemented yet!
{"filter_type": "intersects", "filter_variable": "geometry", "filter_value": {"type": "Feature", "properties": {}, "geometry": {"type": "Polygon", "coordinates": [[[-71.13269805908203, 42.298135272741206], [-70.99433898925781, 42.298135272741206], [-70.99433898925781, 42.385937107381125], [-71.13269805908203, 42.385937107381125], [-71.13269805908203, 42.298135272741206]]]}}
' Not implemented yet!

The IntersectsFilter constructs a filter to restrict results to geographies intersecting the GeoJSON polygon

Aggregations

In many cases, we want to aggregate data returned by the API. For example, if we have the SQL query: SELECT year, geoid5, sum(households) FROM incomeforecast_county_annual_income_group_age WHERE income_g >= 5 AND age_g >= 17 GROUP BY year, geoid5 ORDER BY year, geoid5;, we need to group by the year and geoid5 (county) columns, and then get the sum of households for each combination.

Aggregrations tell the API which columns to aggregate, using which function.

SumAggregation

This tells the API to take the sum of population

from strato_query.aggregations import SumAggregation

SumAggregation('population')
library(stRatoquery)

sum_aggregation(variable_name = 'population')
{"aggregation_type": "sum", "variable_name": "population"}
sumAggregation(variableName:="population")

Sum aggregations tell the API to return the sum of the variable passed in, equivalent to sum(variable_name) in SQL

Sample queries

Population density in the Boston, MA MSA

Population density in Boston up to 2015:

from strato_query import SDAPIQuery, APIQueryParams, EqualToFilter, LessThanFilter

df = SDAPIQuery.query_api_df(
    query_params=APIQueryParams(
        query_type='COUNT',
        table='populationforecast_metro_annual_population',
        data_fields=('year', 'cbsa', {'population': 'population'}),
        data_filters=(
            LessThanFilter(var='year', val=2015),
            EqualToFilter(var='cbsa', val=14454),
        ),
        aggregations=(dict(aggregation_func='sum', variable_name='population'),),
        groupby=('cbsa', 'year'),
        order=('year',),
        join=APIQueryParams(
            query_type='AREA',
            table='geocookbook_metro_na_shapes_full',
            data_fields=('cbsa', 'area', 'name'),
            data_filters=(EqualToFilter(var='cbsa', val=14454),),
            groupby=('cbsa', 'name'),
            aggregations=(),
            on=dict(left=('cbsa',), right=('cbsa',)),
        )
    )
)
library(stRatoquery)

apiToken = 'my-api-token'

df = submit_api_query(
  query = api_query_params(
    table = 'populationforecast_metro_annual_population',
    data_fields = api_fields(fields_list = list('year', 'cbsa', list(population = 'population'))),
    data_filters = list(
        lt_filter(filter_variable = 'year', filter_value = 2015),
        eq_filter(filter_variable = 'cbsa', filter_value = 14454)
    ),
    groupby=c('year', 'cbsa'),
    aggregations = list(sum_aggregation(variable_name = 'population')),
    join = api_query_params(
        table = 'geocookbook_metro_na_shapes_full',
        query_type = 'AREA',
        data_fields = api_fields(fields_list = list('cbsa', 'area', 'name')),
        data_filters = list(),
        groupby = c('cbsa', 'name'),
        aggregations = list(),
        on = list(left = c('cbsa'), right = c('cbsa'))
    )
  ),
  apiToken = apiToken)

df$pop_per_sq_mi = df$population / df$area
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"query\": { \"query_type\": \"COUNT\", \"data_fields\": [ \"year\", \"cbsa\", { \"population\": \"population\" } ], \"table\": \"populationforecast_metro_annual_population\", \"groupby\": [ \"cbsa\", \"year\" ], \"data_filters\": [ { \"filter_type\": \"lt\", \"filter_value\": 2015, \"filter_variable\": \"year\" }, { \"filter_type\": \"eq\", \"filter_value\": 14454, \"filter_variable\": \"cbsa\" } ], \"aggregations\": [ { \"aggregation_func\": \"sum\", \"variable_name\": \"population\" } ], \"join\": { \"query_type\": \"AREA\", \"data_fields\": [ \"cbsa\", \"area\", \"name\" ], \"table\": \"geocookbook_metro_na_shapes_full\", \"groupby\": [ \"cbsa\", \"name\" ], \"data_filters\": [ { \"filter_type\": \"eq\", \"filter_value\": 14454, \"filter_variable\": \"cbsa\" } ], \"aggregations\": [], \"on\": { \"left\": [ \"cbsa\" ], \"right\": [ \"cbsa\" ] } }, \"order\": [ \"year\" ]}}"

Calculating the population density (population per square mile) in the Boston, MA MSA, by year.

YEAR NAME POP_PER_SQ_MI
2014 Boston, MA 1665.827530
2013 Boston, MA 1651.187549
2012 Boston, MA 1633.847778
2011 Boston, MA 1617.340007
2010 Boston, MA 1601.802067

Population within five miles of latitude-longitude pair

Population within five miles of 40.7589542, -73.9937348'):

from strato_query import SDAPIQuery, APIQueryParams, MileRadiusFilter, BetweenFilter

df = SDAPIQuery.query_api_df(
    query_params=APIQueryParams(
        table='populationforecast_tract_annual_population',
        data_fields=('YEAR', {'population': 'population_within_5_miles'}),
        data_filters=(
            # Aggregate data within five miles of 40.7589542, -73.9937348
            MileRadiusFilter(
                latitude=40.75895, longitude=-73.9937, miles=5),
            # Only get data for years between 2010 and 2020 (inclusive)
            BetweenFilter(var='year', val=[2010, 2020])),
        aggregations=({'variable_name': 'population', 'aggregation_func': 'sum'},),
        groupby=('year',)))
library(stRatoquery)

apiToken = 'my-api-token'

df = submit_api_query(
  api_query_params(
    table = 'populationforecast_tract_annual_population',
    data_fields = api_fields(fields_list = list('YEAR', list(population = 'population_within_5_miles'))),
    data_filters = list(
      # Aggregate data within five miles of 40.7589, -73.9937
      mile_radius_filter(latitude = 40.7589, longitude = -73.9937, miles = 5),
      # Only get data for years between 2010 and 2020 (inclusive)
      between_filter(filter_variable = 'year', filter_value = c(2010, 2020))),
    aggregations = list(sum_aggregation(variable_name = 'population')),
    groupby = c('year')),
  apiToken = apiToken)
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"query\": { \"query_type\": \"COUNT\", \"data_fields\": [ \"YEAR\", { \"population\": \"population_within_5_miles\" } ], \"table\": \"populationforecast_tract_annual_population\", \"groupby\": [ \"year\" ], \"data_filters\": [ { \"filter_type\": \"mile_radius\", \"filter_value\": { \"latitude\": 40.75895, \"longitude\": -73.9937, \"miles\": 5 }, \"filter_variable\": \"\" }, { \"filter_type\": \"between\", \"filter_value\": [ 2010, 2020 ], \"filter_variable\": \"year\" } ], \"aggregations\": [ { \"variable_name\": \"population\", \"aggregation_func\": \"sum\" } ]}}"

Calculating the population within a mile radius buffer, by year.

YEAR POPULATION_WITHIN_5_MILES
2010 2.333544e+06
2011 2.369469e+06
2012 2.400245e+06
2013 2.420539e+06
2014 2.438206e+06

Submitting multiple queries

Submit multiple queries with unique IDs to the API for a batch query

from strato_query import SDAPIQuery, APIQueryParams

# This returns a dictionary with the query ID pointing to a pandas.DataFrame
SDAPIQuery.query_api_multiple(
    queries={
        'query1': APIQueryParams(
            table='populationforecast_us_annual_population',
            data_fields=('year', 'population'),
            data_filters=(),
            groupby=(),
            aggregations=()),
        'query2': APIQueryParams(
            table='populationforecast_state_annual_population_age',
            data_fields=('year', 'geoid2', 'age_g', 'population'),
            data_filters=(),
            groupby=(),
            aggregations=()),
    })
# Not officially supported
$ curl -X POST "https://api.stratodem.com/api" \
    -H "accept: application/json" \
    -H "Content-Type: application/json" \
    -d "{ \"token\": \"my-api-token\", \"queries\": {\"query1\": { \"query_type\": \"COUNT\", \"data_fields\": [ \"year\", \"cbsa\", { \"population\": \"population\" } ], \"table\": \"populationforecast_metro_annual_population\", \"groupby\": [ \"cbsa\", \"year\" ], \"data_filters\": [ { \"filter_type\": \"lt\", \"filter_value\": 2015, \"filter_variable\": \"year\" }, { \"filter_type\": \"eq\", \"filter_value\": 14454, \"filter_variable\": \"cbsa\" } ], \"aggregations\": [ { \"aggregation_func\": \"sum\", \"variable_name\": \"population\" } ], \"join\": { \"query_type\": \"AREA\", \"data_fields\": [ \"cbsa\", \"area\", \"name\" ], \"table\": \"geocookbook_metro_na_shapes_full\", \"groupby\": [ \"cbsa\", \"name\" ], \"data_filters\": [ { \"filter_type\": \"eq\", \"filter_value\": 14454, \"filter_variable\": \"cbsa\" } ], \"aggregations\": [], \"on\": { \"left\": [ \"cbsa\" ], \"right\": [ \"cbsa\" ] } }, \"order\": [ \"year\" ]}}, \"query2\": { \"query_type\": \"COUNT\", \"data_fields\": [ \"year\", \"cbsa\", { \"population\": \"population\" } ], \"table\": \"populationforecast_metro_annual_population\", \"groupby\": [ \"cbsa\", \"year\" ], \"data_filters\": [ { \"filter_type\": \"lt\", \"filter_value\": 2015, \"filter_variable\": \"year\" }, { \"filter_type\": \"eq\", \"filter_value\": 14454, \"filter_variable\": \"cbsa\" } ], \"aggregations\": [ { \"aggregation_func\": \"sum\", \"variable_name\": \"population\" } ], \"join\": { \"query_type\": \"AREA\", \"data_fields\": [ \"cbsa\", \"area\", \"name\" ], \"table\": \"geocookbook_metro_na_shapes_full\", \"groupby\": [ \"cbsa\", \"name\" ], \"data_filters\": [ { \"filter_type\": \"eq\", \"filter_value\": 14454, \"filter_variable\": \"cbsa\" } ], \"aggregations\": [], \"on\": { \"left\": [ \"cbsa\" ], \"right\": [ \"cbsa\" ] } }, \"order\": [ \"year\" ]}}}}"
' Not officially supported

The StratoDem Analytics API supports batches of queries to execute in parallel. The Python package supports this through the SDAPIQuery.query_api_multiple method, which takes a dictionary of queries and returns a dictionary of pandas.DataFrames