UM Geospatial Digital Special Collections

Citation: 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: 2024-10-03 (update frequency: As Needed). Abstract: 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,attrs=[objectid,ogc_fid,pid,folio,ttrrss,x_coord,y_coord,true_site_addr,true_site_unit,true_site_city,true_site_zip_code,true_mailing_addr1,true_mailing_addr2,true_mailing_addr3,true_mailing_city,true_mailing_state,true_mailing_zip_code,true_mailing_country,true_owner1,true_owner2,true_owner3,condo_flag,parent_folio,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,legal,shape_area,shape_length,mdc_county_zone,mdc_county_zone_desc,mdc_municipal_zone,mdc_municipal_zone_desc,mdc_municipal_park_name,mdc_county_park_name,fema_flood_zone,flma_name,flma_type,mdc_qualified_opportunity_zones,mdc_streetid,mdc_street_type,mdc_water_polygons,elevation],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 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_county_zoning to mdc_parcels_camp (within) CREATE SERVER postgis_e2ysqv7vtzx37qac FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-e2ysqv7vtzx37qac', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_e2ysqv7vtzx37qac OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_county_zoning) FROM SERVER postgis_e2ysqv7vtzx37qac INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN mdc_county_zone varchar, ADD COLUMN mdc_county_zone_desc varchar; COMMIT; UPDATE mdc_parcels_camp 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_camp.geom_centroid,mdc_county_zoning.geom); COMMIT; -- drop foreign server(??) -- join mdc_municipal_zoning to mdc_parcels_camp (within) CREATE SERVER postgis_xhqzmmz1wgd6d6sy FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-xhqzmmz1wgd6d6sy', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_xhqzmmz1wgd6d6sy OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_municipal_zoning) FROM SERVER postgis_xhqzmmz1wgd6d6sy INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN mdc_municipal_zone varchar, ADD COLUMN mdc_municipal_zone_desc varchar; COMMIT; UPDATE mdc_parcels_camp 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_camp.geom_centroid,mdc_municipal_zoning.geom); COMMIT; -- drop foreign server(??) -- join mdc_municipal_park_boundaries to mdc_parcels_camp (within) CREATE SERVER postgis_3la54wxabicz8vzm FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-3la54wxabicz8vzm', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_3la54wxabicz8vzm OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_municipal_park_boundaries) FROM SERVER postgis_3la54wxabicz8vzm INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN mdc_municipal_park_name varchar; 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_municipal_park_name = mdc_municipal_park_boundaries.name FROM mdc_municipal_park_boundaries WHERE ST_Within(mdc_parcels_camp.geom_local_centroid,mdc_municipal_park_boundaries.geom_local); COMMIT; -- drop foreign server(??) -- join mdc_county_park_boundaries to mdc_parcels_camp (within) CREATE SERVER postgis_am3kfvhfkh4bcfsi FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-am3kfvhfkh4bcfsi', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_am3kfvhfkh4bcfsi OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_county_park_boundaries) FROM SERVER postgis_am3kfvhfkh4bcfsi INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN mdc_county_park_name varchar; COMMIT; UPDATE mdc_parcels_camp SET mdc_county_park_name = mdc_county_park_boundaries.name FROM mdc_county_park_boundaries WHERE ST_Within(mdc_parcels_camp.geom_local_centroid,mdc_county_park_boundaries.geom_local); COMMIT; -- drop foreign server(??) -- join fl_2023_flma to mdc_parcels_camp (within) CREATE SERVER postgis_vq9e663z4cjbv2iw FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-vq9e663z4cjbv2iw', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_vq9e663z4cjbv2iw OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (fl_2023_flma) FROM SERVER postgis_vq9e663z4cjbv2iw INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN fl_protected_area_name varchar, ADD COLUMN fl_protected_area_type varchar; COMMIT; UPDATE mdc_parcels_camp SET fl_protected_area_name = fl_2023_flma.maname, fl_protected_area_type = fl_2023_flma.matype FROM fl_2023_flma WHERE ST_Within(mdc_parcels_camp.geom_local_centroid,fl_2023_flma.geom_local); COMMIT; -- drop foreign server(??) -- join mdc_flood_hazard to mdc_parcels_camp (within) CREATE SERVER postgis_gaxfbdigubom2fwo FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-gaxfbdigubom2fwo', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_gaxfbdigubom2fwo OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_flood_hazard) FROM SERVER postgis_gaxfbdigubom2fwo INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN fema_flood_zone varchar; COMMIT; UPDATE mdc_parcels_camp SET fema_flood_zone = mdc_flood_hazard.fzone FROM mdc_flood_hazard WHERE ST_Within(mdc_parcels_camp.geom_centroid,mdc_flood_hazard.geom); COMMIT; -- drop foreign server(??) -- join mdc_qualified_opportunity_zones to mdc_parcels_camp (within) CREATE SERVER postgis_2k0dg34uilke6ctl FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-2k0dg34uilke6ctl', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_2k0dg34uilke6ctl OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_qualified_opportunity_zones) FROM SERVER postgis_2k0dg34uilke6ctl INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN mdc_qualified_opportunity_zones boolean; COMMIT; UPDATE mdc_parcels_camp SET mdc_qualified_opportunity_zones = true FROM mdc_qualified_opportunity_zones WHERE ST_Within(mdc_parcels_camp.geom_centroid,mdc_qualified_opportunity_zones.geom); COMMIT; -- drop foreign server(??) -- join mdc_major_roads to mdc_parcels_camp (intersects) CREATE SERVER postgis_ljdbztukphqn0glt FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-ljdbztukphqn0glt', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_ljdbztukphqn0glt OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_major_roads) FROM SERVER postgis_ljdbztukphqn0glt INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN mdc_streetid numeric, ADD COLUMN mdc_street_type varchar; COMMIT; UPDATE mdc_parcels_camp SET mdc_streetid = mdc_major_roads.streetid, mdc_street_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_water_polygons to mdc_parcels_camp (overlap) CREATE SERVER postgis_unqeqjtqeg4bwgre FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-unqeqjtqeg4bwgre', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_unqeqjtqeg4bwgre OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_water_polygons) FROM SERVER postgis_unqeqjtqeg4bwgre INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN mdc_water_polygons boolean; COMMIT; UPDATE mdc_parcels_camp SET mdc_water_polygons = true FROM mdc_water_polygons WHERE ST_Intersects(mdc_parcels_camp.geom_local, mdc_water_polygons.geom_local) AND ST_Area(ST_Intersection(mdc_parcels_camp.geom_local, mdc_water_polygons.geom_local))/ST_Area(mdc_parcels_camp.geom_local) > 0.5; COMMIT; -- drop foreign server(??) -- join mdc_2015_dem_30ft to mdc_parcels_camp (extract) CREATE SERVER postgis_vtc29jryy1rbxz07 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-vtc29jryy1rbxz07', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_vtc29jryy1rbxz07 OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_2015_dem_30ft) FROM SERVER postgis_vtc29jryy1rbxz07 INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN elevation numeric; COMMIT; UPDATE mdc_parcels_camp SET elevation = ST_Value(rast,ST_Transform(mdc_parcels_camp.geom_centroid,2236)) FROM mdc_2015_dem_30ft WHERE ST_Intersects(ST_Transform(mdc_parcels_camp.geom_centroid,2236), mdc_2015_dem_30ft.rast); COMMIT; -- drop foreign server(??) -- join mdc_neighborhoods to mdc_parcels_camp (within) CREATE SERVER postgis_d1gxh00d8j6m2xd0 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-d1gxh00d8j6m2xd0', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_d1gxh00d8j6m2xd0 OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_neighborhoods) FROM SERVER postgis_d1gxh00d8j6m2xd0 INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN mdc_neighborhood_name varchar; COMMIT; UPDATE mdc_parcels_camp SET mdc_neighborhood_name = mdc_neighborhoods.name FROM mdc_neighborhoods WHERE ST_Within(mdc_parcels_camp.geom_centroid,mdc_neighborhoods.geom); COMMIT; -- drop foreign server(??) -- join mdc_municipal_boundary to mdc_parcels_camp (within) CREATE SERVER postgis_dn7tn2p5cfahc611 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-dn7tn2p5cfahc611', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_dn7tn2p5cfahc611 OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (mdc_municipal_boundary) FROM SERVER postgis_dn7tn2p5cfahc611 INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN mdc_municipal_boundary_name varchar; COMMIT; UPDATE mdc_parcels_camp SET mdc_municipal_boundary_name = mdc_municipal_boundary.name FROM mdc_municipal_boundary WHERE ST_Within(mdc_parcels_camp.geom_centroid,mdc_municipal_boundary.geom); COMMIT; -- drop foreign server(??) -- join com_neighborhoods to mdc_parcels_camp (within) CREATE SERVER postgis_90zi29yo2f6dr8g0 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'postgis-90zi29yo2f6dr8g0', dbname 'gdsc', port '5432'); CREATE USER MAPPING FOR postgres SERVER postgis_90zi29yo2f6dr8g0 OPTIONS (user '__PGUSER__', password '__PGPASS__'); IMPORT FOREIGN SCHEMA public LIMIT TO (com_neighborhoods) FROM SERVER postgis_90zi29yo2f6dr8g0 INTO public; ALTER TABLE mdc_parcels_camp ADD COLUMN com_neighborhood_name varchar; COMMIT; UPDATE mdc_parcels_camp SET com_neighborhood_name = com_neighborhoods.label FROM com_neighborhoods WHERE ST_Within(mdc_parcels_camp.geom_centroid,com_neighborhoods.geom); 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; "