dadams/california-equity-git
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
california-equity-git/california_enviroscreen/enviroscreen_to_postgis.ipynb
dadams-AU 7e1c6020ba restart equity git
2024-09-28 22:47:56 -07:00

555 lines
19 KiB
Plaintext
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"## read california enviroscreen data gbd file and save to postgis\n",
"## 2024-09-22"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [],
"source": [
"## set directory\n",
"import os\n",
"os.chdir('/home/dadams/Repos/california_equity_git/')"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [],
"source": [
"# import libraries\n",
"import pandas as pd\n",
"import geopandas as gpd\n",
"from sqlalchemy import create_engine\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [],
"source": [
"import geopandas as gpd\n",
"\n",
"# Read in data\n",
"data = gpd.read_file('california_enviroscreen/calenviroscreen40gdb_F_2021.gdb')\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Tract</th>\n",
" <th>ZIP</th>\n",
" <th>Population</th>\n",
" <th>CIscore</th>\n",
" <th>CIscoreP</th>\n",
" <th>Ozone</th>\n",
" <th>Ozone_Pctl</th>\n",
" <th>PM2_5</th>\n",
" <th>PM2_5_Pctl</th>\n",
" <th>Diesel_PM</th>\n",
" <th>...</th>\n",
" <th>African_American</th>\n",
" <th>Native_American</th>\n",
" <th>Asian_American</th>\n",
" <th>Pacific_Islander</th>\n",
" <th>Other_Multiple</th>\n",
" <th>Shape_Length</th>\n",
" <th>Shape_Area</th>\n",
" <th>County</th>\n",
" <th>ApproxLoc</th>\n",
" <th>geometry</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>6.083002e+09</td>\n",
" <td>93454</td>\n",
" <td>4495</td>\n",
" <td>36.019653</td>\n",
" <td>69.162885</td>\n",
" <td>0.034190</td>\n",
" <td>10.566273</td>\n",
" <td>7.567724</td>\n",
" <td>10.031114</td>\n",
" <td>0.154573</td>\n",
" <td>...</td>\n",
" <td>0.4004</td>\n",
" <td>0.2670</td>\n",
" <td>8.2091</td>\n",
" <td>0.0</td>\n",
" <td>1.3126</td>\n",
" <td>6999.357622</td>\n",
" <td>2.847611e+06</td>\n",
" <td>Santa Barbara</td>\n",
" <td>Santa Maria</td>\n",
" <td>MULTIPOLYGON (((-39795.070 -341919.191, -38126...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>6.083002e+09</td>\n",
" <td>93455</td>\n",
" <td>13173</td>\n",
" <td>37.030667</td>\n",
" <td>70.637922</td>\n",
" <td>0.035217</td>\n",
" <td>11.561917</td>\n",
" <td>7.624775</td>\n",
" <td>10.454263</td>\n",
" <td>0.106088</td>\n",
" <td>...</td>\n",
" <td>2.5051</td>\n",
" <td>0.0000</td>\n",
" <td>4.6990</td>\n",
" <td>0.0</td>\n",
" <td>0.9489</td>\n",
" <td>19100.578003</td>\n",
" <td>1.635292e+07</td>\n",
" <td>Santa Barbara</td>\n",
" <td>Santa Maria</td>\n",
" <td>MULTIPOLYGON (((-39795.070 -341919.191, -39803...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>6.083002e+09</td>\n",
" <td>93454</td>\n",
" <td>2398</td>\n",
" <td>31.213140</td>\n",
" <td>61.069087</td>\n",
" <td>0.034190</td>\n",
" <td>10.566273</td>\n",
" <td>7.548835</td>\n",
" <td>9.931549</td>\n",
" <td>0.190474</td>\n",
" <td>...</td>\n",
" <td>0.9591</td>\n",
" <td>0.0000</td>\n",
" <td>0.5421</td>\n",
" <td>0.0</td>\n",
" <td>2.1685</td>\n",
" <td>4970.985933</td>\n",
" <td>1.352330e+06</td>\n",
" <td>Santa Barbara</td>\n",
" <td>Santa Maria</td>\n",
" <td>MULTIPOLYGON (((-38115.747 -341130.248, -38126...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>6.083002e+09</td>\n",
" <td>93455</td>\n",
" <td>4496</td>\n",
" <td>6.639331</td>\n",
" <td>5.988401</td>\n",
" <td>0.036244</td>\n",
" <td>13.615432</td>\n",
" <td>7.660570</td>\n",
" <td>10.653391</td>\n",
" <td>0.091384</td>\n",
" <td>...</td>\n",
" <td>0.9342</td>\n",
" <td>0.7117</td>\n",
" <td>3.6699</td>\n",
" <td>0.0</td>\n",
" <td>2.5356</td>\n",
" <td>6558.956142</td>\n",
" <td>2.417717e+06</td>\n",
" <td>Santa Barbara</td>\n",
" <td>Orcutt</td>\n",
" <td>MULTIPOLYGON (((-37341.662 -348530.437, -37252...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>6.083002e+09</td>\n",
" <td>93455</td>\n",
" <td>4008</td>\n",
" <td>14.022852</td>\n",
" <td>23.121533</td>\n",
" <td>0.036244</td>\n",
" <td>13.615432</td>\n",
" <td>7.663210</td>\n",
" <td>10.690728</td>\n",
" <td>0.050976</td>\n",
" <td>...</td>\n",
" <td>0.6986</td>\n",
" <td>1.4721</td>\n",
" <td>3.2685</td>\n",
" <td>0.0</td>\n",
" <td>1.3723</td>\n",
" <td>6570.368642</td>\n",
" <td>2.608422e+06</td>\n",
" <td>Santa Barbara</td>\n",
" <td>Orcutt</td>\n",
" <td>MULTIPOLYGON (((-39465.107 -348499.262, -38244...</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 68 columns</p>\n",
"</div>"
],
"text/plain": [
" Tract ZIP Population CIscore CIscoreP Ozone \\\n",
"0 6.083002e+09 93454 4495 36.019653 69.162885 0.034190 \n",
"1 6.083002e+09 93455 13173 37.030667 70.637922 0.035217 \n",
"2 6.083002e+09 93454 2398 31.213140 61.069087 0.034190 \n",
"3 6.083002e+09 93455 4496 6.639331 5.988401 0.036244 \n",
"4 6.083002e+09 93455 4008 14.022852 23.121533 0.036244 \n",
"\n",
" Ozone_Pctl PM2_5 PM2_5_Pctl Diesel_PM ... African_American \\\n",
"0 10.566273 7.567724 10.031114 0.154573 ... 0.4004 \n",
"1 11.561917 7.624775 10.454263 0.106088 ... 2.5051 \n",
"2 10.566273 7.548835 9.931549 0.190474 ... 0.9591 \n",
"3 13.615432 7.660570 10.653391 0.091384 ... 0.9342 \n",
"4 13.615432 7.663210 10.690728 0.050976 ... 0.6986 \n",
"\n",
" Native_American Asian_American Pacific_Islander Other_Multiple \\\n",
"0 0.2670 8.2091 0.0 1.3126 \n",
"1 0.0000 4.6990 0.0 0.9489 \n",
"2 0.0000 0.5421 0.0 2.1685 \n",
"3 0.7117 3.6699 0.0 2.5356 \n",
"4 1.4721 3.2685 0.0 1.3723 \n",
"\n",
" Shape_Length Shape_Area County ApproxLoc \\\n",
"0 6999.357622 2.847611e+06 Santa Barbara Santa Maria \n",
"1 19100.578003 1.635292e+07 Santa Barbara Santa Maria \n",
"2 4970.985933 1.352330e+06 Santa Barbara Santa Maria \n",
"3 6558.956142 2.417717e+06 Santa Barbara Orcutt \n",
"4 6570.368642 2.608422e+06 Santa Barbara Orcutt \n",
"\n",
" geometry \n",
"0 MULTIPOLYGON (((-39795.070 -341919.191, -38126... \n",
"1 MULTIPOLYGON (((-39795.070 -341919.191, -39803... \n",
"2 MULTIPOLYGON (((-38115.747 -341130.248, -38126... \n",
"3 MULTIPOLYGON (((-37341.662 -348530.437, -37252... \n",
"4 MULTIPOLYGON (((-39465.107 -348499.262, -38244... \n",
"\n",
"[5 rows x 68 columns]"
]
},
"execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# show data\n",
"data.head()\n"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [],
"source": [
"from sqlalchemy import create_engine\n",
"\n",
"# Connect to the database\n",
"engine = create_engine('postgresql://postgres:MandyLinkToby3@localhost:5432/calif_equity')\n",
"\n",
"# Save the GeoDataFrame as a PostGIS table\n",
"data.to_postgis('calenviroscreen', con=engine, if_exists='replace', index=False)\n"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Tract</th>\n",
" <th>ZIP</th>\n",
" <th>Population</th>\n",
" <th>CIscore</th>\n",
" <th>CIscoreP</th>\n",
" <th>Ozone</th>\n",
" <th>Ozone_Pctl</th>\n",
" <th>PM2_5</th>\n",
" <th>PM2_5_Pctl</th>\n",
" <th>Diesel_PM</th>\n",
" <th>...</th>\n",
" <th>African_American</th>\n",
" <th>Native_American</th>\n",
" <th>Asian_American</th>\n",
" <th>Pacific_Islander</th>\n",
" <th>Other_Multiple</th>\n",
" <th>Shape_Length</th>\n",
" <th>Shape_Area</th>\n",
" <th>County</th>\n",
" <th>ApproxLoc</th>\n",
" <th>geometry</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>6.083002e+09</td>\n",
" <td>93454</td>\n",
" <td>4495</td>\n",
" <td>36.019653</td>\n",
" <td>69.162885</td>\n",
" <td>0.034190</td>\n",
" <td>10.566273</td>\n",
" <td>7.567724</td>\n",
" <td>10.031114</td>\n",
" <td>0.154573</td>\n",
" <td>...</td>\n",
" <td>0.4004</td>\n",
" <td>0.2670</td>\n",
" <td>8.2091</td>\n",
" <td>0.0</td>\n",
" <td>1.3126</td>\n",
" <td>6999.357622</td>\n",
" <td>2.847611e+06</td>\n",
" <td>Santa Barbara</td>\n",
" <td>Santa Maria</td>\n",
" <td>0106000020EE0C00000100000001030000000100000009...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>6.083002e+09</td>\n",
" <td>93455</td>\n",
" <td>13173</td>\n",
" <td>37.030667</td>\n",
" <td>70.637922</td>\n",
" <td>0.035217</td>\n",
" <td>11.561917</td>\n",
" <td>7.624775</td>\n",
" <td>10.454263</td>\n",
" <td>0.106088</td>\n",
" <td>...</td>\n",
" <td>2.5051</td>\n",
" <td>0.0000</td>\n",
" <td>4.6990</td>\n",
" <td>0.0</td>\n",
" <td>0.9489</td>\n",
" <td>19100.578003</td>\n",
" <td>1.635292e+07</td>\n",
" <td>Santa Barbara</td>\n",
" <td>Santa Maria</td>\n",
" <td>0106000020EE0C00000100000001030000000100000013...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>6.083002e+09</td>\n",
" <td>93454</td>\n",
" <td>2398</td>\n",
" <td>31.213140</td>\n",
" <td>61.069087</td>\n",
" <td>0.034190</td>\n",
" <td>10.566273</td>\n",
" <td>7.548835</td>\n",
" <td>9.931549</td>\n",
" <td>0.190474</td>\n",
" <td>...</td>\n",
" <td>0.9591</td>\n",
" <td>0.0000</td>\n",
" <td>0.5421</td>\n",
" <td>0.0</td>\n",
" <td>2.1685</td>\n",
" <td>4970.985933</td>\n",
" <td>1.352330e+06</td>\n",
" <td>Santa Barbara</td>\n",
" <td>Santa Maria</td>\n",
" <td>0106000020EE0C00000100000001030000000100000006...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>6.083002e+09</td>\n",
" <td>93455</td>\n",
" <td>4496</td>\n",
" <td>6.639331</td>\n",
" <td>5.988401</td>\n",
" <td>0.036244</td>\n",
" <td>13.615432</td>\n",
" <td>7.660570</td>\n",
" <td>10.653391</td>\n",
" <td>0.091384</td>\n",
" <td>...</td>\n",
" <td>0.9342</td>\n",
" <td>0.7117</td>\n",
" <td>3.6699</td>\n",
" <td>0.0</td>\n",
" <td>2.5356</td>\n",
" <td>6558.956142</td>\n",
" <td>2.417717e+06</td>\n",
" <td>Santa Barbara</td>\n",
" <td>Orcutt</td>\n",
" <td>0106000020EE0C0000010000000103000000010000000D...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>6.083002e+09</td>\n",
" <td>93455</td>\n",
" <td>4008</td>\n",
" <td>14.022852</td>\n",
" <td>23.121533</td>\n",
" <td>0.036244</td>\n",
" <td>13.615432</td>\n",
" <td>7.663210</td>\n",
" <td>10.690728</td>\n",
" <td>0.050976</td>\n",
" <td>...</td>\n",
" <td>0.6986</td>\n",
" <td>1.4721</td>\n",
" <td>3.2685</td>\n",
" <td>0.0</td>\n",
" <td>1.3723</td>\n",
" <td>6570.368642</td>\n",
" <td>2.608422e+06</td>\n",
" <td>Santa Barbara</td>\n",
" <td>Orcutt</td>\n",
" <td>0106000020EE0C00000100000001030000000100000007...</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 68 columns</p>\n",
"</div>"
],
"text/plain": [
" Tract ZIP Population CIscore CIscoreP Ozone \\\n",
"0 6.083002e+09 93454 4495 36.019653 69.162885 0.034190 \n",
"1 6.083002e+09 93455 13173 37.030667 70.637922 0.035217 \n",
"2 6.083002e+09 93454 2398 31.213140 61.069087 0.034190 \n",
"3 6.083002e+09 93455 4496 6.639331 5.988401 0.036244 \n",
"4 6.083002e+09 93455 4008 14.022852 23.121533 0.036244 \n",
"\n",
" Ozone_Pctl PM2_5 PM2_5_Pctl Diesel_PM ... African_American \\\n",
"0 10.566273 7.567724 10.031114 0.154573 ... 0.4004 \n",
"1 11.561917 7.624775 10.454263 0.106088 ... 2.5051 \n",
"2 10.566273 7.548835 9.931549 0.190474 ... 0.9591 \n",
"3 13.615432 7.660570 10.653391 0.091384 ... 0.9342 \n",
"4 13.615432 7.663210 10.690728 0.050976 ... 0.6986 \n",
"\n",
" Native_American Asian_American Pacific_Islander Other_Multiple \\\n",
"0 0.2670 8.2091 0.0 1.3126 \n",
"1 0.0000 4.6990 0.0 0.9489 \n",
"2 0.0000 0.5421 0.0 2.1685 \n",
"3 0.7117 3.6699 0.0 2.5356 \n",
"4 1.4721 3.2685 0.0 1.3723 \n",
"\n",
" Shape_Length Shape_Area County ApproxLoc \\\n",
"0 6999.357622 2.847611e+06 Santa Barbara Santa Maria \n",
"1 19100.578003 1.635292e+07 Santa Barbara Santa Maria \n",
"2 4970.985933 1.352330e+06 Santa Barbara Santa Maria \n",
"3 6558.956142 2.417717e+06 Santa Barbara Orcutt \n",
"4 6570.368642 2.608422e+06 Santa Barbara Orcutt \n",
"\n",
" geometry \n",
"0 0106000020EE0C00000100000001030000000100000009... \n",
"1 0106000020EE0C00000100000001030000000100000013... \n",
"2 0106000020EE0C00000100000001030000000100000006... \n",
"3 0106000020EE0C0000010000000103000000010000000D... \n",
"4 0106000020EE0C00000100000001030000000100000007... \n",
"\n",
"[5 rows x 68 columns]"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# verify data\n",
"sql = 'SELECT * FROM calenviroscreen LIMIT 5;'\n",
"pd.read_sql(sql, engine)"
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {},
"outputs": [],
"source": [
"# close connection\n",
"engine.dispose()\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "spatial_modeling",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.9.19"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Reference in New Issue View Git Blame Copy Permalink