BranchesTags

Repository files navigation

Setup

Clone the repository and ensure an installation of uv exists. Create a local virtual environment by running uv venv then uv sync in the command line. Ensure that a secrets.toml file exists

Configuration of Private Data in secrets.toml

In order to avoid exposing certain data to the public this repository uses a secrets file to store sensitive configurations in addition to a standard configuration file. This file is stored in the root directory of the repository as secrets.toml and is included in the .gitignore intentionally to avoid it ever being committed to the repository.

The secrets.toml should mirror the following structure.

[sql.estimates]
server = "<SqlInstanceName>"# SQL instance containing estimates databasedatabase = "<SqlDatabaseName>"# database within SQL instance containing SQL build objects
[sql.gis]
server = "<SqlInstanceName>"# SQL instance containing GIS databasedatabase = "<SqlDatabaseName>"# database within instance containing GIS datasets (GQ/LUDU)
[sql]
staging = '<FolderPath>'# unconditional network folder path visible to SQL instance for BULK INSERT

Installation of ODBC Driver 18

You will need ODBC Driver 18 installed on your device to execute the python scripts. To check if ODBC Driver 18 is installed, run the program odbcad32.exe locally using the windows search bar and navigate to the Drivers search bar. If you already have the correct driver installed, there is nothing else you need to do. If you are missing the driver or do not have the most up to date driver, follow the instructions from pyodbc to install the proper driver (Note, link is a permalink and may not be the most recent wiki revision). Afterwards, you will see ODBC Driver 18 for SQL Server in the list of installed drivers (you may have to close and re-open the odbcad32.exe screen).

Running

Set the configuration file config.toml parameters specific to the run in the project root directory. Finally, simply execute uv run main.py in the main project directory

Configuration File Settings

The default version of the runtime configuration file is copied here, with comments explaining each and every key/value pair

# Configuration for what parts of the Estimates Program to run. Since this file may be# modified from the default settings, you can always restore to default using the copy# stored in README.md. For brevity, detailed comments have been removed from this file# The 'run' section contains configuration for running every module of the Estimates# Program for a specified set of years
[run]
# Whether to use the 'run' section. Mutually exclusive with 'debug' modeenabled = true# The MGRA series to use for this run. Currently only '15' is validseries = 15# The first year inclusive to start running fromstart_year = 2020# The last year inclusive to end running withend_year = 2025# The code versionversion = "1.2.2-dev"# Additional notes on this runcomments = "Example comment"# The `debug` section contains configuration for running a single module for a single# year based on the input data of an existing complete Estimates run. Output data is not# written to database, but is instead saved to a local folder debug_output\, which is # ignored by .gitignore. No data is saved locally for the "startup" and "staging" # modules
[debug]
# Whether to use the 'debug' section. Mutually exclusive with 'run' modeenabled = false# The [run_id] of a fully [complete] Estimates Program run. Input data for debugging# will be pulled from this [run_id]run_id = 224# The run_id for the released v25 Estimates# The year of the Estimates Program to run. This year must be consistent with the stored# [start_year] and [end_year] associated with the above [run_id] in [metadata].[run]year = 2020# The module of the Estimates Program to run. Since only [complete] [run_id]s are # allowed, this can be any Estimates Program module. Explicitly, the valid inputs# are "startup", "housing_and_households", "population", "population_by_ase", # "household_characteristics", "employment", or "staging"module = ""

Production Database Schema

