surrogatetoolsdb's People
surrogatetoolsdb's Issues
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.
simplify projection and grid configuration
Run ./load_shapefile_reproject_multi.quickstart.csh failed with SQL error
review calculations for numerator and denominator fields in output files
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
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
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
Update all scripts to
SELECT surg_code, ${data_attribute}, colnum, rownum, ROUND(surg::NUMERIC, 10), '!', numer, denom
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
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
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
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[...
Add ellipsoid to surrogate header
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)
Support SQLite + SpatiaLite as alternative to Postgres
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.
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
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.
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.
add How-Tos: change shapefiles, how to run gapfilling and merging
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.
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.