Office for Civic and Community Engagement, University of Miami, Institute for Data Science and Computing (2024). Miami-Dade Parcels Augmented CAMP/LAND/MAP Vector Tiles [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_cce_vector_tiles/{z}/{x}/{y}.pbf | WMS (coming soon) | WFS (coming soon) Analytical Functions: gdsc_get_attributes(tablename=mdc_parcels_cce_vector_tiles,lat=25.718,lng=-80.279,identifier=folio,normal='') ETL documentation

ETL for mdc_parcels_cce_vector_tiles

######### # 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_cce_vector_tiles/{download,etl} && cd /data/mdc_parcels_cce_vector_tiles # 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_property_boundary as dependency (see mdc_property_boundary for details). # load mdc_cce_distinct_dor_codes as dependency (see mdc_cce_distinct_dor_codes 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_cce_vector_tiles','geom_centroid',4326,'POINT',2); UPDATE mdc_parcels_cce_vector_tiles SET geom_centroid = ST_PointOnSurface(mdc_parcels_cce_vector_tiles.geom); CREATE INDEX mdc_parcels_cce_vector_tiles_geom_centroid_idx ON mdc_parcels_cce_vector_tiles USING GIST(geom_centroid); COMMIT; NOTIFY pgrst, 'reload schema'; -- join mdc_county_zoning to mdc_parcels_cce_vector_tiles (within) CREATE SERVER gdsc_postgis_node_9t8k17qs2hz96gkf FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-9t8k17qs2hz96gkf', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_9t8k17qs2hz96gkf OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_county_zoning) FROM SERVER gdsc_postgis_node_9t8k17qs2hz96gkf INTO public; ALTER TABLE mdc_parcels_cce_vector_tiles ADD COLUMN IF NOT EXISTS mdc_county_zone varchar, ADD COLUMN IF NOT EXISTS mdc_county_zone_desc varchar; COMMIT; UPDATE mdc_parcels_cce_vector_tiles SET mdc_county_zone=mdc_county_zoning.zone, mdc_county_zone_desc=mdc_county_zoning.zone_desc FROM mdc_county_zoning WHERE ST_Within(mdc_parcels_cce_vector_tiles.geom_centroid,mdc_county_zoning.geom); COMMIT; -- drop foreign server(??) -- join mdc_municipal_zoning to mdc_parcels_cce_vector_tiles (within) CREATE SERVER gdsc_postgis_node_4ze9w136wpktq8i2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-4ze9w136wpktq8i2', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_4ze9w136wpktq8i2 OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_municipal_zoning) FROM SERVER gdsc_postgis_node_4ze9w136wpktq8i2 INTO public; ALTER TABLE mdc_parcels_cce_vector_tiles ADD COLUMN IF NOT EXISTS mdc_municipal_zone varchar, ADD COLUMN IF NOT EXISTS mdc_municipal_zone_desc varchar; COMMIT; UPDATE mdc_parcels_cce_vector_tiles SET mdc_municipal_zone=mdc_municipal_zoning.zone, mdc_municipal_zone_desc=mdc_municipal_zoning.zonedesc FROM mdc_municipal_zoning WHERE ST_Within(mdc_parcels_cce_vector_tiles.geom_centroid,mdc_municipal_zoning.geom); COMMIT; -- drop foreign server(??) -- join mdc_flood_hazard to mdc_parcels_cce_vector_tiles (within) CREATE SERVER gdsc_postgis_node_hh0dt802i4uf29tg FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-hh0dt802i4uf29tg', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_hh0dt802i4uf29tg OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_flood_hazard) FROM SERVER gdsc_postgis_node_hh0dt802i4uf29tg INTO public; ALTER TABLE mdc_parcels_cce_vector_tiles ADD COLUMN IF NOT EXISTS fema_flood_zone varchar; COMMIT; UPDATE mdc_parcels_cce_vector_tiles SET fema_flood_zone=mdc_flood_hazard.fzone FROM mdc_flood_hazard WHERE ST_Within(mdc_parcels_cce_vector_tiles.geom_centroid,mdc_flood_hazard.geom); COMMIT; -- drop foreign server(??) -- join mdc_qualified_opportunity_zones to mdc_parcels_cce_vector_tiles (within) CREATE SERVER gdsc_postgis_node_oqghhpl7h1786lol FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-oqghhpl7h1786lol', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_oqghhpl7h1786lol OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_qualified_opportunity_zones) FROM SERVER gdsc_postgis_node_oqghhpl7h1786lol INTO public; ALTER TABLE mdc_parcels_cce_vector_tiles ADD COLUMN IF NOT EXISTS mdc_qualified_opportunity_zones boolean; COMMIT; UPDATE mdc_parcels_cce_vector_tiles SET mdc_qualified_opportunity_zones=true FROM mdc_qualified_opportunity_zones WHERE ST_Within(mdc_parcels_cce_vector_tiles.geom_centroid,mdc_qualified_opportunity_zones.geom); COMMIT; -- drop foreign server(??) -- join mdc_neighborhoods to mdc_parcels_cce_vector_tiles (within) CREATE SERVER gdsc_postgis_node_jiuoxqxujrhn7gwb FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-jiuoxqxujrhn7gwb', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_jiuoxqxujrhn7gwb OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_neighborhoods) FROM SERVER gdsc_postgis_node_jiuoxqxujrhn7gwb INTO public; ALTER TABLE mdc_parcels_cce_vector_tiles ADD COLUMN IF NOT EXISTS mdc_neighborhood_name varchar; COMMIT; UPDATE mdc_parcels_cce_vector_tiles SET mdc_neighborhood_name=mdc_neighborhoods.name FROM mdc_neighborhoods WHERE ST_Within(mdc_parcels_cce_vector_tiles.geom_centroid,mdc_neighborhoods.geom); COMMIT; -- drop foreign server(??) -- join mdc_municipal_boundary to mdc_parcels_cce_vector_tiles (within) CREATE SERVER gdsc_postgis_node_yhldbud3v73x0hfd FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-yhldbud3v73x0hfd', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_yhldbud3v73x0hfd OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_municipal_boundary) FROM SERVER gdsc_postgis_node_yhldbud3v73x0hfd INTO public; ALTER TABLE mdc_parcels_cce_vector_tiles ADD COLUMN IF NOT EXISTS mdc_municipal_boundary_name varchar; COMMIT; UPDATE mdc_parcels_cce_vector_tiles SET mdc_municipal_boundary_name=mdc_municipal_boundary.name FROM mdc_municipal_boundary WHERE ST_Within(mdc_parcels_cce_vector_tiles.geom_centroid,mdc_municipal_boundary.geom); COMMIT; -- drop foreign server(??) -- join com_neighborhoods to mdc_parcels_cce_vector_tiles (within) CREATE SERVER gdsc_postgis_node_t7ipfmv0ttyny32a FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-t7ipfmv0ttyny32a', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_t7ipfmv0ttyny32a OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (com_neighborhoods) FROM SERVER gdsc_postgis_node_t7ipfmv0ttyny32a INTO public; ALTER TABLE mdc_parcels_cce_vector_tiles ADD COLUMN IF NOT EXISTS com_neighborhood_name varchar; COMMIT; UPDATE mdc_parcels_cce_vector_tiles SET com_neighborhood_name=com_neighborhoods.label FROM com_neighborhoods WHERE ST_Within(mdc_parcels_cce_vector_tiles.geom_centroid,com_neighborhoods.geom); COMMIT; -- drop foreign server(??) -- join mdc_cce_distinct_dor_codes to mdc_parcels_cce_vector_tiles (column) CREATE SERVER gdsc_postgis_node_fbwoh3sdzqtgxo0d FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-fbwoh3sdzqtgxo0d', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_fbwoh3sdzqtgxo0d OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_cce_distinct_dor_codes) FROM SERVER gdsc_postgis_node_fbwoh3sdzqtgxo0d INTO public; ALTER TABLE mdc_parcels_cce_vector_tiles ADD COLUMN IF NOT EXISTS cce_land_use varchar; COMMIT; UPDATE mdc_parcels_cce_vector_tiles SET cce_land_use = mdc_cce_distinct_dor_codes.cce_land_use FROM mdc_cce_distinct_dor_codes WHERE mdc_parcels_cce_vector_tiles.dor_code_cur = mdc_cce_distinct_dor_codes.dor_code_cur; COMMIT; -- drop foreign server(??) -- join mdc_2015_dem_30ft to mdc_parcels_cce_vector_tiles (extract) CREATE SERVER gdsc_postgis_node_yd7lnwh2jcyvwktl FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'gdsc-postgis-node-yd7lnwh2jcyvwktl', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER gdsc_postgis_node_yd7lnwh2jcyvwktl OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_2015_dem_30ft) FROM SERVER gdsc_postgis_node_yd7lnwh2jcyvwktl INTO public; ALTER TABLE mdc_parcels_cce_vector_tiles ADD COLUMN IF NOT EXISTS elevation numeric; COMMIT; -- create centroids and spatial index for spatial joins to multipolygon SELECT AddGeometryColumn('public','mdc_parcels_cce_vector_tiles','geom_local_centroid',2236,'POINT',2); UPDATE mdc_parcels_cce_vector_tiles SET geom_local_centroid = ST_PointOnSurface(mdc_parcels_cce_vector_tiles.geom_local); CREATE INDEX mdc_parcels_cce_vector_tiles_geom_local_centroid_idx ON mdc_parcels_cce_vector_tiles USING GIST(geom_local_centroid); COMMIT; NOTIFY pgrst, 'reload schema'; UPDATE mdc_parcels_cce_vector_tiles SET elevation = ST_Value(rast,mdc_parcels_cce_vector_tiles.geom_local_centroid) FROM mdc_2015_dem_30ft WHERE ST_Intersects(mdc_parcels_cce_vector_tiles.geom_local_centroid, mdc_2015_dem_30ft.rast); COMMIT; -- drop foreign server(??) -- remove extension postgres_fwd (??) ALTER TABLE mdc_parcels_cce_vector_tiles DROP COLUMN geom_centroid; ALTER TABLE mdc_parcels_cce_vector_tiles DROP COLUMN geom_local_centroid; "# Move into corrrect directory and create derivative directory in data package on osgeo cd /data/mdc_parcels_cce_vector_tiles/ mkdir -p derived # Create geojson of table in derivate directory on osgeo ogr2ogr -f GeoJSON -overwrite derived/mdc_parcels_cce_vector_tiles.json PG:"" -select "folio,true_site_addr,true_site_unit,true_site_city,true_site_zip_code,dor_code_cur,dor_desc,subdivision,bedroom_count,bathroom_count,half_bathroom_count,floor_count,unit_count,building_actual_area,building_heated_area,lot_size,year_built,assessment_year_cur,assessed_val_cur,dos_1,price_1,mdc_county_zone,mdc_county_zone_desc,mdc_municipal_zone,mdc_municipal_zone_desc,fema_flood_zone,mdc_qualified_opportunity_zones,mdc_neighborhood_name,mdc_municipal_boundary_name,com_neighborhood_name,cce_land_use,elevation,geom" mdc_parcels_cce_vector_tiles # Create downloadable tarfile of geojson in derivative directory on osgeo rm -f derived/mdc_parcels_cce_vector_tiles.geojson.tar.gz tar -czf derived/mdc_parcels_cce_vector_tiles.geojson.tar.gz meta_dcat_mdc_parcels_cce_vector_tiles.json -C derived mdc_parcels_cce_vector_tiles.json # Move into correct directory cd /data/mdc_parcels_cce_vector_tiles/ # Create pg_dump of table SQL in derivate directory on postgis. pg_dump -h gdsc-postgis-node-nvg22uubdqh4v8d6 -t mdc_parcels_cce_vector_tiles > derived/mdc_parcels_cce_vector_tiles.sql # Create downloadable tarfile of SQL in derivative directory on postgis rm -f derived/mdc_parcels_cce_vector_tiles.sql.tar.gz tar -czf derived/mdc_parcels_cce_vector_tiles.sql.tar.gz meta_dcat_mdc_parcels_cce_vector_tiles.json -C derived mdc_parcels_cce_vector_tiles.sql