UM Geospatial Digital Special Collections

Citation: CIESIN - Center for International Earth Science Information Network, Columbia University (2021). Annual PM2.5 Concentrations for Countries and Urban Areas, v1 (1998 – 2016) [gis dataset]. University of Miami GDSC. https://doi.org/10.7927/rja8-8h89. last updated: 2024-10-28 (update frequency: As Needed). Abstract: The Annual PM2.5 Concentrations for Countries and Urban Areas, 1998-2016, consists of mean concentrations of particulate matter (PM2.5) for countries and urban areas. The PM2.5 data are from the Global Annual PM2.5 Grids from MODIS, MISR and SeaWiFS Aerosol Optical Depth (AOD) with GWR, 1998-2016. The urban areas are from the Global Rural-Urban Mapping Project, Version 1 (GRUMPv1): Urban Extent Polygons, Revision 02, and its time series runs from 1998 to 2016. The country averages are population-weighted such that concentrations in populated areas count more toward the country average than concentrations in less populated areas, and its time series runs from 2008 to 2015 Key words: Global, PM 2.5, Air Quality Open Geospatial Consortium Standards: dynamic vector tiles | | WMS (coming soon) | WFS (coming soon) Analytical Functions: gdsc_get_attributes(tablename=global_pm25_concentration_1998_2016,lat=25.718,lng=-80.279,identifier=name,attrs=[urbid,stndrdname,name,isourbid,sqkm,iso3,unsdcode,countryeng,continent,avpmu_1998,avpmu_1999,avpmu_2000,avpmu_2001,avpmu_2002,avpmu_2003,avpmu_2004,avpmu_2005,avpmu_2006,avpmu_2007,avpmu_2008,avpmu_2009,avpmu_2010,avpmu_2011,avpmu_2012,avpmu_2013,avpmu_2014,avpmu_2015,avpmu_2016],normal='') ETL documentation

ETL for global_pm25_concentration_1998_2016

######### # 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/global_pm25_concentration_1998_2016/{download,etl} && cd /data/global_pm25_concentration_1998_2016 # set update flag based on last update and update frequency do_update=0 if date() < last_update + update_frequency else do_update = 1 # load into postGIS with: ogr2ogr -lco GEOMETRY_NAME=geom -f PostgreSQL PG:"" download/sdei-annual-pm2-5-concentrations-countries-urban-areas-v1-1998-2016-urban-areas.shp -nlt multipolygon -nln global_pm25_concentration_1998_2016 ######### # Step 2 - SQL script (pseudo code): #### # remove duplicate points and make geometries valid: UPDATE global_pm25_concentration_1998_2016 SET geom=ST_MakeValid(ST_RemoveRepeatedPoints(geom));" # add local geometry column and reproject existing geometries into local EPSG: SELECT AddGeometryColumn ( 'global_pm25_concentration_1998_2016', 'geom_local',3857,'multipolygon',2 ); UPDATE global_pm25_concentration_1998_2016 SET geom_local=ST_MakeValid(ST_RemoveRepeatedPoints(ST_Transform(ST_Multi(geom),3857))); CREATE INDEX global_pm25_concentration_1998_2016_geom_local_idx ON global_pm25_concentration_1998_2016 USING GIST (geom_local); NOTIFY pgrst, 'reload schema';"cd /data/global_pm25_concentration_1998_2016/ mkdir -p derived # Create pg_dump and downloadable zip of table in derivate directory pg_dump -d $POSTGRES_DB -U $POSTGRES_USER -p $POSTGRES_PORT -h postgis-56ye0y2nj5ke19ae -t global_pm25_concentration_1998_2016 > derived/global_pm25_concentration_1998_2016.sql tar -czf derived/global_pm25_concentration_1998_2016.sql.tar.gz meta_dcat_global_pm25_concentration_1998_2016.json -C derived global_pm25_concentration_1998_2016.sql # TODO: Create shapefile and downloadable zip of table in derivate directory