Office for Civic and Community Engagement, University of Miami, Institute for Data Science and Computing (2024). Miami-Dade Parcels Augmented for CAMP [gis dataset]. University of Miami Institute for Data Science and Computing. last updated: 0001-01-01 (update frequency: As Needed). A polygon feature class of property boundaries in Miami-Dade county sourced from the Miami-Dade Open Data Portal and then augmented with elevation data, flood zone data, qualified opportunity zone boolean, water overlap as boolean, managed areas (parks), intersections with major roads, and both county and municipal zoning information. All of the augmentation is based on spatial joines. Please see the ETL and provenance documentation for more information. Key words: Miami-Dade County, property, elevation, flood zones, municipal zoning, county zoning, qualified opportunity zones, land use, DOR codes Open Geospatial Consortium Standards: dynamic vector tiles | vector tiles | vector tile url: https://gdsc.idsc.miami.edu/vectortiles/data/mdc_parcels_camp/{z}/{x}/{y}.pbf | WMS (coming soon) | WFS (coming soon) Analytical Functions: gdsc_get_attributes(tablename=mdc_parcels_camp,lat=25.718,lng=-80.279,identifier=folio,normal='') ETL documentation

ETL for mdc_parcels_camp

######### # GDSC ETL is performed in two steps: shell script to ETL data into postgres # and then additional transformations with SQL in postGIS (if needed). #### ######### # Step 1 - bash script (pseudo code) #### # create directory structure and move into it mkdir -p /data/mdc_parcels_camp/{download,etl} && cd /data/mdc_parcels_camp # set update flag based on last update and update frequency do_update=0 if date() < last_update + update_frequency else do_update = 1 # load mdc_parcels_cce_vector_tiles as dependency (see mdc_parcels_cce_vector_tiles for details). # load mdc_cce_land_exclude_dor_codes as dependency (see mdc_cce_land_exclude_dor_codes for details). # load mdc_cce_land_parcel_filters as dependency (see mdc_cce_land_parcel_filters for details). # load mdc_cce_land_exclude_water as dependency (see mdc_cce_land_exclude_water for details). -- load extension for foreign table management into pod CREATE EXTENSION IF NOT EXISTS postgres_fdw; -- create centroids and spatial index for spatial joins to multipolygon SELECT AddGeometryColumn('public','mdc_parcels_camp','geom_centroid',4326,'POINT',2); UPDATE mdc_parcels_camp SET geom_centroid = ST_PointOnSurface(mdc_parcels_camp.geom); CREATE INDEX mdc_parcels_camp_geom_centroid_idx ON mdc_parcels_camp USING GIST(geom_centroid); COMMIT; NOTIFY pgrst, 'reload schema'; -- join mdc_cce_land_exclude_dor_codes to mdc_parcels_camp (column) CREATE SERVER gdsc_postgis_node_kpmpsxwnna0cr4ka FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-kpmpsxwnna0cr4ka', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_kpmpsxwnna0cr4ka OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_cce_land_exclude_dor_codes) FROM SERVER gdsc_postgis_node_kpmpsxwnna0cr4ka INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN IF NOT EXISTS exclude varchar; COMMIT; UPDATE mdc_parcels_camp SET exclude = mdc_cce_land_exclude_dor_codes.exclude FROM mdc_cce_land_exclude_dor_codes WHERE mdc_parcels_camp.dor_code_cur = mdc_cce_land_exclude_dor_codes.dor_code_cur; COMMIT; -- drop foreign server(??) -- join mdc_cce_land_parcel_filters to mdc_parcels_camp (column) CREATE SERVER gdsc_postgis_node_e258rek9acmutch5 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-e258rek9acmutch5', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_e258rek9acmutch5 OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_cce_land_parcel_filters) FROM SERVER gdsc_postgis_node_e258rek9acmutch5 INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN IF NOT EXISTS exclude varchar, ADD COLUMN IF NOT EXISTS include varchar; COMMIT; UPDATE mdc_parcels_camp SET exclude = mdc_cce_land_parcel_filters.exclude, include = mdc_cce_land_parcel_filters.include FROM mdc_cce_land_parcel_filters WHERE mdc_parcels_camp.folio = mdc_cce_land_parcel_filters.folio; COMMIT; -- drop foreign server(??) -- join mdc_major_roads to mdc_parcels_camp (intersects) CREATE SERVER gdsc_postgis_node_bgorngopq01xs8x7 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-bgorngopq01xs8x7', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_bgorngopq01xs8x7 OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_major_roads) FROM SERVER gdsc_postgis_node_bgorngopq01xs8x7 INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN IF NOT EXISTS mdc_road_id numeric, ADD COLUMN IF NOT EXISTS mdc_road_type varchar; COMMIT; UPDATE mdc_parcels_camp SET mdc_road_id=mdc_major_roads.streetid, mdc_road_type=mdc_major_roads.class FROM mdc_major_roads WHERE ST_Intersects(mdc_parcels_camp.geom,mdc_major_roads.geom); COMMIT; -- drop foreign server(??) -- join mdc_streets to mdc_parcels_camp (intersects) CREATE SERVER gdsc_postgis_node_r96xb6p4fpgnqrmc FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-r96xb6p4fpgnqrmc', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_r96xb6p4fpgnqrmc OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_streets) FROM SERVER gdsc_postgis_node_r96xb6p4fpgnqrmc INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN IF NOT EXISTS mdc_street_id numeric, ADD COLUMN IF NOT EXISTS mdc_street_type varchar; COMMIT; UPDATE mdc_parcels_camp SET mdc_street_id=mdc_streets.streetid, mdc_street_type=mdc_streets.class FROM mdc_streets WHERE ST_Intersects(mdc_parcels_camp.geom,mdc_streets.geom); COMMIT; -- drop foreign server(??) -- join mdc_cce_land_exclude_water to mdc_parcels_camp (column) CREATE SERVER gdsc_postgis_node_z8cxsbpn29ldp2da FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-z8cxsbpn29ldp2da', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_z8cxsbpn29ldp2da OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_cce_land_exclude_water) FROM SERVER gdsc_postgis_node_z8cxsbpn29ldp2da INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN IF NOT EXISTS exclude varchar; COMMIT; UPDATE mdc_parcels_camp SET exclude = mdc_cce_land_exclude_water.exclude FROM mdc_cce_land_exclude_water WHERE mdc_parcels_camp.folio = mdc_cce_land_exclude_water.folio; COMMIT; -- drop foreign server(??) -- join mdc_urban_growth_boundary to mdc_parcels_camp (within) CREATE SERVER gdsc_postgis_node_j7e9mssesuuy52uk FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-j7e9mssesuuy52uk', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_j7e9mssesuuy52uk OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_urban_growth_boundary) FROM SERVER gdsc_postgis_node_j7e9mssesuuy52uk INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN IF NOT EXISTS mdc_urban_growth_boundary numeric; COMMIT; -- create centroids and spatial index for spatial joins to multipolygon SELECT AddGeometryColumn('public','mdc_parcels_camp','geom_local_centroid',2236,'POINT',2); UPDATE mdc_parcels_camp SET geom_local_centroid = ST_PointOnSurface(mdc_parcels_camp.geom_local); CREATE INDEX mdc_parcels_camp_geom_local_centroid_idx ON mdc_parcels_camp USING GIST(geom_local_centroid); COMMIT; NOTIFY pgrst, 'reload schema'; UPDATE mdc_parcels_camp SET mdc_urban_growth_boundary=mdc_urban_growth_boundary.objectid FROM mdc_urban_growth_boundary WHERE ST_Within(mdc_parcels_camp.geom_local_centroid,mdc_urban_growth_boundary.geom_local); COMMIT; -- drop foreign server(??) -- join mdc_municipal_park_boundaries to mdc_parcels_camp (intersects) CREATE SERVER gdsc_postgis_node_4cvtujs0aj472xcs FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-4cvtujs0aj472xcs', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_4cvtujs0aj472xcs OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_municipal_park_boundaries) FROM SERVER gdsc_postgis_node_4cvtujs0aj472xcs INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN IF NOT EXISTS exclude varchar; COMMIT; UPDATE mdc_parcels_camp SET exclude=mdc_municipal_park_boundaries.name FROM mdc_municipal_park_boundaries WHERE ST_Intersects(mdc_parcels_camp.geom_local,mdc_municipal_park_boundaries.geom_local); COMMIT; -- drop foreign server(??) -- join mdc_county_park_boundaries to mdc_parcels_camp (intersects) CREATE SERVER gdsc_postgis_node_tk6rz7sfnr4hf87o FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-tk6rz7sfnr4hf87o', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_tk6rz7sfnr4hf87o OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_county_park_boundaries) FROM SERVER gdsc_postgis_node_tk6rz7sfnr4hf87o INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN IF NOT EXISTS exclude varchar; COMMIT; UPDATE mdc_parcels_camp SET exclude=mdc_county_park_boundaries.name FROM mdc_county_park_boundaries WHERE ST_Intersects(mdc_parcels_camp.geom_local,mdc_county_park_boundaries.geom_local); COMMIT; -- drop foreign server(??) -- join mdc_state_and_national_parks to mdc_parcels_camp (intersects) CREATE SERVER gdsc_postgis_node_o9kemvyhuf6r94kq FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-o9kemvyhuf6r94kq', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_o9kemvyhuf6r94kq OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_state_and_national_parks) FROM SERVER gdsc_postgis_node_o9kemvyhuf6r94kq INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN IF NOT EXISTS exclude varchar; COMMIT; UPDATE mdc_parcels_camp SET exclude=mdc_state_and_national_parks.maname FROM mdc_state_and_national_parks WHERE ST_Intersects(mdc_parcels_camp.geom_local,mdc_state_and_national_parks.geom_local); COMMIT; -- drop foreign server(??) -- remove extension postgres_fwd (??) ALTER TABLE mdc_parcels_camp DROP COLUMN geom_centroid; ALTER TABLE mdc_parcels_camp DROP COLUMN geom_local_centroid; "# Move into corrrect directory and create derivative directory in data package on osgeo cd /data/mdc_parcels_camp/ mkdir -p derived # Create shapefile of table in derivate directory on osgeo ogr2ogr -f "ESRI Shapefile" -overwrite derived/mdc_parcels_camp.shp PG:"" mdc_parcels_camp # Create downloadable tarfile of shp in derivative directory on osgeo rm -f derived/mdc_parcels_camp.shp.tar.gz tar -czf derived/mdc_parcels_camp.shp.tar.gz meta_dcat_mdc_parcels_camp.json -C derived mdc_parcels_camp.shp mdc_parcels_camp.prj mdc_parcels_camp.shx mdc_parcels_camp.dbf # Move into correct directory cd /data/mdc_parcels_camp/