erDiagram
direction TB
metadata_run {
run_id INT PK
series INT
start_year INT
end_year INT
user NVARCHAR(100)
start_date DATETIME
end_date DATETIME
version NVARCHAR(50)
comments NVARCHAR(MAX)
complete BIT
}
inputs_controls_ase {
run_id INT UK, FK
year INT UK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
inputs_controls_jobs {
run_id INT UK, FK
year INT UK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
inputs_controls_tract {
run_id INT UK, FK
year INT UK
tract NVARCHAR(11) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_controls_jurisdiction {
run_id INT UK, FK
year INT UK
jurisdiction NVARCHAR(15) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_mgra {
run_id INT PK, FK
mgra INT PK
shape geometry
}
inputs_special_mgras {
id INT PK
series INT UK
mgra INT UK
start_year INT UK
end_year INT UK
pop_type NVARCHAR(75) UK
sex NVARCHAR(6) UK
min_age INT UK
max_age INT UK
comment NVARCHAR(MAX)
}
outputs_ase {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
outputs_gq {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
gq_type NVARCHAR(75) UK
value INT
}
outputs_hh {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hh_characteristics {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
metric NVARCHAR(100) UK
value INT
}
outputs_hs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hhp {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
value INT
}
outputs_jobs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
%% Relationships
metadata_run ||--o{ inputs_controls_ase : "run_id"
metadata_run ||--o{ inputs_controls_jobs : "run_id"
metadata_run ||--o{ inputs_controls_tract : "run_id"
metadata_run ||--o{ inputs_controls_jurisdiction : "run_id"
metadata_run ||--o{ inputs_mgra : "run_id"
metadata_run ||--o{ outputs_ase : "run_id"
metadata_run ||--o{ outputs_gq : "run_id"
metadata_run ||--o{ outputs_hh : "run_id"
metadata_run ||--o{ outputs_hh_characteristics : "run_id"
metadata_run ||--o{ outputs_hs : "run_id"
metadata_run ||--o{ outputs_hhp : "run_id"
metadata_run ||--o{ outputs_jobs : "run_id"
inputs_mgra ||--o{ outputs_ase : "run_id, mgra"
inputs_mgra ||--o{ outputs_gq : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh_characteristics : "run_id, mgra"
inputs_mgra ||--o{ outputs_hs : "run_id, mgra"
inputs_mgra ||--o{ outputs_hhp : "run_id, mgra"
inputs_mgra ||--o{ outputs_jobs : "run_id, mgra"
Loading

Versioning and Releases

Estimates Program follows a non-standard release schedule. Rather than doing a new release after changes, bug fixes, or new features, Estimates Program only has a new release when there's output data ready to be shared with non Estimates & Forecasts team members. These are nearly always associated with the annual release of Estimates, with the data first shared with SANDAG's QA/QC team and eventually to the public via SANDAG's Open Data Portal.

Release Format

Releases follow a standard format which can be seen on any release on the Releases page. Each release is associated with a newly created Git tag for the released version in the format vX.X.X (also see Semantic Versioning). The release title matches the tagged version in this format: Estimates Program vX.X.X. Release notes begin with metadata describing the purpose of the release and the production database [run_id](s) for external consumption associated with that release. An optional Major Update(s) section follows, summarizing the automatically generated release notes listed below it. The automatically generated release notes are created by clicking the "Generate release notes" button

How to Release

Once a production run is ready for external consumption, the following manual steps are performed. Note, these changes can be made directly to the main branch

  1. Set the config.toml default version to the new release version vX.X.X
  2. Add the new release version vX.X.X to the list of allowable versions in InputParser:_validate_config() in the file python/parsers.py
  3. Update the default configuration example in README.md to show vX.X.X
  4. A tag and release is made following the release format described above.
  5. Any associated [run_id](s) in the production database identified in the release notes have the [version] field in the [metadata].[run] table manually updated to reflect the release version vX.X.X.
  6. Repeat steps 1-3 but with version vX.X.X-dev

About

SANDAG's Annual Estimates Program

Topics

estimates-and-forecasts

Resources

Readme
Activity
Custom properties

Stars

0 stars

Watchers

3 watching

Forks

0 forks
Report repository

Releases

Packages

Used by

Contributors

Languages

Generated from SANDAG/Estimates-Forecasts-Template
, 'i'); if (__m === '*' || __re.test(location.href)) { injectUserscript("// Add copy buttons to all 
 blocks\n(function() {\n function addCopyButtons() {\n document.querySelectorAll('pre code').forEach(function(codeBlock) {\n if (codeBlock.parentElement.hasAttribute('data-copy-added')) return;\n codeBlock.parentElement.setAttribute('data-copy-added', 'true');\n \n var btn = document.createElement('button');\n btn.textContent = 'Copy';\n btn.style.cssText = 'position:absolute;top:4px;right:4px;padding:2px 8px;font-size:11px;background:#4ecdc4;border:none;border-radius:4px;color:#1a1a2e;cursor:pointer;opacity:0.7;transition:opacity 0.2s;';\n btn.onmouseover = function() { this.style.opacity = '1'; };\n btn.onmouseout = function() { this.style.opacity = '0.7'; };\n btn.onclick = function() {\n navigator.clipboard.writeText(codeBlock.textContent).then(function() {\n btn.textContent = 'Copied!';\n setTimeout(function() { btn.textContent = 'Copy'; }, 1500);\n });\n };\n codeBlock.parentElement.style.position = 'relative';\n codeBlock.parentElement.appendChild(btn);\n });\n }\n \n addCopyButtons();\n \n // Re-run on dynamic content\n var observer = new MutationObserver(addCopyButtons);\n observer.observe(document.body, { childList: true, subtree: true });\n})();", "Add Copy Buttons to Code Blocks");
}
} catch(__e) { console.warn('[Userscript:Add Copy Buttons to Code Blocks]', __e); }
})();
(function(){
try {
var __m = "github.com";
var __re = new RegExp('^' + "github\\.com" + '

Skip to content
BranchesTags
Repository files navigation
Setup
Clone the repository and ensure an installation of uv exists. Create a local virtual environment by running uv venv then uv sync in the command line. Ensure that a secrets.toml file exists
Configuration of Private Data in secrets.toml
In order to avoid exposing certain data to the public this repository uses a secrets file to store sensitive configurations in addition to a standard configuration file. This file is stored in the root directory of the repository as secrets.toml and is included in the .gitignore intentionally to avoid it ever being committed to the repository.
The secrets.toml should mirror the following structure.
[sql.estimates]
server = "<SqlInstanceName>"# SQL instance containing estimates databasedatabase = "<SqlDatabaseName>"# database within SQL instance containing SQL build objects
[sql.gis]
server = "<SqlInstanceName>"# SQL instance containing GIS databasedatabase = "<SqlDatabaseName>"# database within instance containing GIS datasets (GQ/LUDU)
[sql]
staging = '<FolderPath>'# unconditional network folder path visible to SQL instance for BULK INSERT
Installation of ODBC Driver 18
You will need ODBC Driver 18 installed on your device to execute the python scripts. To check if ODBC Driver 18 is installed, run the program odbcad32.exe locally using the windows search bar and navigate to the Drivers search bar. If you already have the correct driver installed, there is nothing else you need to do. If you are missing the driver or do not have the most up to date driver, follow the instructions from pyodbc to install the proper driver (Note, link is a permalink and may not be the most recent wiki revision). Afterwards, you will see ODBC Driver 18 for SQL Server in the list of installed drivers (you may have to close and re-open the odbcad32.exe screen).
Running
Set the configuration file config.toml parameters specific to the run in the project root directory. Finally, simply execute uv run main.py in the main project directory
Configuration File Settings
The default version of the runtime configuration file is copied here, with comments explaining each and every key/value pair
# Configuration for what parts of the Estimates Program to run. Since this file may be# modified from the default settings, you can always restore to default using the copy# stored in README.md. For brevity, detailed comments have been removed from this file# The 'run' section contains configuration for running every module of the Estimates# Program for a specified set of years
[run]
# Whether to use the 'run' section. Mutually exclusive with 'debug' modeenabled = true# The MGRA series to use for this run. Currently only '15' is validseries = 15# The first year inclusive to start running fromstart_year = 2020# The last year inclusive to end running withend_year = 2025# The code versionversion = "1.2.2-dev"# Additional notes on this runcomments = "Example comment"# The `debug` section contains configuration for running a single module for a single# year based on the input data of an existing complete Estimates run. Output data is not# written to database, but is instead saved to a local folder debug_output\, which is # ignored by .gitignore. No data is saved locally for the "startup" and "staging" # modules
[debug]
# Whether to use the 'debug' section. Mutually exclusive with 'run' modeenabled = false# The [run_id] of a fully [complete] Estimates Program run. Input data for debugging# will be pulled from this [run_id]run_id = 224# The run_id for the released v25 Estimates# The year of the Estimates Program to run. This year must be consistent with the stored# [start_year] and [end_year] associated with the above [run_id] in [metadata].[run]year = 2020# The module of the Estimates Program to run. Since only [complete] [run_id]s are # allowed, this can be any Estimates Program module. Explicitly, the valid inputs# are "startup", "housing_and_households", "population", "population_by_ase", # "household_characteristics", "employment", or "staging"module = ""
Production Database Schema
erDiagram
direction TB
metadata_run {
run_id INT PK
series INT
start_year INT
end_year INT
user NVARCHAR(100)
start_date DATETIME
end_date DATETIME
version NVARCHAR(50)
comments NVARCHAR(MAX)
complete BIT
}
inputs_controls_ase {
run_id INT UK, FK
year INT UK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
inputs_controls_jobs {
run_id INT UK, FK
year INT UK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
inputs_controls_tract {
run_id INT UK, FK
year INT UK
tract NVARCHAR(11) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_controls_jurisdiction {
run_id INT UK, FK
year INT UK
jurisdiction NVARCHAR(15) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_mgra {
run_id INT PK, FK
mgra INT PK
shape geometry
}
inputs_special_mgras {
id INT PK
series INT UK
mgra INT UK
start_year INT UK
end_year INT UK
pop_type NVARCHAR(75) UK
sex NVARCHAR(6) UK
min_age INT UK
max_age INT UK
comment NVARCHAR(MAX)
}
outputs_ase {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
outputs_gq {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
gq_type NVARCHAR(75) UK
value INT
}
outputs_hh {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hh_characteristics {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
metric NVARCHAR(100) UK
value INT
}
outputs_hs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hhp {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
value INT
}
outputs_jobs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
%% Relationships
metadata_run ||--o{ inputs_controls_ase : "run_id"
metadata_run ||--o{ inputs_controls_jobs : "run_id"
metadata_run ||--o{ inputs_controls_tract : "run_id"
metadata_run ||--o{ inputs_controls_jurisdiction : "run_id"
metadata_run ||--o{ inputs_mgra : "run_id"
metadata_run ||--o{ outputs_ase : "run_id"
metadata_run ||--o{ outputs_gq : "run_id"
metadata_run ||--o{ outputs_hh : "run_id"
metadata_run ||--o{ outputs_hh_characteristics : "run_id"
metadata_run ||--o{ outputs_hs : "run_id"
metadata_run ||--o{ outputs_hhp : "run_id"
metadata_run ||--o{ outputs_jobs : "run_id"
inputs_mgra ||--o{ outputs_ase : "run_id, mgra"
inputs_mgra ||--o{ outputs_gq : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh_characteristics : "run_id, mgra"
inputs_mgra ||--o{ outputs_hs : "run_id, mgra"
inputs_mgra ||--o{ outputs_hhp : "run_id, mgra"
inputs_mgra ||--o{ outputs_jobs : "run_id, mgra"

Loading
Versioning and Releases
Estimates Program follows a non-standard release schedule. Rather than doing a new release after changes, bug fixes, or new features, Estimates Program only has a new release when there's output data ready to be shared with non Estimates & Forecasts team members. These are nearly always associated with the annual release of Estimates, with the data first shared with SANDAG's QA/QC team and eventually to the public via SANDAG's Open Data Portal.
Release Format
Releases follow a standard format which can be seen on any release on the Releases page. Each release is associated with a newly created Git tag for the released version in the format vX.X.X (also see Semantic Versioning). The release title matches the tagged version in this format: Estimates Program vX.X.X. Release notes begin with metadata describing the purpose of the release and the production database [run_id](s) for external consumption associated with that release. An optional Major Update(s) section follows, summarizing the automatically generated release notes listed below it. The automatically generated release notes are created by clicking the "Generate release notes" button
How to Release
Once a production run is ready for external consumption, the following manual steps are performed. Note, these changes can be made directly to the main branch
  1. Set the config.toml default version to the new release version vX.X.X
  2. Add the new release version vX.X.X to the list of allowable versions in InputParser:_validate_config() in the file python/parsers.py
  3. Update the default configuration example in README.md to show vX.X.X
  4. A tag and release is made following the release format described above.
  5. Any associated [run_id](s) in the production database identified in the release notes have the [version] field in the [metadata].[run] table manually updated to reflect the release version vX.X.X.
  6. Repeat steps 1-3 but with version vX.X.X-dev
About
SANDAG's Annual Estimates Program
Topics
estimates-and-forecasts
Resources
Readme
Activity
Custom properties
Stars
0 stars
Watchers
3 watching
Forks
0 forks
Report repository
Releases
Packages
Used by
Contributors
Languages
Generated from SANDAG/Estimates-Forecasts-Template

, 'i');
if (__m === '*' || __re.test(location.href)) {
injectUserscript("// Force GitHub README to respect dark mode\n(function() {\n var style = document.createElement('style');\n style.textContent = '\n .markdown-body {\n color-scheme: dark light;\n }\n .markdown-body pre { background: #161b22 !important; }\n .markdown-body code { background: rgba(110, 118, 129, 0.4) !important; }\n .markdown-body table th, .markdown-body table td { border-color: #30363d !important; }\n .markdown-body img { background: #0d1117; }\n .markdown-body blockquote { border-left-color: #8b949e; }\n .markdown-body hr { border-color: #30363d; }\n ';\n document.head.appendChild(style);\n})();", "GitHub Dark Mode README Fix");
}
} catch(__e) { console.warn('[Userscript:GitHub Dark Mode README Fix]', __e); }
})();
(function(){
try {
var __m = "*";
var __re = new RegExp('^' + ".*" + '

Skip to content
BranchesTags
Repository files navigation
Setup
Clone the repository and ensure an installation of uv exists. Create a local virtual environment by running uv venv then uv sync in the command line. Ensure that a secrets.toml file exists
Configuration of Private Data in secrets.toml
In order to avoid exposing certain data to the public this repository uses a secrets file to store sensitive configurations in addition to a standard configuration file. This file is stored in the root directory of the repository as secrets.toml and is included in the .gitignore intentionally to avoid it ever being committed to the repository.
The secrets.toml should mirror the following structure.
[sql.estimates]
server = "<SqlInstanceName>"# SQL instance containing estimates databasedatabase = "<SqlDatabaseName>"# database within SQL instance containing SQL build objects
[sql.gis]
server = "<SqlInstanceName>"# SQL instance containing GIS databasedatabase = "<SqlDatabaseName>"# database within instance containing GIS datasets (GQ/LUDU)
[sql]
staging = '<FolderPath>'# unconditional network folder path visible to SQL instance for BULK INSERT
Installation of ODBC Driver 18
You will need ODBC Driver 18 installed on your device to execute the python scripts. To check if ODBC Driver 18 is installed, run the program odbcad32.exe locally using the windows search bar and navigate to the Drivers search bar. If you already have the correct driver installed, there is nothing else you need to do. If you are missing the driver or do not have the most up to date driver, follow the instructions from pyodbc to install the proper driver (Note, link is a permalink and may not be the most recent wiki revision). Afterwards, you will see ODBC Driver 18 for SQL Server in the list of installed drivers (you may have to close and re-open the odbcad32.exe screen).
Running
Set the configuration file config.toml parameters specific to the run in the project root directory. Finally, simply execute uv run main.py in the main project directory
Configuration File Settings
The default version of the runtime configuration file is copied here, with comments explaining each and every key/value pair
# Configuration for what parts of the Estimates Program to run. Since this file may be# modified from the default settings, you can always restore to default using the copy# stored in README.md. For brevity, detailed comments have been removed from this file# The 'run' section contains configuration for running every module of the Estimates# Program for a specified set of years
[run]
# Whether to use the 'run' section. Mutually exclusive with 'debug' modeenabled = true# The MGRA series to use for this run. Currently only '15' is validseries = 15# The first year inclusive to start running fromstart_year = 2020# The last year inclusive to end running withend_year = 2025# The code versionversion = "1.2.2-dev"# Additional notes on this runcomments = "Example comment"# The `debug` section contains configuration for running a single module for a single# year based on the input data of an existing complete Estimates run. Output data is not# written to database, but is instead saved to a local folder debug_output\, which is # ignored by .gitignore. No data is saved locally for the "startup" and "staging" # modules
[debug]
# Whether to use the 'debug' section. Mutually exclusive with 'run' modeenabled = false# The [run_id] of a fully [complete] Estimates Program run. Input data for debugging# will be pulled from this [run_id]run_id = 224# The run_id for the released v25 Estimates# The year of the Estimates Program to run. This year must be consistent with the stored# [start_year] and [end_year] associated with the above [run_id] in [metadata].[run]year = 2020# The module of the Estimates Program to run. Since only [complete] [run_id]s are # allowed, this can be any Estimates Program module. Explicitly, the valid inputs# are "startup", "housing_and_households", "population", "population_by_ase", # "household_characteristics", "employment", or "staging"module = ""
Production Database Schema
erDiagram
direction TB
metadata_run {
run_id INT PK
series INT
start_year INT
end_year INT
user NVARCHAR(100)
start_date DATETIME
end_date DATETIME
version NVARCHAR(50)
comments NVARCHAR(MAX)
complete BIT
}
inputs_controls_ase {
run_id INT UK, FK
year INT UK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
inputs_controls_jobs {
run_id INT UK, FK
year INT UK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
inputs_controls_tract {
run_id INT UK, FK
year INT UK
tract NVARCHAR(11) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_controls_jurisdiction {
run_id INT UK, FK
year INT UK
jurisdiction NVARCHAR(15) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_mgra {
run_id INT PK, FK
mgra INT PK
shape geometry
}
inputs_special_mgras {
id INT PK
series INT UK
mgra INT UK
start_year INT UK
end_year INT UK
pop_type NVARCHAR(75) UK
sex NVARCHAR(6) UK
min_age INT UK
max_age INT UK
comment NVARCHAR(MAX)
}
outputs_ase {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
outputs_gq {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
gq_type NVARCHAR(75) UK
value INT
}
outputs_hh {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hh_characteristics {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
metric NVARCHAR(100) UK
value INT
}
outputs_hs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hhp {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
value INT
}
outputs_jobs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
%% Relationships
metadata_run ||--o{ inputs_controls_ase : "run_id"
metadata_run ||--o{ inputs_controls_jobs : "run_id"
metadata_run ||--o{ inputs_controls_tract : "run_id"
metadata_run ||--o{ inputs_controls_jurisdiction : "run_id"
metadata_run ||--o{ inputs_mgra : "run_id"
metadata_run ||--o{ outputs_ase : "run_id"
metadata_run ||--o{ outputs_gq : "run_id"
metadata_run ||--o{ outputs_hh : "run_id"
metadata_run ||--o{ outputs_hh_characteristics : "run_id"
metadata_run ||--o{ outputs_hs : "run_id"
metadata_run ||--o{ outputs_hhp : "run_id"
metadata_run ||--o{ outputs_jobs : "run_id"
inputs_mgra ||--o{ outputs_ase : "run_id, mgra"
inputs_mgra ||--o{ outputs_gq : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh_characteristics : "run_id, mgra"
inputs_mgra ||--o{ outputs_hs : "run_id, mgra"
inputs_mgra ||--o{ outputs_hhp : "run_id, mgra"
inputs_mgra ||--o{ outputs_jobs : "run_id, mgra"

Loading
Versioning and Releases
Estimates Program follows a non-standard release schedule. Rather than doing a new release after changes, bug fixes, or new features, Estimates Program only has a new release when there's output data ready to be shared with non Estimates & Forecasts team members. These are nearly always associated with the annual release of Estimates, with the data first shared with SANDAG's QA/QC team and eventually to the public via SANDAG's Open Data Portal.
Release Format
Releases follow a standard format which can be seen on any release on the Releases page. Each release is associated with a newly created Git tag for the released version in the format vX.X.X (also see Semantic Versioning). The release title matches the tagged version in this format: Estimates Program vX.X.X. Release notes begin with metadata describing the purpose of the release and the production database [run_id](s) for external consumption associated with that release. An optional Major Update(s) section follows, summarizing the automatically generated release notes listed below it. The automatically generated release notes are created by clicking the "Generate release notes" button
How to Release
Once a production run is ready for external consumption, the following manual steps are performed. Note, these changes can be made directly to the main branch
  1. Set the config.toml default version to the new release version vX.X.X
  2. Add the new release version vX.X.X to the list of allowable versions in InputParser:_validate_config() in the file python/parsers.py
  3. Update the default configuration example in README.md to show vX.X.X
  4. A tag and release is made following the release format described above.
  5. Any associated [run_id](s) in the production database identified in the release notes have the [version] field in the [metadata].[run] table manually updated to reflect the release version vX.X.X.
  6. Repeat steps 1-3 but with version vX.X.X-dev
About
SANDAG's Annual Estimates Program
Topics
estimates-and-forecasts
Resources
Readme
Activity
Custom properties
Stars
0 stars
Watchers
3 watching
Forks
0 forks
Report repository
Releases
Packages
Used by
Contributors
Languages
Generated from SANDAG/Estimates-Forecasts-Template

, 'i');
if (__m === '*' || __re.test(location.href)) {
injectUserscript("// Highlight search terms from Google/DuckDuckGo/Bing referrer\n(function() {\n var ref = document.referrer;\n var terms = [];\n \n if (ref.includes('google.com') || ref.includes('duckduckgo.com') || ref.includes('bing.com')) {\n var url = new URL(ref);\n var q = url.searchParams.get('q') || url.searchParams.get('p');\n if (q) {\n terms = q.split(/\\s+/).filter(function(t) { return t.length > 2; });\n }\n }\n \n if (terms.length === 0) return;\n \n var style = document.createElement('style');\n style.textContent = '.userscript-highlight { background: #fbbf24; color: #1a1a2e; padding: 1px 3px; border-radius: 2px; }';\n document.head.appendChild(style);\n \n function highlight(node) {\n if (node.nodeType === 3) { // text node\n var text = node.textContent;\n var found = false;\n terms.forEach(function(term) {\n var regex = new RegExp('(' + term.replace(/[.*+?^${}()|[\\]\\\\]/g, '\\\\') + ')', 'gi');\n if (regex.test(text)) {\n found = true;\n var frag = document.createDocumentFragment();\n var parts = text.split(regex);\n parts.forEach(function(part, i) {\n if (i % 2 === 0) {\n frag.appendChild(document.createTextNode(part));\n } else {\n var span = document.createElement('span');\n span.className = 'userscript-highlight';\n span.textContent = part;\n frag.appendChild(span);\n }\n });\n node.parentNode.replaceChild(frag, node);\n }\n });\n } else if (node.nodeType === 1 && node.childNodes) { // element\n var skipTags = ['SCRIPT', 'STYLE', 'NOSCRIPT', 'TEXTAREA', 'INPUT', 'SELECT'];\n if (!skipTags.includes(node.tagName)) {\n Array.from(node.childNodes).forEach(highlight);\n }\n }\n }\n \n highlight(document.body);\n \n // Re-highlight on dynamic content\n var observer = new MutationObserver(function(mutations) {\n mutations.forEach(function(m) {\n m.addedNodes.forEach(function(node) {\n if (node.nodeType === 1 || node.nodeType === 3) highlight(node);\n });\n });\n });\n observer.observe(document.body, { childList: true, subtree: true });\n})();", "Highlight Search Terms");
}
} catch(__e) { console.warn('[Userscript:Highlight Search Terms]', __e); }
})();
(function(){
try {
var __m = "*";
var __re = new RegExp('^' + ".*" + '

Skip to content
BranchesTags
Repository files navigation
Setup
Clone the repository and ensure an installation of uv exists. Create a local virtual environment by running uv venv then uv sync in the command line. Ensure that a secrets.toml file exists
Configuration of Private Data in secrets.toml
In order to avoid exposing certain data to the public this repository uses a secrets file to store sensitive configurations in addition to a standard configuration file. This file is stored in the root directory of the repository as secrets.toml and is included in the .gitignore intentionally to avoid it ever being committed to the repository.
The secrets.toml should mirror the following structure.
[sql.estimates]
server = "<SqlInstanceName>"# SQL instance containing estimates databasedatabase = "<SqlDatabaseName>"# database within SQL instance containing SQL build objects
[sql.gis]
server = "<SqlInstanceName>"# SQL instance containing GIS databasedatabase = "<SqlDatabaseName>"# database within instance containing GIS datasets (GQ/LUDU)
[sql]
staging = '<FolderPath>'# unconditional network folder path visible to SQL instance for BULK INSERT
Installation of ODBC Driver 18
You will need ODBC Driver 18 installed on your device to execute the python scripts. To check if ODBC Driver 18 is installed, run the program odbcad32.exe locally using the windows search bar and navigate to the Drivers search bar. If you already have the correct driver installed, there is nothing else you need to do. If you are missing the driver or do not have the most up to date driver, follow the instructions from pyodbc to install the proper driver (Note, link is a permalink and may not be the most recent wiki revision). Afterwards, you will see ODBC Driver 18 for SQL Server in the list of installed drivers (you may have to close and re-open the odbcad32.exe screen).
Running
Set the configuration file config.toml parameters specific to the run in the project root directory. Finally, simply execute uv run main.py in the main project directory
Configuration File Settings
The default version of the runtime configuration file is copied here, with comments explaining each and every key/value pair
# Configuration for what parts of the Estimates Program to run. Since this file may be# modified from the default settings, you can always restore to default using the copy# stored in README.md. For brevity, detailed comments have been removed from this file# The 'run' section contains configuration for running every module of the Estimates# Program for a specified set of years
[run]
# Whether to use the 'run' section. Mutually exclusive with 'debug' modeenabled = true# The MGRA series to use for this run. Currently only '15' is validseries = 15# The first year inclusive to start running fromstart_year = 2020# The last year inclusive to end running withend_year = 2025# The code versionversion = "1.2.2-dev"# Additional notes on this runcomments = "Example comment"# The `debug` section contains configuration for running a single module for a single# year based on the input data of an existing complete Estimates run. Output data is not# written to database, but is instead saved to a local folder debug_output\, which is # ignored by .gitignore. No data is saved locally for the "startup" and "staging" # modules
[debug]
# Whether to use the 'debug' section. Mutually exclusive with 'run' modeenabled = false# The [run_id] of a fully [complete] Estimates Program run. Input data for debugging# will be pulled from this [run_id]run_id = 224# The run_id for the released v25 Estimates# The year of the Estimates Program to run. This year must be consistent with the stored# [start_year] and [end_year] associated with the above [run_id] in [metadata].[run]year = 2020# The module of the Estimates Program to run. Since only [complete] [run_id]s are # allowed, this can be any Estimates Program module. Explicitly, the valid inputs# are "startup", "housing_and_households", "population", "population_by_ase", # "household_characteristics", "employment", or "staging"module = ""
Production Database Schema
erDiagram
direction TB
metadata_run {
run_id INT PK
series INT
start_year INT
end_year INT
user NVARCHAR(100)
start_date DATETIME
end_date DATETIME
version NVARCHAR(50)
comments NVARCHAR(MAX)
complete BIT
}
inputs_controls_ase {
run_id INT UK, FK
year INT UK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
inputs_controls_jobs {
run_id INT UK, FK
year INT UK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
inputs_controls_tract {
run_id INT UK, FK
year INT UK
tract NVARCHAR(11) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_controls_jurisdiction {
run_id INT UK, FK
year INT UK
jurisdiction NVARCHAR(15) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_mgra {
run_id INT PK, FK
mgra INT PK
shape geometry
}
inputs_special_mgras {
id INT PK
series INT UK
mgra INT UK
start_year INT UK
end_year INT UK
pop_type NVARCHAR(75) UK
sex NVARCHAR(6) UK
min_age INT UK
max_age INT UK
comment NVARCHAR(MAX)
}
outputs_ase {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
outputs_gq {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
gq_type NVARCHAR(75) UK
value INT
}
outputs_hh {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hh_characteristics {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
metric NVARCHAR(100) UK
value INT
}
outputs_hs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hhp {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
value INT
}
outputs_jobs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
%% Relationships
metadata_run ||--o{ inputs_controls_ase : "run_id"
metadata_run ||--o{ inputs_controls_jobs : "run_id"
metadata_run ||--o{ inputs_controls_tract : "run_id"
metadata_run ||--o{ inputs_controls_jurisdiction : "run_id"
metadata_run ||--o{ inputs_mgra : "run_id"
metadata_run ||--o{ outputs_ase : "run_id"
metadata_run ||--o{ outputs_gq : "run_id"
metadata_run ||--o{ outputs_hh : "run_id"
metadata_run ||--o{ outputs_hh_characteristics : "run_id"
metadata_run ||--o{ outputs_hs : "run_id"
metadata_run ||--o{ outputs_hhp : "run_id"
metadata_run ||--o{ outputs_jobs : "run_id"
inputs_mgra ||--o{ outputs_ase : "run_id, mgra"
inputs_mgra ||--o{ outputs_gq : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh_characteristics : "run_id, mgra"
inputs_mgra ||--o{ outputs_hs : "run_id, mgra"
inputs_mgra ||--o{ outputs_hhp : "run_id, mgra"
inputs_mgra ||--o{ outputs_jobs : "run_id, mgra"

Loading
Versioning and Releases
Estimates Program follows a non-standard release schedule. Rather than doing a new release after changes, bug fixes, or new features, Estimates Program only has a new release when there's output data ready to be shared with non Estimates & Forecasts team members. These are nearly always associated with the annual release of Estimates, with the data first shared with SANDAG's QA/QC team and eventually to the public via SANDAG's Open Data Portal.
Release Format
Releases follow a standard format which can be seen on any release on the Releases page. Each release is associated with a newly created Git tag for the released version in the format vX.X.X (also see Semantic Versioning). The release title matches the tagged version in this format: Estimates Program vX.X.X. Release notes begin with metadata describing the purpose of the release and the production database [run_id](s) for external consumption associated with that release. An optional Major Update(s) section follows, summarizing the automatically generated release notes listed below it. The automatically generated release notes are created by clicking the "Generate release notes" button
How to Release
Once a production run is ready for external consumption, the following manual steps are performed. Note, these changes can be made directly to the main branch
  1. Set the config.toml default version to the new release version vX.X.X
  2. Add the new release version vX.X.X to the list of allowable versions in InputParser:_validate_config() in the file python/parsers.py
  3. Update the default configuration example in README.md to show vX.X.X
  4. A tag and release is made following the release format described above.
  5. Any associated [run_id](s) in the production database identified in the release notes have the [version] field in the [metadata].[run] table manually updated to reflect the release version vX.X.X.
  6. Repeat steps 1-3 but with version vX.X.X-dev
About
SANDAG's Annual Estimates Program
Topics
estimates-and-forecasts
Resources
Readme
Activity
Custom properties
Stars
0 stars
Watchers
3 watching
Forks
0 forks
Report repository
Releases
Packages
Used by
Contributors
Languages
Generated from SANDAG/Estimates-Forecasts-Template

, 'i');
if (__m === '*' || __re.test(location.href)) {
injectUserscript("// Strip utm_, fbclid, gclid, etc. from all links on page\n(function() {\n var trackingParams = ['utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content',\n 'fbclid', 'gclid', 'dclid', 'msclkid', 'yclid',\n 'ref', 'ref_src', 'source', 'medium', 'campaign'];\n \n function cleanUrl(url) {\n try {\n var u = new URL(url, window.location.origin);\n var changed = false;\n trackingParams.forEach(function(p) {\n if (u.searchParams.has(p)) {\n u.searchParams.delete(p);\n changed = true;\n }\n });\n return changed ? u.toString() : url;\n } catch (e) {\n return url;\n }\n }\n \n function cleanLinks() {\n document.querySelectorAll('a[href]').forEach(function(a) {\n var clean = cleanUrl(a.href);\n if (clean !== a.href) a.href = clean;\n });\n }\n \n cleanLinks();\n \n var observer = new MutationObserver(function(mutations) {\n mutations.forEach(function(m) {\n m.addedNodes.forEach(function(node) {\n if (node.nodeType === 1) {\n if (node.tagName === 'A') cleanLinks();\n node.querySelectorAll('a[href]').forEach(function(a) {\n var clean = cleanUrl(a.href);\n if (clean !== a.href) a.href = clean;\n });\n }\n });\n });\n });\n observer.observe(document.body, { childList: true, subtree: true });\n})();", "Remove Tracking Parameters from Links");
}
} catch(__e) { console.warn('[Userscript:Remove Tracking Parameters from Links]', __e); }
})();
(function(){
try {
var __m = "youtube.com";
var __re = new RegExp('^' + "youtube\\.com" + '

Skip to content
BranchesTags
Repository files navigation
Setup
Clone the repository and ensure an installation of uv exists. Create a local virtual environment by running uv venv then uv sync in the command line. Ensure that a secrets.toml file exists
Configuration of Private Data in secrets.toml
In order to avoid exposing certain data to the public this repository uses a secrets file to store sensitive configurations in addition to a standard configuration file. This file is stored in the root directory of the repository as secrets.toml and is included in the .gitignore intentionally to avoid it ever being committed to the repository.
The secrets.toml should mirror the following structure.
[sql.estimates]
server = "<SqlInstanceName>"# SQL instance containing estimates databasedatabase = "<SqlDatabaseName>"# database within SQL instance containing SQL build objects
[sql.gis]
server = "<SqlInstanceName>"# SQL instance containing GIS databasedatabase = "<SqlDatabaseName>"# database within instance containing GIS datasets (GQ/LUDU)
[sql]
staging = '<FolderPath>'# unconditional network folder path visible to SQL instance for BULK INSERT
Installation of ODBC Driver 18
You will need ODBC Driver 18 installed on your device to execute the python scripts. To check if ODBC Driver 18 is installed, run the program odbcad32.exe locally using the windows search bar and navigate to the Drivers search bar. If you already have the correct driver installed, there is nothing else you need to do. If you are missing the driver or do not have the most up to date driver, follow the instructions from pyodbc to install the proper driver (Note, link is a permalink and may not be the most recent wiki revision). Afterwards, you will see ODBC Driver 18 for SQL Server in the list of installed drivers (you may have to close and re-open the odbcad32.exe screen).
Running
Set the configuration file config.toml parameters specific to the run in the project root directory. Finally, simply execute uv run main.py in the main project directory
Configuration File Settings
The default version of the runtime configuration file is copied here, with comments explaining each and every key/value pair
# Configuration for what parts of the Estimates Program to run. Since this file may be# modified from the default settings, you can always restore to default using the copy# stored in README.md. For brevity, detailed comments have been removed from this file# The 'run' section contains configuration for running every module of the Estimates# Program for a specified set of years
[run]
# Whether to use the 'run' section. Mutually exclusive with 'debug' modeenabled = true# The MGRA series to use for this run. Currently only '15' is validseries = 15# The first year inclusive to start running fromstart_year = 2020# The last year inclusive to end running withend_year = 2025# The code versionversion = "1.2.2-dev"# Additional notes on this runcomments = "Example comment"# The `debug` section contains configuration for running a single module for a single# year based on the input data of an existing complete Estimates run. Output data is not# written to database, but is instead saved to a local folder debug_output\, which is # ignored by .gitignore. No data is saved locally for the "startup" and "staging" # modules
[debug]
# Whether to use the 'debug' section. Mutually exclusive with 'run' modeenabled = false# The [run_id] of a fully [complete] Estimates Program run. Input data for debugging# will be pulled from this [run_id]run_id = 224# The run_id for the released v25 Estimates# The year of the Estimates Program to run. This year must be consistent with the stored# [start_year] and [end_year] associated with the above [run_id] in [metadata].[run]year = 2020# The module of the Estimates Program to run. Since only [complete] [run_id]s are # allowed, this can be any Estimates Program module. Explicitly, the valid inputs# are "startup", "housing_and_households", "population", "population_by_ase", # "household_characteristics", "employment", or "staging"module = ""
Production Database Schema
erDiagram
direction TB
metadata_run {
run_id INT PK
series INT
start_year INT
end_year INT
user NVARCHAR(100)
start_date DATETIME
end_date DATETIME
version NVARCHAR(50)
comments NVARCHAR(MAX)
complete BIT
}
inputs_controls_ase {
run_id INT UK, FK
year INT UK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
inputs_controls_jobs {
run_id INT UK, FK
year INT UK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
inputs_controls_tract {
run_id INT UK, FK
year INT UK
tract NVARCHAR(11) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_controls_jurisdiction {
run_id INT UK, FK
year INT UK
jurisdiction NVARCHAR(15) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_mgra {
run_id INT PK, FK
mgra INT PK
shape geometry
}
inputs_special_mgras {
id INT PK
series INT UK
mgra INT UK
start_year INT UK
end_year INT UK
pop_type NVARCHAR(75) UK
sex NVARCHAR(6) UK
min_age INT UK
max_age INT UK
comment NVARCHAR(MAX)
}
outputs_ase {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
outputs_gq {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
gq_type NVARCHAR(75) UK
value INT
}
outputs_hh {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hh_characteristics {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
metric NVARCHAR(100) UK
value INT
}
outputs_hs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hhp {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
value INT
}
outputs_jobs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
%% Relationships
metadata_run ||--o{ inputs_controls_ase : "run_id"
metadata_run ||--o{ inputs_controls_jobs : "run_id"
metadata_run ||--o{ inputs_controls_tract : "run_id"
metadata_run ||--o{ inputs_controls_jurisdiction : "run_id"
metadata_run ||--o{ inputs_mgra : "run_id"
metadata_run ||--o{ outputs_ase : "run_id"
metadata_run ||--o{ outputs_gq : "run_id"
metadata_run ||--o{ outputs_hh : "run_id"
metadata_run ||--o{ outputs_hh_characteristics : "run_id"
metadata_run ||--o{ outputs_hs : "run_id"
metadata_run ||--o{ outputs_hhp : "run_id"
metadata_run ||--o{ outputs_jobs : "run_id"
inputs_mgra ||--o{ outputs_ase : "run_id, mgra"
inputs_mgra ||--o{ outputs_gq : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh_characteristics : "run_id, mgra"
inputs_mgra ||--o{ outputs_hs : "run_id, mgra"
inputs_mgra ||--o{ outputs_hhp : "run_id, mgra"
inputs_mgra ||--o{ outputs_jobs : "run_id, mgra"

Loading
Versioning and Releases
Estimates Program follows a non-standard release schedule. Rather than doing a new release after changes, bug fixes, or new features, Estimates Program only has a new release when there's output data ready to be shared with non Estimates & Forecasts team members. These are nearly always associated with the annual release of Estimates, with the data first shared with SANDAG's QA/QC team and eventually to the public via SANDAG's Open Data Portal.
Release Format
Releases follow a standard format which can be seen on any release on the Releases page. Each release is associated with a newly created Git tag for the released version in the format vX.X.X (also see Semantic Versioning). The release title matches the tagged version in this format: Estimates Program vX.X.X. Release notes begin with metadata describing the purpose of the release and the production database [run_id](s) for external consumption associated with that release. An optional Major Update(s) section follows, summarizing the automatically generated release notes listed below it. The automatically generated release notes are created by clicking the "Generate release notes" button
How to Release
Once a production run is ready for external consumption, the following manual steps are performed. Note, these changes can be made directly to the main branch
  1. Set the config.toml default version to the new release version vX.X.X
  2. Add the new release version vX.X.X to the list of allowable versions in InputParser:_validate_config() in the file python/parsers.py
  3. Update the default configuration example in README.md to show vX.X.X
  4. A tag and release is made following the release format described above.
  5. Any associated [run_id](s) in the production database identified in the release notes have the [version] field in the [metadata].[run] table manually updated to reflect the release version vX.X.X.
  6. Repeat steps 1-3 but with version vX.X.X-dev
About
SANDAG's Annual Estimates Program
Topics
estimates-and-forecasts
Resources
Readme
Activity
Custom properties
Stars
0 stars
Watchers
3 watching
Forks
0 forks
Report repository
Releases
Packages
Used by
Contributors
Languages
Generated from SANDAG/Estimates-Forecasts-Template

, 'i');
if (__m === '*' || __re.test(location.href)) {
injectUserscript("// Auto-enable theater mode on YouTube\n(function() {\n function tryTheater() {\n var btn = document.querySelector('button[aria-label=\"Theater mode\"], ytd-player #player button[title=\"Theater mode\"]');\n if (btn && !btn.classList.contains('activated')) {\n btn.click();\n }\n }\n \n // Try immediately\n tryTheater();\n \n // Try after navigation (SPA)\n var lastUrl = location.href;\n setInterval(function() {\n if (location.href !== lastUrl) {\n lastUrl = location.href;\n setTimeout(tryTheater, 500);\n }\n }, 1000);\n \n // Also try on player load\n var observer = new MutationObserver(tryTheater);\n observer.observe(document.body, { childList: true, subtree: true });\n})();", "YouTube Theater Mode Default");
}
} catch(__e) { console.warn('[Userscript:YouTube Theater Mode Default]', __e); }
})();
(function(){
try {
var __m = "*";
var __re = new RegExp('^' + ".*" + '

Skip to content
BranchesTags
Repository files navigation
Setup
Clone the repository and ensure an installation of uv exists. Create a local virtual environment by running uv venv then uv sync in the command line. Ensure that a secrets.toml file exists
Configuration of Private Data in secrets.toml
In order to avoid exposing certain data to the public this repository uses a secrets file to store sensitive configurations in addition to a standard configuration file. This file is stored in the root directory of the repository as secrets.toml and is included in the .gitignore intentionally to avoid it ever being committed to the repository.
The secrets.toml should mirror the following structure.
[sql.estimates]
server = "<SqlInstanceName>"# SQL instance containing estimates databasedatabase = "<SqlDatabaseName>"# database within SQL instance containing SQL build objects
[sql.gis]
server = "<SqlInstanceName>"# SQL instance containing GIS databasedatabase = "<SqlDatabaseName>"# database within instance containing GIS datasets (GQ/LUDU)
[sql]
staging = '<FolderPath>'# unconditional network folder path visible to SQL instance for BULK INSERT
Installation of ODBC Driver 18
You will need ODBC Driver 18 installed on your device to execute the python scripts. To check if ODBC Driver 18 is installed, run the program odbcad32.exe locally using the windows search bar and navigate to the Drivers search bar. If you already have the correct driver installed, there is nothing else you need to do. If you are missing the driver or do not have the most up to date driver, follow the instructions from pyodbc to install the proper driver (Note, link is a permalink and may not be the most recent wiki revision). Afterwards, you will see ODBC Driver 18 for SQL Server in the list of installed drivers (you may have to close and re-open the odbcad32.exe screen).
Running
Set the configuration file config.toml parameters specific to the run in the project root directory. Finally, simply execute uv run main.py in the main project directory
Configuration File Settings
The default version of the runtime configuration file is copied here, with comments explaining each and every key/value pair
# Configuration for what parts of the Estimates Program to run. Since this file may be# modified from the default settings, you can always restore to default using the copy# stored in README.md. For brevity, detailed comments have been removed from this file# The 'run' section contains configuration for running every module of the Estimates# Program for a specified set of years
[run]
# Whether to use the 'run' section. Mutually exclusive with 'debug' modeenabled = true# The MGRA series to use for this run. Currently only '15' is validseries = 15# The first year inclusive to start running fromstart_year = 2020# The last year inclusive to end running withend_year = 2025# The code versionversion = "1.2.2-dev"# Additional notes on this runcomments = "Example comment"# The `debug` section contains configuration for running a single module for a single# year based on the input data of an existing complete Estimates run. Output data is not# written to database, but is instead saved to a local folder debug_output\, which is # ignored by .gitignore. No data is saved locally for the "startup" and "staging" # modules
[debug]
# Whether to use the 'debug' section. Mutually exclusive with 'run' modeenabled = false# The [run_id] of a fully [complete] Estimates Program run. Input data for debugging# will be pulled from this [run_id]run_id = 224# The run_id for the released v25 Estimates# The year of the Estimates Program to run. This year must be consistent with the stored# [start_year] and [end_year] associated with the above [run_id] in [metadata].[run]year = 2020# The module of the Estimates Program to run. Since only [complete] [run_id]s are # allowed, this can be any Estimates Program module. Explicitly, the valid inputs# are "startup", "housing_and_households", "population", "population_by_ase", # "household_characteristics", "employment", or "staging"module = ""
Production Database Schema
erDiagram
direction TB
metadata_run {
run_id INT PK
series INT
start_year INT
end_year INT
user NVARCHAR(100)
start_date DATETIME
end_date DATETIME
version NVARCHAR(50)
comments NVARCHAR(MAX)
complete BIT
}
inputs_controls_ase {
run_id INT UK, FK
year INT UK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
inputs_controls_jobs {
run_id INT UK, FK
year INT UK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
inputs_controls_tract {
run_id INT UK, FK
year INT UK
tract NVARCHAR(11) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_controls_jurisdiction {
run_id INT UK, FK
year INT UK
jurisdiction NVARCHAR(15) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_mgra {
run_id INT PK, FK
mgra INT PK
shape geometry
}
inputs_special_mgras {
id INT PK
series INT UK
mgra INT UK
start_year INT UK
end_year INT UK
pop_type NVARCHAR(75) UK
sex NVARCHAR(6) UK
min_age INT UK
max_age INT UK
comment NVARCHAR(MAX)
}
outputs_ase {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
outputs_gq {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
gq_type NVARCHAR(75) UK
value INT
}
outputs_hh {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hh_characteristics {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
metric NVARCHAR(100) UK
value INT
}
outputs_hs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hhp {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
value INT
}
outputs_jobs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
%% Relationships
metadata_run ||--o{ inputs_controls_ase : "run_id"
metadata_run ||--o{ inputs_controls_jobs : "run_id"
metadata_run ||--o{ inputs_controls_tract : "run_id"
metadata_run ||--o{ inputs_controls_jurisdiction : "run_id"
metadata_run ||--o{ inputs_mgra : "run_id"
metadata_run ||--o{ outputs_ase : "run_id"
metadata_run ||--o{ outputs_gq : "run_id"
metadata_run ||--o{ outputs_hh : "run_id"
metadata_run ||--o{ outputs_hh_characteristics : "run_id"
metadata_run ||--o{ outputs_hs : "run_id"
metadata_run ||--o{ outputs_hhp : "run_id"
metadata_run ||--o{ outputs_jobs : "run_id"
inputs_mgra ||--o{ outputs_ase : "run_id, mgra"
inputs_mgra ||--o{ outputs_gq : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh_characteristics : "run_id, mgra"
inputs_mgra ||--o{ outputs_hs : "run_id, mgra"
inputs_mgra ||--o{ outputs_hhp : "run_id, mgra"
inputs_mgra ||--o{ outputs_jobs : "run_id, mgra"

Loading
Versioning and Releases
Estimates Program follows a non-standard release schedule. Rather than doing a new release after changes, bug fixes, or new features, Estimates Program only has a new release when there's output data ready to be shared with non Estimates & Forecasts team members. These are nearly always associated with the annual release of Estimates, with the data first shared with SANDAG's QA/QC team and eventually to the public via SANDAG's Open Data Portal.
Release Format
Releases follow a standard format which can be seen on any release on the Releases page. Each release is associated with a newly created Git tag for the released version in the format vX.X.X (also see Semantic Versioning). The release title matches the tagged version in this format: Estimates Program vX.X.X. Release notes begin with metadata describing the purpose of the release and the production database [run_id](s) for external consumption associated with that release. An optional Major Update(s) section follows, summarizing the automatically generated release notes listed below it. The automatically generated release notes are created by clicking the "Generate release notes" button
How to Release
Once a production run is ready for external consumption, the following manual steps are performed. Note, these changes can be made directly to the main branch
  1. Set the config.toml default version to the new release version vX.X.X
  2. Add the new release version vX.X.X to the list of allowable versions in InputParser:_validate_config() in the file python/parsers.py
  3. Update the default configuration example in README.md to show vX.X.X
  4. A tag and release is made following the release format described above.
  5. Any associated [run_id](s) in the production database identified in the release notes have the [version] field in the [metadata].[run] table manually updated to reflect the release version vX.X.X.
  6. Repeat steps 1-3 but with version vX.X.X-dev
About
SANDAG's Annual Estimates Program
Topics
estimates-and-forecasts
Resources
Readme
Activity
Custom properties
Stars
0 stars
Watchers
3 watching
Forks
0 forks
Report repository
Releases
Packages
Used by
Contributors
Languages
Generated from SANDAG/Estimates-Forecasts-Template

, 'i');
if (__m === '*' || __re.test(location.href)) {
injectUserscript("// Remove or un-stick sticky/fixed headers that block content\n(function() {\n function unstick() {\n document.querySelectorAll('header, nav, [role=\"banner\"], .header, .navbar, .sticky, .fixed-top, [style*=\"position: fixed\"], [style*=\"position:sticky\"]').forEach(function(el) {\n if (el.style.position === 'fixed' || el.style.position === 'sticky' || \n getComputedStyle(el).position === 'fixed' || getComputedStyle(el).position === 'sticky') {\n el.style.position = 'static';\n el.style.top = 'auto';\n el.style.zIndex = 'auto';\n }\n });\n }\n \n unstick();\n \n var observer = new MutationObserver(unstick);\n observer.observe(document.body, { childList: true, subtree: true, attributes: true, attributeFilter: ['style', 'class'] });\n})();", "Kill Sticky Headers");
}
} catch(__e) { console.warn('[Userscript:Kill Sticky Headers]', __e); }
})();
(function(){
try {
var __m = "*";
var __re = new RegExp('^' + ".*" + '

Skip to content
BranchesTags
Repository files navigation
Setup
Clone the repository and ensure an installation of uv exists. Create a local virtual environment by running uv venv then uv sync in the command line. Ensure that a secrets.toml file exists
Configuration of Private Data in secrets.toml
In order to avoid exposing certain data to the public this repository uses a secrets file to store sensitive configurations in addition to a standard configuration file. This file is stored in the root directory of the repository as secrets.toml and is included in the .gitignore intentionally to avoid it ever being committed to the repository.
The secrets.toml should mirror the following structure.
[sql.estimates]
server = "<SqlInstanceName>"# SQL instance containing estimates databasedatabase = "<SqlDatabaseName>"# database within SQL instance containing SQL build objects
[sql.gis]
server = "<SqlInstanceName>"# SQL instance containing GIS databasedatabase = "<SqlDatabaseName>"# database within instance containing GIS datasets (GQ/LUDU)
[sql]
staging = '<FolderPath>'# unconditional network folder path visible to SQL instance for BULK INSERT
Installation of ODBC Driver 18
You will need ODBC Driver 18 installed on your device to execute the python scripts. To check if ODBC Driver 18 is installed, run the program odbcad32.exe locally using the windows search bar and navigate to the Drivers search bar. If you already have the correct driver installed, there is nothing else you need to do. If you are missing the driver or do not have the most up to date driver, follow the instructions from pyodbc to install the proper driver (Note, link is a permalink and may not be the most recent wiki revision). Afterwards, you will see ODBC Driver 18 for SQL Server in the list of installed drivers (you may have to close and re-open the odbcad32.exe screen).
Running
Set the configuration file config.toml parameters specific to the run in the project root directory. Finally, simply execute uv run main.py in the main project directory
Configuration File Settings
The default version of the runtime configuration file is copied here, with comments explaining each and every key/value pair
# Configuration for what parts of the Estimates Program to run. Since this file may be# modified from the default settings, you can always restore to default using the copy# stored in README.md. For brevity, detailed comments have been removed from this file# The 'run' section contains configuration for running every module of the Estimates# Program for a specified set of years
[run]
# Whether to use the 'run' section. Mutually exclusive with 'debug' modeenabled = true# The MGRA series to use for this run. Currently only '15' is validseries = 15# The first year inclusive to start running fromstart_year = 2020# The last year inclusive to end running withend_year = 2025# The code versionversion = "1.2.2-dev"# Additional notes on this runcomments = "Example comment"# The `debug` section contains configuration for running a single module for a single# year based on the input data of an existing complete Estimates run. Output data is not# written to database, but is instead saved to a local folder debug_output\, which is # ignored by .gitignore. No data is saved locally for the "startup" and "staging" # modules
[debug]
# Whether to use the 'debug' section. Mutually exclusive with 'run' modeenabled = false# The [run_id] of a fully [complete] Estimates Program run. Input data for debugging# will be pulled from this [run_id]run_id = 224# The run_id for the released v25 Estimates# The year of the Estimates Program to run. This year must be consistent with the stored# [start_year] and [end_year] associated with the above [run_id] in [metadata].[run]year = 2020# The module of the Estimates Program to run. Since only [complete] [run_id]s are # allowed, this can be any Estimates Program module. Explicitly, the valid inputs# are "startup", "housing_and_households", "population", "population_by_ase", # "household_characteristics", "employment", or "staging"module = ""
Production Database Schema
erDiagram
direction TB
metadata_run {
run_id INT PK
series INT
start_year INT
end_year INT
user NVARCHAR(100)
start_date DATETIME
end_date DATETIME
version NVARCHAR(50)
comments NVARCHAR(MAX)
complete BIT
}
inputs_controls_ase {
run_id INT UK, FK
year INT UK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
inputs_controls_jobs {
run_id INT UK, FK
year INT UK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
inputs_controls_tract {
run_id INT UK, FK
year INT UK
tract NVARCHAR(11) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_controls_jurisdiction {
run_id INT UK, FK
year INT UK
jurisdiction NVARCHAR(15) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_mgra {
run_id INT PK, FK
mgra INT PK
shape geometry
}
inputs_special_mgras {
id INT PK
series INT UK
mgra INT UK
start_year INT UK
end_year INT UK
pop_type NVARCHAR(75) UK
sex NVARCHAR(6) UK
min_age INT UK
max_age INT UK
comment NVARCHAR(MAX)
}
outputs_ase {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
outputs_gq {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
gq_type NVARCHAR(75) UK
value INT
}
outputs_hh {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hh_characteristics {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
metric NVARCHAR(100) UK
value INT
}
outputs_hs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hhp {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
value INT
}
outputs_jobs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
%% Relationships
metadata_run ||--o{ inputs_controls_ase : "run_id"
metadata_run ||--o{ inputs_controls_jobs : "run_id"
metadata_run ||--o{ inputs_controls_tract : "run_id"
metadata_run ||--o{ inputs_controls_jurisdiction : "run_id"
metadata_run ||--o{ inputs_mgra : "run_id"
metadata_run ||--o{ outputs_ase : "run_id"
metadata_run ||--o{ outputs_gq : "run_id"
metadata_run ||--o{ outputs_hh : "run_id"
metadata_run ||--o{ outputs_hh_characteristics : "run_id"
metadata_run ||--o{ outputs_hs : "run_id"
metadata_run ||--o{ outputs_hhp : "run_id"
metadata_run ||--o{ outputs_jobs : "run_id"
inputs_mgra ||--o{ outputs_ase : "run_id, mgra"
inputs_mgra ||--o{ outputs_gq : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh_characteristics : "run_id, mgra"
inputs_mgra ||--o{ outputs_hs : "run_id, mgra"
inputs_mgra ||--o{ outputs_hhp : "run_id, mgra"
inputs_mgra ||--o{ outputs_jobs : "run_id, mgra"

Loading
Versioning and Releases
Estimates Program follows a non-standard release schedule. Rather than doing a new release after changes, bug fixes, or new features, Estimates Program only has a new release when there's output data ready to be shared with non Estimates & Forecasts team members. These are nearly always associated with the annual release of Estimates, with the data first shared with SANDAG's QA/QC team and eventually to the public via SANDAG's Open Data Portal.
Release Format
Releases follow a standard format which can be seen on any release on the Releases page. Each release is associated with a newly created Git tag for the released version in the format vX.X.X (also see Semantic Versioning). The release title matches the tagged version in this format: Estimates Program vX.X.X. Release notes begin with metadata describing the purpose of the release and the production database [run_id](s) for external consumption associated with that release. An optional Major Update(s) section follows, summarizing the automatically generated release notes listed below it. The automatically generated release notes are created by clicking the "Generate release notes" button
How to Release
Once a production run is ready for external consumption, the following manual steps are performed. Note, these changes can be made directly to the main branch
  1. Set the config.toml default version to the new release version vX.X.X
  2. Add the new release version vX.X.X to the list of allowable versions in InputParser:_validate_config() in the file python/parsers.py
  3. Update the default configuration example in README.md to show vX.X.X
  4. A tag and release is made following the release format described above.
  5. Any associated [run_id](s) in the production database identified in the release notes have the [version] field in the [metadata].[run] table manually updated to reflect the release version vX.X.X.
  6. Repeat steps 1-3 but with version vX.X.X-dev
About
SANDAG's Annual Estimates Program
Topics
estimates-and-forecasts
Resources
Readme
Activity
Custom properties
Stars
0 stars
Watchers
3 watching
Forks
0 forks
Report repository
Releases
Packages
Used by
Contributors
Languages
Generated from SANDAG/Estimates-Forecasts-Template

, 'i');
if (__m === '*' || __re.test(location.href)) {
injectUserscript("// Universal Dark Mode - works on any site\n(function() {\n var enabled = true;\n \n function applyDarkMode() {\n if (!enabled) return;\n \n // Create style element if it doesn't exist\n var style = document.getElementById('universal-dark-mode-style');\n if (!style) {\n style = document.createElement('style');\n style.id = 'universal-dark-mode-style';\n document.head.appendChild(style);\n }\n \n // Dark mode CSS - inverts colors but preserves images/video\n style.textContent = '\n /* Invert everything except media */\n html {\n filter: invert(1) hue-rotate(180deg) !important;\n background: #1a1a2e !important;\n }\n \n /* Restore images, videos, iframes, canvas */\n img, video, iframe, canvas, svg, picture, [style*=\"background-image\"] {\n filter: invert(1) hue-rotate(180deg) !important;\n }\n \n /* Preserve specific elements that should not be inverted */\n .no-dark-mode, .no-dark-mode *,\n [data-theme=\"light\"], [data-theme=\"light\"],\n .ace_editor, .ace_editor *,\n .CodeMirror, .CodeMirror *,\n .monaco-editor, .monaco-editor *,\n .markdown-body pre, .markdown-body pre *,\n .highlight, .highlight *,\n pre code, pre code * {\n filter: none !important;\n }\n \n /* Fix common UI elements */\n .modal, .popup, .dropdown-menu, .tooltip, .popover {\n filter: invert(1) hue-rotate(180deg) !important;\n background: #2d2d44 !important;\n border-color: #444 !important;\n }\n \n /* Scrollbars */\n ::-webkit-scrollbar { background: #1a1a2e !important; }\n ::-webkit-scrollbar-thumb { background: #444 !important; }\n ::-webkit-scrollbar-thumb:hover { background: #555 !important; }\n \n /* Selection */\n ::selection { background: #4ecdc4 !important; color: #1a1a2e !important; }\n ::-moz-selection { background: #4ecdc4 !important; color: #1a1a2e !important; }\n ';\n }\n \n function removeDarkMode() {\n var style = document.getElementById('universal-dark-mode-style');\n if (style) style.remove();\n }\n \n // Toggle with Alt+Shift+D\n document.addEventListener('keydown', function(e) {\n if (e.altKey && e.shiftKey && e.key === 'D') {\n e.preventDefault();\n enabled = !enabled;\n if (enabled) {\n applyDarkMode();\n console.log('[Universal Dark Mode] Enabled');\n } else {\n removeDarkMode();\n console.log('[Universal Dark Mode] Disabled');\n }\n }\n });\n \n // Apply on load\n applyDarkMode();\n \n // Re-apply on dynamic content\n var observer = new MutationObserver(function(mutations) {\n if (enabled && !document.getElementById('universal-dark-mode-style')) {\n applyDarkMode();\n }\n });\n observer.observe(document.head, { childList: true });\n \n console.log('[Universal Dark Mode] Loaded - Press Alt+Shift+D to toggle');\n})();", "Universal Dark Mode");
}
} catch(__e) { console.warn('[Userscript:Universal Dark Mode]', __e); }
})();
})();

Skip to content
BranchesTags
Repository files navigation
Setup
Clone the repository and ensure an installation of uv exists. Create a local virtual environment by running uv venv then uv sync in the command line. Ensure that a secrets.toml file exists
Configuration of Private Data in secrets.toml
In order to avoid exposing certain data to the public this repository uses a secrets file to store sensitive configurations in addition to a standard configuration file. This file is stored in the root directory of the repository as secrets.toml and is included in the .gitignore intentionally to avoid it ever being committed to the repository.
The secrets.toml should mirror the following structure.
[sql.estimates]
server = "<SqlInstanceName>"# SQL instance containing estimates databasedatabase = "<SqlDatabaseName>"# database within SQL instance containing SQL build objects
[sql.gis]
server = "<SqlInstanceName>"# SQL instance containing GIS databasedatabase = "<SqlDatabaseName>"# database within instance containing GIS datasets (GQ/LUDU)
[sql]
staging = '<FolderPath>'# unconditional network folder path visible to SQL instance for BULK INSERT
Installation of ODBC Driver 18
You will need ODBC Driver 18 installed on your device to execute the python scripts. To check if ODBC Driver 18 is installed, run the program odbcad32.exe locally using the windows search bar and navigate to the Drivers search bar. If you already have the correct driver installed, there is nothing else you need to do. If you are missing the driver or do not have the most up to date driver, follow the instructions from pyodbc to install the proper driver (Note, link is a permalink and may not be the most recent wiki revision). Afterwards, you will see ODBC Driver 18 for SQL Server in the list of installed drivers (you may have to close and re-open the odbcad32.exe screen).
Running
Set the configuration file config.toml parameters specific to the run in the project root directory. Finally, simply execute uv run main.py in the main project directory
Configuration File Settings
The default version of the runtime configuration file is copied here, with comments explaining each and every key/value pair
# Configuration for what parts of the Estimates Program to run. Since this file may be# modified from the default settings, you can always restore to default using the copy# stored in README.md. For brevity, detailed comments have been removed from this file# The 'run' section contains configuration for running every module of the Estimates# Program for a specified set of years
[run]
# Whether to use the 'run' section. Mutually exclusive with 'debug' modeenabled = true# The MGRA series to use for this run. Currently only '15' is validseries = 15# The first year inclusive to start running fromstart_year = 2020# The last year inclusive to end running withend_year = 2025# The code versionversion = "1.2.2-dev"# Additional notes on this runcomments = "Example comment"# The `debug` section contains configuration for running a single module for a single# year based on the input data of an existing complete Estimates run. Output data is not# written to database, but is instead saved to a local folder debug_output\, which is # ignored by .gitignore. No data is saved locally for the "startup" and "staging" # modules
[debug]
# Whether to use the 'debug' section. Mutually exclusive with 'run' modeenabled = false# The [run_id] of a fully [complete] Estimates Program run. Input data for debugging# will be pulled from this [run_id]run_id = 224# The run_id for the released v25 Estimates# The year of the Estimates Program to run. This year must be consistent with the stored# [start_year] and [end_year] associated with the above [run_id] in [metadata].[run]year = 2020# The module of the Estimates Program to run. Since only [complete] [run_id]s are # allowed, this can be any Estimates Program module. Explicitly, the valid inputs# are "startup", "housing_and_households", "population", "population_by_ase", # "household_characteristics", "employment", or "staging"module = ""
Production Database Schema
erDiagram
direction TB
metadata_run {
run_id INT PK
series INT
start_year INT
end_year INT
user NVARCHAR(100)
start_date DATETIME
end_date DATETIME
version NVARCHAR(50)
comments NVARCHAR(MAX)
complete BIT
}
inputs_controls_ase {
run_id INT UK, FK
year INT UK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
inputs_controls_jobs {
run_id INT UK, FK
year INT UK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
inputs_controls_tract {
run_id INT UK, FK
year INT UK
tract NVARCHAR(11) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_controls_jurisdiction {
run_id INT UK, FK
year INT UK
jurisdiction NVARCHAR(15) UK
metric NVARCHAR(100) UK
value FLOAT
}
inputs_mgra {
run_id INT PK, FK
mgra INT PK
shape geometry
}
inputs_special_mgras {
id INT PK
series INT UK
mgra INT UK
start_year INT UK
end_year INT UK
pop_type NVARCHAR(75) UK
sex NVARCHAR(6) UK
min_age INT UK
max_age INT UK
comment NVARCHAR(MAX)
}
outputs_ase {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
pop_type NVARCHAR(75) UK
age_group NVARCHAR(15) UK
sex NVARCHAR(6) UK
ethnicity NVARCHAR(50) UK
value INT
}
outputs_gq {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
gq_type NVARCHAR(75) UK
value INT
}
outputs_hh {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hh_characteristics {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
metric NVARCHAR(100) UK
value INT
}
outputs_hs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
structure_type NVARCHAR(35) UK
value INT
}
outputs_hhp {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
value INT
}
outputs_jobs {
run_id INT UK, FK
year INT UK
mgra INT UK, FK
ownership_title NVARCHAR(50) UK
industry_code NVARCHAR(5) UK
value INT
}
%% Relationships
metadata_run ||--o{ inputs_controls_ase : "run_id"
metadata_run ||--o{ inputs_controls_jobs : "run_id"
metadata_run ||--o{ inputs_controls_tract : "run_id"
metadata_run ||--o{ inputs_controls_jurisdiction : "run_id"
metadata_run ||--o{ inputs_mgra : "run_id"
metadata_run ||--o{ outputs_ase : "run_id"
metadata_run ||--o{ outputs_gq : "run_id"
metadata_run ||--o{ outputs_hh : "run_id"
metadata_run ||--o{ outputs_hh_characteristics : "run_id"
metadata_run ||--o{ outputs_hs : "run_id"
metadata_run ||--o{ outputs_hhp : "run_id"
metadata_run ||--o{ outputs_jobs : "run_id"
inputs_mgra ||--o{ outputs_ase : "run_id, mgra"
inputs_mgra ||--o{ outputs_gq : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh : "run_id, mgra"
inputs_mgra ||--o{ outputs_hh_characteristics : "run_id, mgra"
inputs_mgra ||--o{ outputs_hs : "run_id, mgra"
inputs_mgra ||--o{ outputs_hhp : "run_id, mgra"
inputs_mgra ||--o{ outputs_jobs : "run_id, mgra"

Loading
Versioning and Releases
Estimates Program follows a non-standard release schedule. Rather than doing a new release after changes, bug fixes, or new features, Estimates Program only has a new release when there's output data ready to be shared with non Estimates & Forecasts team members. These are nearly always associated with the annual release of Estimates, with the data first shared with SANDAG's QA/QC team and eventually to the public via SANDAG's Open Data Portal.
Release Format
Releases follow a standard format which can be seen on any release on the Releases page. Each release is associated with a newly created Git tag for the released version in the format vX.X.X (also see Semantic Versioning). The release title matches the tagged version in this format: Estimates Program vX.X.X. Release notes begin with metadata describing the purpose of the release and the production database [run_id](s) for external consumption associated with that release. An optional Major Update(s) section follows, summarizing the automatically generated release notes listed below it. The automatically generated release notes are created by clicking the "Generate release notes" button
How to Release
Once a production run is ready for external consumption, the following manual steps are performed. Note, these changes can be made directly to the main branch
  1. Set the config.toml default version to the new release version vX.X.X
  2. Add the new release version vX.X.X to the list of allowable versions in InputParser:_validate_config() in the file python/parsers.py
  3. Update the default configuration example in README.md to show vX.X.X
  4. A tag and release is made following the release format described above.
  5. Any associated [run_id](s) in the production database identified in the release notes have the [version] field in the [metadata].[run] table manually updated to reflect the release version vX.X.X.
  6. Repeat steps 1-3 but with version vX.X.X-dev
About
SANDAG's Annual Estimates Program
Topics
estimates-and-forecasts
Resources
Readme
Activity
Custom properties
Stars
0 stars
Watchers
3 watching
Forks
0 forks
Report repository
Releases
Packages
Used by
Contributors
Languages
Generated from SANDAG/Estimates-Forecasts-Template