cempd/surrogatetoolsdb

surrogatetoolsdb's Issues

Update surrogate_specification_pg.csv for 2017 surrogates

Also need to update shapefile_catalog_pg.csv and util/load_shapefile_reproject_multi.csh
Default grid is already updated to 12US3 (us12k_516x444)

add error checking when generating surrogates

Shapefile hasn't been loaded

PGSRGCREATE script_ERROR>psql:./outputs/<grid>/temp_files/670_create_wp_cty.sql:22: ERROR:  relation "public.spud_cbm_conus_2016" does not exist

Support SQLite + SpatiaLite as alternative to Postgres

restrict surrogate fraction output to 20 characters

Export is handled by the individual scripts in pgscripts / pgscripts2017:

SurrogateToolsDB/pgscripts/template_line_noFF_noWA_nofips.csh

Lines 129 to 138 in c24179c

	# Export surrogate
	echo "Exporting surrogates $schema.surg_${surg_code}_${grid}; "
	echo "#GRID" > ${output_dir}/USA_${surg_code}_NOFILL.txt
	$PGBIN/psql -h $server -d $dbname -U $user --field-separator ' ' -t --no-align ${dbname} << END >> ${output_dir}/USA_${surg_code}_NOFILL.txt
	SELECT surg_code, ${data_attribute}, colnum, rownum, surg, '!', numer, denom
	FROM $schema.surg_${surg_code}_${grid}
	order by ${data_attribute}, colnum, rownum;
	END

Update all scripts to

SELECT surg_code, ${data_attribute}, colnum, rownum, ROUND(surg::NUMERIC, 10), '!', numer, denom

large values around border of domain

From James Beidler:

The first fine scale grid that we have run through the DB surrogate tool is a small domain over NJ for Ben Murphy. In the process of modeling large values were found around the edge/border of the domain. Chris traced this down to the 1NJ1 surrogate and what looked like normalization. This was surprising because I had not run normalization on the surrogates. I dug further and did a step-by-step set of SQL queries to find what is likely a bug in the PG tool.

The likely bug is in the calculation of the denominator of the fraction. The templates/queries use the sum of the weight (ie. population in surrogate 100) from the county-cell table as the denominator. In a domain where the edges partially overlap counties only the population or area of the overlapping regions are included in the sum. This is an “intrinsic” normalization. Another table is created that has the value for the entire county. I believe that this should be the source of the denominator.

As an example, Suffolk county has a population of around 1,473,000. The portion of this county contained within the 1NJ1 domain is very small and has an estimated population of 354. The sum of the fractions in the surrogates for Suffolk should be less than 1% but are 100% because the 354 is used in the denominator. This means that all of the Suffolk county emissions are put into a few cells on the edge of the domain.

At this point this only impacts the 1NJ1 domain. All of the work that has been done with the DB tool on the 12 and 36 km CONUS domains completely overlap the CONUS counties.

Create utility to set up projection and grid table based on GRIDDESC

Currently the output grid projection and grid cell table get set up using hard-coded utility scripts (util/create_900921.sql, util/generate_modeling_grid.sh) instead of using data from the GRIDDESC file. The projection srid is also hard-coded to 900921.

errors running load_shapefile_reproject_multi.2017.csh

Results of running load_shapefile_reproject_multi.2017.csh

pil_2019_06_24
Finished loading shapefile: /proj/ie/proj/staff/cseppan/srgtool/data/emiss_shp2017/PIL/pil_2019_06_24.shp
NOTICE: index "pil_2019_06_24_geom_900921_idx" does not exist, skipping
ERROR: Geometry type (Point) does not match column type (MultiPoint)
MultiPoint

ERTAC_railyard_WRF
Finished loading shapefile: /proj/ie/proj/staff/cseppan/srgtool/data/emiss_shp2017/2010/ERTAC_railyard_WRF.shp
NOTICE: index "ertac_railyard_wrf_geom_900921_idx" does not exist, skipping
ERROR: Geometry type (Point) does not match column type (MultiPoint)
MultiPoint
⚠️ not used in 2017 surrogates

NTAD_2016_ipcd
Finished loading shapefile: /proj/ie/proj/staff/cseppan/srgtool/data/emiss_shp2017/NTAD/NTAD_2016_ipcd.shp
NOTICE: index "ntad_2016_ipcd_geom_900921_idx" does not exist, skipping
ERROR: Geometry type (Point) does not match column type (MultiPoint)
MultiPoint

