Draft selection table for best-in-time-and-space version of the `terrain_aggregator` workflow

dhardestylewis commented 2 years ago

Draft selection table for best-in-time-and-space version of the terrain_aggregator workflow

See below for detail on how to read & interpret this table.

Please let me know if you think

I am missing any characteristics to help select one tile over another, or
there is a better preference order for these characteristics

Each row of this table is bucketed by pixelresolution, resolution, pixeltype, year, srid, colorinterp

postgres=# SELECT pixelresolution, resolution, pixeltype, year, srid, colorinterp, COUNT(DISTINCT(PROJECT)), COUNT(*), SUM(DISTINCT(count_by_project)) FROM tnris_lidar_tiles GROUP BY pixelresolution, resolution, pixeltype, year, srid, colorinterp ORDER BY pixelresolution, resolution, pixeltype, year DESC, srid, colorinterp ;

 pixelresolution (m) | resolution (m) | pixeltype | year | srid  | colorinterp | #projects | #tiles_in_bucket  |  sum(#tiles_in_projects)
-----------------+------------+-----------+------+-------+-------------+-------+--------+--------
             0.5 |       0.35 | 32BF      | 2017 | 26915 | Undefined   |     1 |    289 |    289
             0.5 |        0.5 | 32BF      | 2020 |  6343 | Gray        |     1 |      5 |   6450
               1 |        0.5 | 32BF      | 2021 |  6343 | Gray        |     2 |    596 |    596
               1 |        0.5 | 32BF      | 2020 |  6343 | Gray        |     2 |   6692 |   6730
               1 |        0.5 | 32BF      | 2020 |  6344 | Gray        |     1 |     33 |    280
               1 |        0.5 | 32BF      | 2019 | 26914 | Undefined   |     1 |    184 |    184
               1 |        0.5 | 32BF      | 2019 | 26915 | Undefined   |     1 |     44 |     44
               1 |        0.5 | 32BF      | 2018 |  6343 | Undefined   |     1 |     14 |   1002
               1 |        0.5 | 32BF      | 2018 |  6344 | Undefined   |     1 |      4 |   1002
               1 |        0.5 | 32BF      | 2018 | 26914 | Undefined   |     3 |   2251 |  10557
               1 |        0.5 | 32BF      | 2018 | 26915 | Undefined   |     2 |   8288 |  10073
               1 |        0.5 | 32BF      | 2017 | 26914 | Undefined   |     2 |   7080 |  10023
               1 |        0.5 | 32BF      | 2017 | 26915 | Undefined   |     2 |   3784 |   4342
               1 |        0.5 | 32BF      | 2015 | 26914 | Undefined   |     2 |    507 |    507
               1 |        0.5 | 32BF      | 2014 | 26914 | Undefined   |     3 |   2491 |   4961
               1 |        0.5 | 32BF      | 2014 | 26915 | Undefined   |     1 |   2470 |   2729
               1 |        0.5 | 32BF      | 2013 | 26914 | Undefined   |     3 |   4636 |   4636
               1 |        0.5 | 32BF      | 2012 | 26915 | Undefined   |     1 |    112 |    376
               1 |        0.5 | 32BF      | 2011 | 26914 | Undefined   |     5 |   2438 |   2839
               1 |        0.5 | 32BF      | 2011 | 26915 | Undefined   |     1 |    401 |    840
               1 |        0.5 | 32BF      | 2010 | 26914 | Undefined   |     1 |    458 |    458
               1 |        0.7 | 32BF      | 2020 |  6578 | Gray        |     1 |     46 |     46
               1 |        0.7 | 32BF      | 2019 |  6342 | Gray        |     3 |  38687 |  55384
               1 |        0.7 | 32BF      | 2019 |  6343 | Gray        |     3 |  33453 |  67643
               1 |        0.7 | 32BF      | 2019 |  6344 | Gray        |     2 |    883 |  29100
               1 |        0.7 | 32BF      | 2018 |  6342 | Undefined   |     1 |   2484 |  50895
               1 |        0.7 | 32BF      | 2018 |  6343 | Undefined   |     2 |  21112 |  58267
               1 |        0.7 | 32BF      | 2018 | 26913 | Undefined   |     2 |  24597 |  90803
               1 |        0.7 | 32BF      | 2018 | 26914 | Undefined   |     7 | 113281 | 166556
               1 |        0.7 | 32BF      | 2018 | 26915 | Undefined   |     2 |   5082 |  58267
               1 |        0.7 | 32BF      | 2017 | 26914 | Undefined   |     2 |   6906 |   6906
               1 |        0.7 | 32BF      | 2017 | 26915 | Undefined   |     1 |  10138 |  10138
               1 |        0.7 | 32BF      | 2016 | 26914 | Undefined   |     2 |  18152 |  18155
               1 |        0.7 | 32BF      | 2016 | 26915 | Undefined   |     1 |  18390 |  18390
               1 |        0.7 | 32BF      | 2014 | 26914 | Undefined   |     1 |   3431 |   3431
               1 |        0.7 | 32BF      | 2014 | 32614 | Undefined   |     1 |   2252 |   2252
               1 |        0.7 | 32BF      | 2011 | 26913 | Undefined   |     1 |     82 |    419
               1 |        0.7 | 32BF      | 2011 | 26914 | Undefined   |     1 |    337 |    419
               1 |        0.7 | 8BUI      | 2016 | 26914 | Palette     |     1 |      2 |   2901
               1 |        0.7 | 8BUI      | 2016 | 26914 | Undefined   |     1 |      1 |   2901
               1 |          1 | 32BF      | 2014 | 26913 | Undefined   |     1 |    995 |    995
               1 |          1 | 32BF      | 2011 | 26915 | Undefined   |     1 |    433 |    433
               1 |          1 | 32BF      | 2010 | 26914 | Undefined   |     1 |      4 |    915
               1 |          1 | 32BF      | 2009 | 26914 | Undefined   |     1 |    504 |    504
               1 |        1.2 | 32BF      | 2008 | 26914 | Undefined   |     1 |    213 |    213
               1 |        1.4 | 32BF      | 2007 | 26914 | Undefined   |     1 |   1130 |   1673
               1 |        1.4 | 32BF      | 2006 | 26915 | Undefined   |     1 |    290 |   2886
             1.4 |        1.4 | 32BF      | 2007 | 26914 | Undefined   |     1 |    543 |   1673
             1.4 |        1.4 | 32BF      | 2006 | 26914 | Undefined   |     1 |   1199 |   2886
             1.4 |        1.4 | 32BF      | 2006 | 26915 | Undefined   |     1 |   1382 |   2886
             1.5 |        0.5 | 32BF      | 2012 | 26914 | Undefined   |     1 |    264 |    376
             1.5 |          1 | 32BF      | 2012 | 26914 | Undefined   |     1 |     48 |     48
               2 |        0.6 | 32BF      | 2013 | 26914 | Undefined   |     1 |    763 |    763
               2 |        0.7 | 32BF      | 2015 | 26914 | Undefined   |     1 |   1270 |   1270
               2 |        1.5 | 32BF      | 2011 | 26914 | Undefined   |     1 |    275 |    275
               3 |        0.5 | 32BF      | 2014 |  2278 | Undefined   |     1 |   1010 |   1010
               3 |        0.5 | 32BF      | 2010 | 26914 | Undefined   |     1 |    537 |    537
               3 |       0.61 | 32BF      | 2011 | 32614 | Undefined   |     1 |   1456 |   1456
               3 |        0.7 | 32BF      | 2016 |  2278 | Undefined   |     1 |    677 |    677
             3.3 |        0.7 | 32BF      | 2006 |  2279 | Undefined   |     1 |   1602 |   1602
               5 |        0.3 | 32BF      | 2014 | 26914 | Undefined   |     1 |      1 |      1
               5 |        0.5 | 32BF      | 2009 | 26914 | Undefined   |     1 |    322 |    322
               5 |          1 | 32BF      | 2010 | 26914 | Undefined   |     1 |    911 |    915
               5 |          1 | 32BF      | 2009 | 26914 | Undefined   |     3 |   1268 |   1268
               5 |          1 | 32BF      | 2008 |  2278 | Undefined   |     1 |    229 |    229
               5 |        1.4 | 32BF      | 2006 | 26915 | Undefined   |     1 |     15 |   2886
             9.8 |        1.4 | 16BSI     | 2007 |  2277 | Undefined   |     1 |    139 |   2770
             9.8 |        1.4 | 32BF      | 2008 |  2277 | Undefined   |     1 |   2693 |   2693
             9.8 |        1.4 | 32BF      | 2007 |  2277 | Undefined   |     1 |   2631 |   2770
              10 |        1.4 | 32BF      | 2012 |  2277 | Undefined   |     1 |   1085 |   1085
