Codliver
In [2]:
Copied!
import geopypi.common as geopypi
urls = ["https://prd-tnm.s3.amazonaws.com/StagedProducts/Hydrography/NHD/State/GDB/NHD_H_District_of_Columbia_State_GDB.zip"]
geopypi.download_files(urls, out_dir='.', unzip=True)
import geopypi.common as geopypi
urls = ["https://prd-tnm.s3.amazonaws.com/StagedProducts/Hydrography/NHD/State/GDB/NHD_H_District_of_Columbia_State_GDB.zip"]
geopypi.download_files(urls, out_dir='.', unzip=True)
Downloading... From: https://prd-tnm.s3.amazonaws.com/StagedProducts/Hydrography/NHD/State/GDB/NHD_H_District_of_Columbia_State_GDB.zip To: d:\spring 24\software design\Geopypi\docs\examples\NHD_H_District_of_Columbia_State_GDB.zip 100%|██████████| 73.5M/73.5M [00:30<00:00, 2.37MB/s]
Extracting files...
In [3]:
Copied!
pip install lonboard
pip install lonboard
Requirement already satisfied: lonboard in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (0.8.0) Requirement already satisfied: anywidget<0.10.0,>=0.9.0 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from lonboard) (0.9.10) Requirement already satisfied: geopandas>=0.13 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from lonboard) (0.14.3) Requirement already satisfied: matplotlib<4.0,>=3.7 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from lonboard) (3.8.3) Requirement already satisfied: palettable<4.0.0,>=3.3.3 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from lonboard) (3.3.3) Requirement already satisfied: pandas<3,>=2 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from lonboard) (2.2.1) Requirement already satisfied: pyarrow>=14.0.1 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from lonboard) (15.0.2) Requirement already satisfied: shapely<3,>=2 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from lonboard) (2.0.3) Requirement already satisfied: typing-extensions<5.0.0,>=4.6.0 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from lonboard) (4.11.0) Requirement already satisfied: ipywidgets>=7.6.0 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from anywidget<0.10.0,>=0.9.0->lonboard) (8.1.2) Requirement already satisfied: psygnal>=0.8.1 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from anywidget<0.10.0,>=0.9.0->lonboard) (0.11.0) Requirement already satisfied: fiona>=1.8.21 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from geopandas>=0.13->lonboard) (1.9.6) Requirement already satisfied: packaging in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from geopandas>=0.13->lonboard) (23.2) Requirement already satisfied: pyproj>=3.3.0 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from geopandas>=0.13->lonboard) (3.6.1) Requirement already satisfied: contourpy>=1.0.1 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from matplotlib<4.0,>=3.7->lonboard) (1.2.0) Requirement already satisfied: cycler>=0.10 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from matplotlib<4.0,>=3.7->lonboard) (0.12.1) Requirement already satisfied: fonttools>=4.22.0 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from matplotlib<4.0,>=3.7->lonboard) (4.50.0) Requirement already satisfied: kiwisolver>=1.3.1 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from matplotlib<4.0,>=3.7->lonboard) (1.4.5) Requirement already satisfied: numpy<2,>=1.21 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from matplotlib<4.0,>=3.7->lonboard) (1.26.4) Requirement already satisfied: pillow>=8 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from matplotlib<4.0,>=3.7->lonboard) (10.3.0) Requirement already satisfied: pyparsing>=2.3.1 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from matplotlib<4.0,>=3.7->lonboard) (3.1.2) Requirement already satisfied: python-dateutil>=2.7 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from matplotlib<4.0,>=3.7->lonboard) (2.8.2) Requirement already satisfied: pytz>=2020.1 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from pandas<3,>=2->lonboard) (2024.1) Requirement already satisfied: tzdata>=2022.7 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from pandas<3,>=2->lonboard) (2024.1) Requirement already satisfied: attrs>=19.2.0 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from fiona>=1.8.21->geopandas>=0.13->lonboard) (23.2.0) Requirement already satisfied: certifi in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from fiona>=1.8.21->geopandas>=0.13->lonboard) (2024.2.2) Requirement already satisfied: click~=8.0 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from fiona>=1.8.21->geopandas>=0.13->lonboard) (8.1.7) Requirement already satisfied: click-plugins>=1.0 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from fiona>=1.8.21->geopandas>=0.13->lonboard) (1.1.1) Requirement already satisfied: cligj>=0.5 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from fiona>=1.8.21->geopandas>=0.13->lonboard) (0.7.2) Requirement already satisfied: six in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from fiona>=1.8.21->geopandas>=0.13->lonboard) (1.16.0) Requirement already satisfied: comm>=0.1.3 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (0.2.1) Requirement already satisfied: ipython>=6.1.0 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (8.21.0) Requirement already satisfied: traitlets>=4.3.1 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (5.14.1) Requirement already satisfied: widgetsnbextension~=4.0.10 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (4.0.10) Requirement already satisfied: jupyterlab-widgets~=3.0.10 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (3.0.10) Requirement already satisfied: colorama in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from click~=8.0->fiona>=1.8.21->geopandas>=0.13->lonboard) (0.4.6) Requirement already satisfied: decorator in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from ipython>=6.1.0->ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (5.1.1) Requirement already satisfied: jedi>=0.16 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from ipython>=6.1.0->ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (0.19.1) Requirement already satisfied: matplotlib-inline in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from ipython>=6.1.0->ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (0.1.6) Requirement already satisfied: prompt-toolkit<3.1.0,>=3.0.41 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from ipython>=6.1.0->ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (3.0.42) Requirement already satisfied: pygments>=2.4.0 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from ipython>=6.1.0->ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (2.17.2) Requirement already satisfied: stack-data in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from ipython>=6.1.0->ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (0.6.2) Requirement already satisfied: parso<0.9.0,>=0.8.3 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from jedi>=0.16->ipython>=6.1.0->ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (0.8.3) Requirement already satisfied: wcwidth in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from prompt-toolkit<3.1.0,>=3.0.41->ipython>=6.1.0->ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (0.2.13) Requirement already satisfied: executing>=1.2.0 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from stack-data->ipython>=6.1.0->ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (2.0.1) Requirement already satisfied: asttokens>=2.1.0 in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from stack-data->ipython>=6.1.0->ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (2.4.1) Requirement already satisfied: pure-eval in c:\users\shaki\anaconda3\envs\geo\lib\site-packages (from stack-data->ipython>=6.1.0->ipywidgets>=7.6.0->anywidget<0.10.0,>=0.9.0->lonboard) (0.2.2) Note: you may need to restart the kernel to use updated packages.
In [2]:
Copied!
import geopypi.common as neo
import geopypi
import lonboard
state = "DC" # Change to the US State of your choice
url = f"https://data.source.coop/giswqs/nwi/wetlands/{state}_Wetlands.parquet"
gdf = neo.read_parquet(url, return_type='gdf', src_crs='EPSG:5070', dst_crs='EPSG:4326')
geopypi.view_vector(gdf, get_fill_color=[0, 0, 255, 128])
import geopypi.common as neo
import geopypi
import lonboard
state = "DC" # Change to the US State of your choice
url = f"https://data.source.coop/giswqs/nwi/wetlands/{state}_Wetlands.parquet"
gdf = neo.read_parquet(url, return_type='gdf', src_crs='EPSG:5070', dst_crs='EPSG:4326')
geopypi.view_vector(gdf, get_fill_color=[0, 0, 255, 128])
name 'convert_coordinates' is not defined
Out[2]:
Map(layers=[SolidPolygonLayer(get_fill_color=[0, 0, 255, 128], table=pyarrow.Table ATTRIBUTE: string WETLAND_T…
In [3]:
Copied!
import duckdb
import plotly
con = duckdb.connect()
con.install_extension("spatial")
con.load_extension("spatial")
import duckdb
import plotly
con = duckdb.connect()
con.install_extension("spatial")
con.load_extension("spatial")
In [6]:
Copied!
count_df = con.sql(
f"""
SELECT SUBSTRING(filename, LENGTH(filename) - 18, 2) AS State, COUNT(*) AS Count
FROM read_parquet('s3://us-west-2.opendata.source.coop/giswqs/nwi/wetlands/*.parquet', filename=true)
GROUP BY State
ORDER BY COUNT(*) DESC;
"""
).df()
count_df.head(10)
count_df = con.sql(
f"""
SELECT SUBSTRING(filename, LENGTH(filename) - 18, 2) AS State, COUNT(*) AS Count
FROM read_parquet('s3://us-west-2.opendata.source.coop/giswqs/nwi/wetlands/*.parquet', filename=true)
GROUP BY State
ORDER BY COUNT(*) DESC;
"""
).df()
count_df.head(10)
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Out[6]:
| State | Count | |
|---|---|---|
| 0 | MN | 2674597 |
| 1 | TX | 2263259 |
| 2 | ND | 2077099 |
| 3 | MT | 1857721 |
| 4 | WI | 1718865 |
| 5 | SD | 1664305 |
| 6 | AK | 1649192 |
| 7 | NE | 1516060 |
| 8 | MO | 1262141 |
| 9 | KS | 1063234 |
In [7]:
Copied!
con.sql("CREATE OR REPLACE TABLE wetlands AS FROM count_df")
con.sql("FROM wetlands")
con.sql("CREATE OR REPLACE TABLE wetlands AS FROM count_df")
con.sql("FROM wetlands")
Out[7]:
┌─────────┬─────────┐ │ State │ Count │ │ varchar │ int64 │ ├─────────┼─────────┤ │ MN │ 2674597 │ │ TX │ 2263259 │ │ ND │ 2077099 │ │ MT │ 1857721 │ │ WI │ 1718865 │ │ SD │ 1664305 │ │ AK │ 1649192 │ │ NE │ 1516060 │ │ MO │ 1262141 │ │ KS │ 1063234 │ │ · │ · │ │ · │ · │ │ · │ · │ │ NJ │ 248702 │ │ MD │ 240320 │ │ AZ │ 189606 │ │ CT │ 170964 │ │ VT │ 164400 │ │ WV │ 154348 │ │ DE │ 78597 │ │ RI │ 55229 │ │ HI │ 13566 │ │ DC │ 1555 │ ├─────────┴─────────┤ │ 51 rows │ │ (20 shown) │ └───────────────────┘
In [8]:
Copied!
url = "https://open.gishub.org/data/us/us_states.parquet"
con.sql(
f"""
CREATE OR REPLACE TABLE states AS
SELECT * EXCLUDE geometry, ST_GeomFromWKB(geometry)
AS geometry FROM '{url}'
"""
)
con.sql("FROM states")
url = "https://open.gishub.org/data/us/us_states.parquet"
con.sql(
f"""
CREATE OR REPLACE TABLE states AS
SELECT * EXCLUDE geometry, ST_GeomFromWKB(geometry)
AS geometry FROM '{url}'
"""
)
con.sql("FROM states")
Out[8]:
┌─────────┬───────────────┬────────────────────────────────────────────────────────────────────────────────────────────┐ │ id │ name │ geometry │ │ varchar │ varchar │ geometry │ ├─────────┼───────────────┼────────────────────────────────────────────────────────────────────────────────────────────┤ │ AL │ Alabama │ POLYGON ((-87.359296 35.00118, -85.606675 34.984749, -85.431413 34.124869, -85.184951 32… │ │ AK │ Alaska │ MULTIPOLYGON (((-131.602021 55.117982, -131.569159 55.28229, -131.355558 55.183705, -131… │ │ AZ │ Arizona │ POLYGON ((-109.042503 37.000263, -109.04798 31.331629, -111.074448 31.331629, -112.24651… │ │ AR │ Arkansas │ POLYGON ((-94.473842 36.501861, -90.152536 36.496384, -90.064905 36.304691, -90.218259 3… │ │ CA │ California │ POLYGON ((-123.233256 42.006186, -122.378853 42.011663, -121.037003 41.995232, -120.0018… │ │ CO │ Colorado │ POLYGON ((-107.919731 41.003906, -105.728954 40.998429, -104.053011 41.003906, -102.0539… │ │ CT │ Connecticut │ POLYGON ((-73.053528 42.039048, -71.799309 42.022617, -71.799309 42.006186, -71.799309 4… │ │ DE │ Delaware │ POLYGON ((-75.414089 39.804456, -75.507197 39.683964, -75.611259 39.61824, -75.589352 39… │ │ FL │ Florida │ POLYGON ((-85.497137 30.997536, -85.004212 31.003013, -84.867289 30.712735, -83.498053 3… │ │ GA │ Georgia │ POLYGON ((-83.109191 35.00118, -83.322791 34.787579, -83.339222 34.683517, -83.005129 34… │ │ · │ · │ · │ │ · │ · │ · │ │ · │ · │ · │ │ SD │ South Dakota │ POLYGON ((-104.047534 45.944106, -96.560556 45.933153, -96.582464 45.818137, -96.856311 … │ │ TN │ Tennessee │ POLYGON ((-88.054868 36.496384, -88.071299 36.677123, -87.852221 36.633308, -86.592525 3… │ │ TX │ Texas │ POLYGON ((-101.812942 36.501861, -100.000075 36.501861, -100.000075 34.563024, -99.92339… │ │ UT │ Utah │ POLYGON ((-112.164359 41.995232, -111.047063 42.000709, -111.047063 40.998429, -109.0479… │ │ VT │ Vermont │ POLYGON ((-71.503554 45.013027, -71.4926 44.914442, -71.629524 44.750133, -71.536416 44.… │ │ VA │ Virginia │ MULTIPOLYGON (((-75.397659 38.013497, -75.244304 38.029928, -75.375751 37.860142, -75.51… │ │ WA │ Washington │ MULTIPOLYGON (((-117.033359 49.000239, -117.044313 47.762451, -117.038836 46.426077, -11… │ │ WV │ West Virginia │ POLYGON ((-80.518598 40.636951, -80.518598 39.722302, -79.477979 39.722302, -79.488933 3… │ │ WI │ Wisconsin │ POLYGON ((-90.415429 46.568478, -90.229213 46.508231, -90.119674 46.338446, -89.09001 46… │ │ WY │ Wyoming │ POLYGON ((-109.080842 45.002073, -105.91517 45.002073, -104.058488 44.996596, -104.05301… │ ├─────────┴───────────────┴────────────────────────────────────────────────────────────────────────────────────────────┤ │ 50 rows (20 shown) 3 columns │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
In [9]:
Copied!
con.sql(
"""
SELECT * FROM states INNER JOIN wetlands ON states.id = wetlands.State
"""
)
con.sql(
"""
SELECT * FROM states INNER JOIN wetlands ON states.id = wetlands.State
"""
)
Out[9]:
┌─────────┬───────────────┬────────────────────────────────────────────────────────────────────────┬─────────┬─────────┐ │ id │ name │ geometry │ State │ Count │ │ varchar │ varchar │ geometry │ varchar │ int64 │ ├─────────┼───────────────┼────────────────────────────────────────────────────────────────────────┼─────────┼─────────┤ │ MN │ Minnesota │ POLYGON ((-92.014696 46.705401, -92.091373 46.749217, -92.29402 46.6… │ MN │ 2674597 │ │ TX │ Texas │ POLYGON ((-101.812942 36.501861, -100.000075 36.501861, -100.000075 … │ TX │ 2263259 │ │ ND │ North Dakota │ POLYGON ((-97.228743 49.000239, -97.097296 48.682577, -97.16302 48.5… │ ND │ 2077099 │ │ MT │ Montana │ POLYGON ((-104.047534 49.000239, -104.042057 47.861036, -104.047534 … │ MT │ 1857721 │ │ WI │ Wisconsin │ POLYGON ((-90.415429 46.568478, -90.229213 46.508231, -90.119674 46.… │ WI │ 1718865 │ │ SD │ South Dakota │ POLYGON ((-104.047534 45.944106, -96.560556 45.933153, -96.582464 45… │ SD │ 1664305 │ │ AK │ Alaska │ MULTIPOLYGON (((-131.602021 55.117982, -131.569159 55.28229, -131.35… │ AK │ 1649192 │ │ NE │ Nebraska │ POLYGON ((-103.324578 43.002989, -101.626726 42.997512, -98.499393 4… │ NE │ 1516060 │ │ MO │ Missouri │ POLYGON ((-91.833957 40.609566, -91.729895 40.615043, -91.527248 40.… │ MO │ 1262141 │ │ KS │ Kansas │ POLYGON ((-101.90605 40.001626, -95.306337 40.001626, -95.207752 39.… │ KS │ 1063234 │ │ · │ · │ · │ · │ · │ │ · │ · │ · │ · │ · │ │ · │ · │ · │ · │ · │ │ NJ │ New Jersey │ POLYGON ((-74.236547 41.14083, -73.902454 40.998429, -74.022947 40.7… │ NJ │ 248702 │ │ MD │ Maryland │ POLYGON ((-79.477979 39.722302, -75.786521 39.722302, -75.693413 38.… │ MD │ 240320 │ │ AZ │ Arizona │ POLYGON ((-109.042503 37.000263, -109.04798 31.331629, -111.074448 3… │ AZ │ 189606 │ │ CT │ Connecticut │ POLYGON ((-73.053528 42.039048, -71.799309 42.022617, -71.799309 42.… │ CT │ 170964 │ │ VT │ Vermont │ POLYGON ((-71.503554 45.013027, -71.4926 44.914442, -71.629524 44.75… │ VT │ 164400 │ │ WV │ West Virginia │ POLYGON ((-80.518598 40.636951, -80.518598 39.722302, -79.477979 39.… │ WV │ 154348 │ │ DE │ Delaware │ POLYGON ((-75.414089 39.804456, -75.507197 39.683964, -75.611259 39.… │ DE │ 78597 │ │ RI │ Rhode Island │ MULTIPOLYGON (((-71.196845 41.67757, -71.120168 41.496831, -71.31733… │ RI │ 55229 │ │ HI │ Hawaii │ MULTIPOLYGON (((-155.634835 18.948267, -155.881297 19.035898, -155.9… │ HI │ 13566 │ │ MS │ Mississippi │ POLYGON ((-88.471115 34.995703, -88.202745 34.995703, -88.098683 34.… │ MS │ 611768 │ ├─────────┴───────────────┴────────────────────────────────────────────────────────────────────────┴─────────┴─────────┤ │ 50 rows (20 shown) 5 columns │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
In [10]:
Copied!
df = con.sql(
"""
SELECT name, State, Count, ST_AsText(geometry) as geometry
FROM states INNER JOIN wetlands ON states.id = wetlands.State
"""
).df()
df.head()
df = con.sql(
"""
SELECT name, State, Count, ST_AsText(geometry) as geometry
FROM states INNER JOIN wetlands ON states.id = wetlands.State
"""
).df()
df.head()
Out[10]:
| name | State | Count | geometry | |
|---|---|---|---|---|
| 0 | Minnesota | MN | 2674597 | POLYGON ((-92.014696 46.705401, -92.091373 46.... |
| 1 | Texas | TX | 2263259 | POLYGON ((-101.812942 36.501861, -100.000075 3... |
| 2 | North Dakota | ND | 2077099 | POLYGON ((-97.228743 49.000239, -97.097296 48.... |
| 3 | Montana | MT | 1857721 | POLYGON ((-104.047534 49.000239, -104.042057 4... |
| 4 | Wisconsin | WI | 1718865 | POLYGON ((-90.415429 46.568478, -90.229213 46.... |
In [11]:
Copied!
import geopypi.common as geopypi
gdf = geopypi.df_to_gdf(df, src_crs="EPSG:4326")
import geopypi.common as geopypi
gdf = geopypi.df_to_gdf(df, src_crs="EPSG:4326")
In [12]:
Copied!
import geopypi
m = geopypi.Map()
m.add_data(
gdf, column='Count', scheme='Quantiles', cmap='Greens', legend_title='Wetland Count'
)
m
import geopypi
m = geopypi.Map()
m.add_data(
gdf, column='Count', scheme='Quantiles', cmap='Greens', legend_title='Wetland Count'
)
m
Out[12]:
Map(center=[20, 0], controls=(ZoomControl(options=['position', 'zoom_in_text', 'zoom_in_title', 'zoom_out_text…
In [14]:
Copied!
import geopypi.plot as geopypi
geopypi.pie_chart(count_df, 'State', 'Count', height=800, title='Number of Wetlands by State')
import geopypi.plot as geopypi
geopypi.pie_chart(count_df, 'State', 'Count', height=800, title='Number of Wetlands by State')
In [15]:
Copied!
geopypi.bar_chart(count_df, 'State', 'Count', title='Number of Wetlands by State')
geopypi.bar_chart(count_df, 'State', 'Count', title='Number of Wetlands by State')
In [16]:
Copied!
con.sql(f"""
SELECT SUM(Shape_Area) / 1000000 AS Area_SqKm
FROM 's3://us-west-2.opendata.source.coop/giswqs/nwi/wetlands/*.parquet'
""")
con.sql(f"""
SELECT SUM(Shape_Area) / 1000000 AS Area_SqKm
FROM 's3://us-west-2.opendata.source.coop/giswqs/nwi/wetlands/*.parquet'
""")
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Out[16]:
┌────────────────────┐ │ Area_SqKm │ │ double │ ├────────────────────┤ │ 1442876.9096808706 │ └────────────────────┘
In [17]:
Copied!
area_df = con.sql(f"""
SELECT SUBSTRING(filename, LENGTH(filename) - 18, 2) AS State, SUM(Shape_Area) / 1000000 AS Area_SqKm
FROM read_parquet('s3://us-west-2.opendata.source.coop/giswqs/nwi/wetlands/*.parquet', filename=true)
GROUP BY State
ORDER BY COUNT(*) DESC;
""").df()
area_df.head(10)
area_df = con.sql(f"""
SELECT SUBSTRING(filename, LENGTH(filename) - 18, 2) AS State, SUM(Shape_Area) / 1000000 AS Area_SqKm
FROM read_parquet('s3://us-west-2.opendata.source.coop/giswqs/nwi/wetlands/*.parquet', filename=true)
GROUP BY State
ORDER BY COUNT(*) DESC;
""").df()
area_df.head(10)
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Out[17]:
| State | Area_SqKm | |
|---|---|---|
| 0 | MN | 66376.803485 |
| 1 | TX | 43200.379897 |
| 2 | ND | 17586.222328 |
| 3 | MT | 13916.988281 |
| 4 | WI | 81781.779861 |
| 5 | SD | 14151.357901 |
| 6 | AK | 398576.219916 |
| 7 | NE | 7672.796780 |
| 8 | MO | 11283.318920 |
| 9 | KS | 5748.325735 |
In [18]:
Copied!
geopypi.bar_chart(area_df, 'State', 'Area_SqKm', title='Wetland Area by State')
geopypi.bar_chart(area_df, 'State', 'Area_SqKm', title='Wetland Area by State')