EIA_2015_US_Oil
Finished loading shapefile: /proj/ie/proj/staff/cseppan/srgtool/data/emiss_shp2017/EIA/EIA_2015_US_Oil.shp
NOTICE: index "eia_2015_us_oil_geom_900921_idx" does not exist, skipping
ERROR: Geometry type (Point) does not match column type (MultiPoint)
MultiPoint

USGS_2011_mines
Finished loading shapefile: /proj/ie/proj/staff/cseppan/srgtool/data/emiss_shp2017/USGS/USGS_2011_mines.shp
NOTICE: index "usgs_2011_mines_geom_900921_idx" does not exist, skipping
ERROR: Geometry type (Point) does not match column type (MultiPoint)
MultiPoint

usa_golf_courses_2019_10
Finished loading shapefile: /proj/ie/proj/staff/cseppan/srgtool/data/emiss_shp2017/POI/usa_golf_courses_2019_10.shp
NOTICE: index "usa_golf_courses_2019_10_geom_900921_idx" does not exist, skipping
ERROR: Geometry type (Point) does not match column type (MultiPoint)
MultiPoint

add How-Tos: change shapefiles, how to run gapfilling and merging

error loading FEMA shapefile with GDAL 3.0.4

With the original fema_bsf_2002bnd.prj file, running ogr2ogr fails:

ogr2ogr -f "PostgreSQL" "PG:dbname=surrogates user=pgsurg host=localhost" data/emiss_shp2017/FEMA/fema_bsf_2002bnd.shp -lco PRECISION=NO -nlt PROMOTE_TO_MULTI -nln public.fema_test -overwrite
ERROR 1: PROJ: proj_as_proj_string: Unsupported conversion method: Lambert_Conformal_Conic

ogr2ogr --version
GDAL 3.0.4, released 2020/01/28

Changing the DATUM name in the fema_bsf_2002bnd.prj file from "Sphere" to "D_Sphere" seems to fix the problem:

PROJCS["Lambert_Conformal_Conic",GEOGCS["WRF_Sphere",DATUM["Sphere",SPHEROID[...

PROJCS["Lambert_Conformal_Conic",GEOGCS["WRF_Sphere",DATUM["D_Sphere",SPHEROID[...

review calculations for numerator and denominator fields in output files

list of currently used library and software versions

cat /etc/redhat-release
Red Hat Enterprise Linux release 8.8 (Ootpa)

/usr/bin/proj
Rel. 6.3.2, May 1st, 2020

/usr/bin/geos-config --version
3.7.2

/usr/bin/gdal-config --version
3.0.4

/usr/bin/pg_config --version
PostgreSQL 15.2

select postgis_full_version();
POSTGIS="3.3.2 4975da8" [EXTENSION] PGSQL="150" GEOS="3.7.2-CAPI-1.11.2 b55d2125" PROJ="6.3.2" GDAL="GDAL 3.0.4, released 2020/01/28" LIBXML="2.9.7" RASTER

Run ./load_shapefile_reproject_multi.quickstart.csh failed with SQL error

I failed on running this command
./load_shapefile_reproject_multi.quickstart.csh
with the following error, do you know how to fix it?

I'm using postgis version 3 and setup the extension with the following cmd.
CREATE EXTENSION postgis_raster;

Add ellipsoid to surrogate header

point geometries need to be promoted to multipoint

Sample error from issue #8

pil_2019_06_24
Finished loading shapefile: /proj/ie/proj/staff/cseppan/srgtool/data/emiss_shp2017/PIL/pil_2019_06_24.shp
NOTICE: index "pil_2019_06_24_geom_900921_idx" does not exist, skipping
ERROR: Geometry type (Point) does not match column type (MultiPoint)
MultiPoint

When the shapefiles are loaded using ogr2ogr, the flag "-nlt PROMOTE_TO_MULTI" is included which converts polygons and linestrings to their multi counterparts. However, this doesn't promote Point geometries to MultiPoint. Further processing by the surrogate tool scripts expects the geometries to be MultiPoint.

fix issue with previous run data not being cleaned up

From Alison Eyth:

We found that it seems some intermediate data from an earlier run of some surrogates was merged with the updated data in the new run.

We believe that the printf statement on line 22 of template_polygon_withFF_noWA.csh needs to append (>>) rather than replace (>).

Noting that when a surrogate is run, DROP TABLE command(s) should be issued to remove any old tables – most of the other scripts seem to do this, but not this one.

simplify projection and grid configuration

add error checking when loading shapefiles

ogr2ogr error

FAILURE:
Unable to open datasource `/path/to/shapefile.shp' with the following drivers.

User needs to check location and names of shapefiles on disk.

SQL errors

ERROR:  column "geom_900921" of relation "acs2016_5yr_bg" already exists

This happens if the shapefile was already loaded in.

errors generating 2017 surrogates

Run PostgreSQL/PostgreGIS to generate surrogate ratios for USA_239
Error: Catalog csv file does not contain surrogate -- hpms2017_v3_04052020

Run PostgreSQL/PostgreGIS to generate surrogate ratios for USA_240
Error: Catalog csv file does not contain surrogate -- hpms2017_v3_04052020

Run PostgreSQL/PostgreGIS to generate surrogate ratios for USA_242
Error: Catalog csv file does not contain surrogate -- hpms2017_v3_04052020

Run PostgreSQL/PostgreGIS to generate surrogate ratios for USA_244
Error: Catalog csv file does not contain surrogate -- hpms2017_v3_04052020

existing surrogate tables didn't get deleted

Run PostgreSQL/PostgreGIS to generate surrogate ratios for USA_300
PGSRGCREATE script_OUTPUT>public.cb_2017_us_county_500k
PGSRGCREATE script_OUTPUT>Cutting by data shapefile boundaries
PGSRGCREATE script_ERROR>psql:./outputs/bos_1_126x141/temp_files/300_create_wp_cty.sql:3: ERROR: relation "wp_cty_300_900921" already exists
PGSRGCREATE script_ERROR>psql:./outputs/bos_1_126x141/temp_files/300_create_wp_cty.sql:4: ERROR: column "geom_900921" of relation "wp_cty_300_900921" already exists
PGSRGCREATE script_ERROR>CONTEXT: SQL statement "ALTER TABLE public.wp_cty_300_900921 ADD COLUMN geom_900921 geometry(MultiPolygon, 900921)"
PGSRGCREATE script_ERROR>PL/pgSQL function addgeometrycolumn(character varying,character varying,character varying,character varying,integer,character varying,integer,boolean) line 106 at EXECUTE
PGSRGCREATE script_ERROR>SQL statement "SELECT public.AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)"
PGSRGCREATE script_ERROR>PL/pgSQL function addgeometrycolumn(character varying,character varying,character varying,integer,character varying,integer,boolean) line 5 at SQL statement
PGSRGCREATE script_OUTPUT>INSERT 0 195419
PGSRGCREATE script_OUTPUT>UPDATE 0
PGSRGCREATE script_OUTPUT>UPDATE 781676
PGSRGCREATE script_OUTPUT>CREATE INDEX
PGSRGCREATE script_OUTPUT>VACUUM
PGSRGCREATE script_OUTPUT>Gridding weight data to modeling domain
PGSRGCREATE script_OUTPUT>DROP TABLE
PGSRGCREATE script_OUTPUT>CREATE TABLE
PGSRGCREATE script_OUTPUT> addgeometrycolumn
PGSRGCREATE script_OUTPUT>----------------------------------------------------------------------------------------
PGSRGCREATE script_OUTPUT> public.wp_cty_cell_300_bos_1_126x141.geom_900921 SRID:900921 TYPE:MultiPolygon DIMS:2
PGSRGCREATE script_OUTPUT>(1 row)
PGSRGCREATE script_OUTPUT>
PGSRGCREATE script_OUTPUT>INSERT 0 36780
PGSRGCREATE script_OUTPUT>UPDATE 0
PGSRGCREATE script_OUTPUT>UPDATE 36780
PGSRGCREATE script_OUTPUT>CREATE INDEX
PGSRGCREATE script_OUTPUT>VACUUM
PGSRGCREATE script_OUTPUT>CREATE TABLE public.numer_300_bos_1_126x141
PGSRGCREATE script_OUTPUT>DROP TABLE
PGSRGCREATE script_OUTPUT>CREATE TABLE
PGSRGCREATE script_OUTPUT>INSERT 0 5959
PGSRGCREATE script_OUTPUT>CREATE TABLE public.denom_300_bos_1_126x141; create primary key
PGSRGCREATE script_OUTPUT>DROP TABLE
PGSRGCREATE script_OUTPUT>CREATE TABLE
PGSRGCREATE script_OUTPUT>INSERT 0 3127
PGSRGCREATE script_OUTPUT>CREATE TABLE public.surg_300_bos_1_126x141; add primary key
PGSRGCREATE script_OUTPUT>DROP TABLE
PGSRGCREATE script_OUTPUT>CREATE TABLE
PGSRGCREATE script_OUTPUT>INSERT 0 5959
PGSRGCREATE script_OUTPUT>Exporting surrogates public.surg_300_bos_1_126x141;
SUCCESS IN RUNNING THE EXECUTABLE: PGSRGCREATE script