(70 rows)

Basically,

I bucket the complete TNRIS Lidar tileset by the first 6 columns of this table

I throw away the following buckets, relying on separate Fathom 3m complete coverage to fill in any gaps:

1 |        0.7 | 8BUI      | 2016 | 26914 | Palette     |     1 |      2 |   2901
1 |        0.7 | 8BUI      | 2016 | 26914 | Undefined   |     1 |      1 |   2901
9.8 |        1.4 | 16BSI     | 2007 |  2277 | Undefined   |     1 |    139 |   2770

Note: These buckets should be thrown away because their data is in integers, and 1m integer elevation is worse accuracy than the vertical accuracy of the Fathom 3m elevation dataset

I transform these buckets to
- pixelresolution = 0.3 meters
- pixeltype = 32-bit floating point number (32BF)
- srid = NAD83 / Texas Centric Albers Equal Area (EPSG:3083)
- colorinterp = Undefined color interpretation
I build a VRT using the order of the buckets in the table above to select elevation data from one tile over another. The order of these buckets was determined by this order of preference of these characteristics :
- pixelresolution - TNRIS Lidar DEM resolution (in meters)
- resolution - TNRIS Lidar point-cloud resolution (in meters)
- pixeltype - TNRIS Lidar DEM pixel data type
- year DESC - TNRIS Lidar project's release year, most recent year first
- srid - projection unique identifier
- colorinterp - color interpretation scheme (You can read adjacent rows of the table above from left-to-right to see how one each characteristic's values were chosen over each other.)

The last 3 columns in the table above help provide context for each of the buckets :

definition : #projects - number of projects contributing tiles to this bucket
definition : #tiles_in_bucket - total number of tiles in this bucket
definition : sum(#tiles_in_projects) - sum of number of tiles in all projects contributing tiles to this bucket
#projects tells you if multiple projects contribute to a bucket
#tiles_in_bucket/sum(#tiles_in_projects) tells you if a bucket contains every tile of every project that contributes to it

markwang0 commented 2 years ago

lgtm

dhardestylewis commented 2 years ago

note that LPC density could be significantly greater than LPC resolutions reported above

dhardestylewis commented 2 years ago

note PDAL might provide ANPS resolution spec above directly from LPC files

dhardestylewis commented 2 years ago

[ ] update selection table above to TNRIS feedback

replace year with date, refined to the day using @andycarter-pe's TNRIS API calls: https://github.com/andycarter-pe/TNRIS_Terrain_Harvest/blob/master/02_jupyter_notebook/cwe_get_tnris_lidar_collections_20220805.ipynb

new buckets should be delineated by date, srid, colorinterp, first dropping any pixeltype != '32BF'

other existing seamless products such as Fathom3m, USGS National Map will be substituted in at end of table as backfill data to address missing data outside of TNRIS source DEM coverage area, replacing

source DEM selection table

pixelresolution (m) | resolution (m) | pixeltype | date | srid  | colorinterp | #projects | #tiles_in_bucket  |  sum(#tiles_in_projects)
 -----------------|------------|-----------|------|-------|-------------|-------|--------|--------
               3 |        0.5 | 32BF      | 2014 |  2278 | Undefined   |     1 |   1010 |   1010
               3 |        0.5 | 32BF      | 2010 | 26914 | Undefined   |     1 |    537 |    537
               3 |       0.61 | 32BF      | 2011 | 32614 | Undefined   |     1 |   1456 |   1456
               3 |        0.7 | 32BF      | 2016 |  2278 | Undefined   |     1 |    677 |    677
             3.3 |        0.7 | 32BF      | 2006 |  2279 | Undefined   |     1 |   1602 |   1602
               5 |        0.3 | 32BF      | 2014 | 26914 | Undefined   |     1 |      1 |      1
               5 |        0.5 | 32BF      | 2009 | 26914 | Undefined   |     1 |    322 |    322
               5 |          1 | 32BF      | 2010 | 26914 | Undefined   |     1 |    911 |    915
               5 |          1 | 32BF      | 2009 | 26914 | Undefined   |     3 |   1268 |   1268
               5 |          1 | 32BF      | 2008 |  2278 | Undefined   |     1 |    229 |    229
               5 |        1.4 | 32BF      | 2006 | 26915 | Undefined   |     1 |     15 |   2886
             9.8 |        1.4 | 16BSI     | 2007 |  2277 | Undefined   |     1 |    139 |   2770
             9.8 |        1.4 | 32BF      | 2008 |  2277 | Undefined   |     1 |   2693 |   2693
             9.8 |        1.4 | 32BF      | 2007 |  2277 | Undefined   |     1 |   2631 |   2770
              10 |        1.4 | 32BF      | 2012 |  2277 | Undefined   |     1 |   1085 |   1085

with

`seamless DEM backfill table` pixelresolution (m)	resolution (m)	pixeltype	year	srid	colorinterp	#projects	projectname
1	various	32BF	various	?	?	1	USGS National Map (3DEP)
3	various	32BF	various	?	?	1	Fathom3m
3	various	32BF	various	?	?	1	USGS National Map (3DEP)
10	various	32BF	various	?	?	1	USGS National Map (3DEP)

Since the best-in-time-and-space algo will be applied first, there will technically be no substitution of rows above - they will be eliminated beforehand by best-in-time-and-space algo and these other seamless products will be appended to selection table after best-in-time-and-space algo is applied ref, best-in-time-and-space algo: https://github.com/dhardestylewis/terrain_aggregator/issues/41#issuecomment-1207481378

note for QAQC: any interior gaps in TNRIS data coverage that are backfilled with seamless data should be explicitly described with a separate vector image

dhardestylewis commented 2 years ago

replace following characteristic names for clarity:

pixelresolution -> demresolution
resolution -> lpcresolution
pixeltype -> pixeldatatype
year -> acquisitiondate
srid -> projectioncode
colorinterp -> colorinterpretation

dhardestylewis commented 2 years ago

[x] prepping Conda environment to use to gather TNRIS collection acquisitiondate using @andycarter-pe's TNRIS API calls
```
conda create -n tnris_api geopandas pandas shapely
```

dhardestylewis commented 2 years ago

[x] recording Conda environment file for TNRIS API calls

conda env export --from-history > environment_tnris_api.yml

environment_trnris_api.yml

name: tnris_api
channels:
  - conda-forge
  - defaults
dependencies:
  - pandas
  - shapely
  - geopandas

dhardestylewis commented 2 years ago

new buckets should be delineated by date, srid, colorinterp, first dropping any pixeltype != '32BF'

therefore closes https://github.com/dhardestylewis/terrain_aggregator/issues/37

dhardestylewis commented 2 years ago

               3 |        0.5 | 32BF      | 2014 |  2278 | Undefined   |     1 |   1010 |   1010
               3 |        0.5 | 32BF      | 2010 | 26914 | Undefined   |     1 |    537 |    537
               3 |       0.61 | 32BF      | 2011 | 32614 | Undefined   |     1 |   1456 |   1456
               3 |        0.7 | 32BF      | 2016 |  2278 | Undefined   |     1 |    677 |    677
             3.3 |        0.7 | 32BF      | 2006 |  2279 | Undefined   |     1 |   1602 |   1602
               5 |        0.3 | 32BF      | 2014 | 26914 | Undefined   |     1 |      1 |      1
               5 |        0.5 | 32BF      | 2009 | 26914 | Undefined   |     1 |    322 |    322
               5 |          1 | 32BF      | 2010 | 26914 | Undefined   |     1 |    911 |    915
               5 |          1 | 32BF      | 2009 | 26914 | Undefined   |     3 |   1268 |   1268
               5 |          1 | 32BF      | 2008 |  2278 | Undefined   |     1 |    229 |    229
               5 |        1.4 | 32BF      | 2006 | 26915 | Undefined   |     1 |     15 |   2886
             9.8 |        1.4 | 16BSI     | 2007 |  2277 | Undefined   |     1 |    139 |   2770
             9.8 |        1.4 | 32BF      | 2008 |  2277 | Undefined   |     1 |   2693 |   2693
             9.8 |        1.4 | 32BF      | 2007 |  2277 | Undefined   |     1 |   2631 |   2770
              10 |        1.4 | 32BF      | 2012 |  2277 | Undefined   |     1 |   1085 |   1085

@brentporter recommends referring out to TNRIS & possibly IBWC for this question

[ ] if above buckets are excluded, do we still have full coverage of the state?

dhardestylewis / terrain_aggregator

Draft selection table for best-in-time-and-space version of the `terrain_aggregator` workflow #44