search

WikiWatershed / model-my-watershed

The web application front end for Model My Watershed.
https://modelmywatershed.org
Apache License 2.0
57 stars 31 forks source link

Follow up on Empty AoI Projects #3068

Closed rajadain closed 3 years ago

rajadain commented 5 years ago

From #3064

TODO: Add recommendations for how to proceed.

rajadain commented 5 years ago

This list of projects with no area of interest, taken from a backup before the last production deployment on 12/18/2018, indicates that the only two projects affected were those addressed in #3064:

SELECT id, name, area_of_interest_name, modified_at
FROM modeling_project
WHERE area_of_interest IS NULL
ORDER BY modified_at DESC;

  id   |                       name                       |                     area_of_interest_name                      |          modified_at
-------+--------------------------------------------------+----------------------------------------------------------------+-------------------------------
 25753 | Untitled Project                                 | Selected Area                                                  | 2018-11-14 18:45:09.520279+00
 25660 | WEST GROVE BOROUGH                               | Selected Area                                                  | 2018-11-07 19:09:05.256515+00
 25588 | modle my water shed                              | 1 Square Km                                                    | 2018-11-06 14:27:20.506396+00
 25556 | Earth Space project                              | 1 Square Km                                                    | 2018-11-04 21:11:43.079216+00
 24780 | FSNHP_RockyRunLower_SiteStorm                    | Delaware High Resolution                                       | 2018-10-03 14:17:32.920611+00
 25362 | sandy run site storm                             | Delaware High Resolution                                       | 2018-10-02 15:02:55.558739+00
 25204 | Honeybrook Phase I and I planning                | Continental US Medium Resolution                               | 2018-09-07 16:29:43.962262+00
 24789 | New Britain HS demo                              | Selected Area                                                  | 2018-07-20 17:35:44.870013+00
 21885 | Untitled Project                                 | Lower Conestoga River, HUC-12 Subwatershed                     | 2018-07-12 21:07:57.368595+00
 18602 | FiveMile Creek                                   | Fivemile Creek, HUC-12 Subwatershed                            | 2018-06-22 18:38:26.805671+00
 18309 | Spanish Needle                                   | Spanish Needle Creek-Upper Macoupin Creek, HUC-12 Subwatershed | 2018-06-22 18:37:12.351288+00
 18337 | Bullard Lake                                     | Bullard Lake-Middle Macoupin Creek, HUC-12 Subwatershed        | 2018-06-22 18:35:40.461002+00
 24516 | Sponge city team 6                               | City of Houston-Buffalo Bayou, HUC-12 Subwatershed             | 2018-06-13 18:59:51.678238+00
 24454 | Untitled Project                                 | Bear Creek, HUC-12 Subwatershed                                | 2018-06-11 02:17:14.058951+00
 24453 | Untitled Project                                 | City of Philadelphia-Schuylkill River, HUC-12 Subwatershed     | 2018-06-11 02:17:12.759324+00
 24401 | City of Philadelphia-Schuylkill River            | City of Philadelphia-Schuylkill River, HUC-12 Subwatershed     | 2018-06-11 01:32:42.216476+00
 23577 | Lower Ark - 110300130308                         | Negro Creek-Arkansas River, HUC-12 Subwatershed                | 2018-05-15 19:25:03.528369+00
 22627 | ENVS 270                                         | Upper Indian Bend Wash, HUC-12 Subwatershed                    | 2018-05-03 05:13:53.037829+00
  9126 | W Br Brandywine Unnamed Trib #1 Honey Brook Area | Delaware High Resolution                                       | 2018-04-27 17:53:09.104574+00
 13150 | East Br RCC Ph2 Focus Area (v2)                  | Delaware High Resolution                                       | 2018-04-27 17:43:27.456982+00
 21865 | Nickel Mines Run/Meetinghouse Creek              | Selected Area                                                  | 2018-04-26 20:38:17.583964+00
 22320 | Pine Creek                                       | Pine Creek, HUC-10 Watershed                                   | 2018-04-13 18:37:19.676293+00
 21427 | Cherry Ck D SL103                                | Delaware High Resolution                                       | 2018-03-23 18:18:22.294245+00
 21428 | Cherry Ck Up-SL104                               | Delaware High Resolution                                       | 2018-03-23 18:18:16.957613+00
 21463 | Musconetcong D SL102                             | Delaware High Resolution                                       | 2018-03-23 18:18:11.853518+00
 21462 | Musconetcong U SL101                             | Delaware High Resolution                                       | 2018-03-23 18:18:11.022675+00
 21464 | Musconetcong D SL102                             | Delaware High Resolution                                       | 2018-03-23 18:18:07.05709+00
 21465 | Musconetcong D SL102                             | Delaware High Resolution                                       | 2018-03-23 18:18:01.458195+00
 21466 | Pennypack U SL111                                | Delaware High Resolution                                       | 2018-03-23 18:18:01.380313+00
 21472 | Pennypack D SL112                                | Delaware High Resolution                                       | 2018-03-23 18:17:56.136952+00
 21768 | SL113Pickering east (stream1)                    | Delaware High Resolution                                       | 2018-03-23 18:17:51.771213+00
 21767 | SL114Pickering West (stream 2)                   | Delaware High Resolution                                       | 2018-03-23 18:17:50.915399+00
 21789 | Merrill Creek                                    | Delaware High Resolution                                       | 2018-03-23 18:17:43.406988+00
 21799 | SL116-West Portal Brook                          | Delaware High Resolution                                       | 2018-03-23 18:17:38.627262+00
 21798 | SL133 upper Paulinskill                          | Delaware High Resolution                                       | 2018-03-23 18:17:37.906079+00
 21839 | SL118 Wissahickon                                | Delaware High Resolution                                       | 2018-03-23 18:17:33.475177+00
 21889 | SL128 Jacobs D                                   | Delaware High Resolution                                       | 2018-03-23 18:17:28.906322+00
 21888 | SL127 Jacobs U                                   | Delaware High Resolution                                       | 2018-03-23 18:17:28.424593+00
 21891 | SL130 W Br Red Clay                              | Delaware High Resolution                                       | 2018-03-23 18:17:24.045962+00
 21892 | SL131 Little Lehigh                              | Delaware High Resolution                                       | 2018-03-23 18:17:19.566404+00
 21893 | SL132 Mill                                       | Delaware High Resolution                                       | 2018-03-23 18:17:17.058108+00
 21911 | SL134 Mine Hole, NY                              | Continental US Medium Resolution                               | 2018-03-23 18:17:11.592577+00
 21913 | SL136 Hosensack                                  | Delaware High Resolution                                       | 2018-03-23 18:17:06.257548+00
 21912 | SL135 Pickering-Montgomery Sch                   | Delaware High Resolution                                       | 2018-03-23 18:17:05.782644+00
 21914 | SL137 UT to Cobbs                                | Delaware High Resolution                                       | 2018-03-23 18:17:00.920048+00
 21915 | SL138 Pickering-Phoenixville YMCA                | Delaware High Resolution                                       | 2018-03-23 18:16:55.5014+00
 21916 | SL139 Chester                                    | Delaware High Resolution                                       | 2018-03-23 18:16:54.666855+00
 21984 | SL105 Paulinskill-Memory Park                    | Delaware High Resolution                                       | 2018-03-23 18:16:02.784282+00
 21807 | Franklin                                         | Selected Area                                                  | 2018-03-15 22:44:46.781315+00
  2170 | Untitled Project                                 | Stream Network                                                 | 2016-04-19 15:40:50.812718+00
  2169 | Untitled Project                                 | Stream Network                                                 | 2016-04-19 15:18:24.115144+00
  2168 | Untitled Project                                 | Stream Network                                                 | 2016-04-19 15:14:27.463849+00
  2167 | Untitled Project                                 | Stream Network                                                 | 2016-04-19 15:00:27.638741+00
(53 rows)
rajadain commented 4 years ago

This has now been observed again. There are now 51 additional (a total of 100) projects in production whose areas of interest have gone missing:

SELECT *
FROM modeling_project
WHERE area_of_interest IS NULL
  AND id NOT IN (25753, 25660, 25588, 25556, 24780, 25362, 25204, 24789, 21885, 18602, 18309, 18337, 24516, 24454, 24453, 24401, 23577, 22627,  9126, 13150, 21865, 22320, 21427, 21428, 21463, 21462, 21464, 21465, 21466, 21472, 21768, 21767, 21789, 21799, 21798, 21839, 21889, 21888, 21891, 21892, 21893, 21911, 21913, 21912, 21914, 21915, 21916, 21984, 21807,  2170,  2169,  2168,  2167);

  id   |                          name                           | area_of_interest | is_private | model_package |          created_at           |          modified_at          | user_id | is_activity |                       area_of_interest_name                        | gis_data | wkaoi | mapshed_job_uuid_id | subbasin_mapshed_job_uuid_id
-------+---------------------------------------------------------+------------------+------------+---------------+-------------------------------+-------------------------------+---------+-------------+--------------------------------------------------------------------+----------+-------+---------------------+------------------------------
 26871 | SR2 Complementary GSI Locations                         |                  | f          | tr-55         | 2019-02-18 17:14:00.994221+00 | 2019-02-18 17:14:35.572131+00 |    8963 | f           | Continental US Medium Resolution                                   |          |       |                     |
 10514 | McKeever Environmental Center, PA                       |                  | f          | tr-55         | 2017-03-10 17:17:37.474776+00 | 2019-02-08 16:11:03.936552+00 |    1827 | f           | Continental US Medium Resolution                                   |          |       |                     |
 27686 | Untitled Project                                        |                  | f          | tr-55         | 2019-04-18 20:50:32.557637+00 | 2019-04-18 20:52:30.10901+00  |    1827 | f           | Selected Area                                                      |          |       |                     |
 11345 | Los Angeles, CA NSTA - Site Storm                       |                  | f          | tr-55         | 2017-04-01 17:40:45.72843+00  | 2019-02-08 16:10:12.54666+00  |    1827 | f           | 1 Square Km                                                        |          |       |                     |
 24776 | FSNHP_RockyRunUpper_SiteStorm                           |                  | f          | tr-55         | 2018-07-17 19:45:15.020988+00 | 2019-02-13 18:04:49.809965+00 |    2166 | f           | Delaware High Resolution                                           |          |       |                     |
 24793 | Huesken Middle School                                   |                  | f          | tr-55         | 2018-07-23 13:45:12.624335+00 | 2019-02-07 16:09:58.08792+00  |    1827 | f           | Selected Area                                                      |          |       |                     |
 21565 | Wissahickon                                             |                  | f          | gwlfe         | 2018-03-06 17:56:43.178254+00 | 2019-02-07 16:11:39.334983+00 |    1827 | f           | Wissahickon Creek, HUC-10 Watershed                                |          |       |                     |
 26527 | Untitled Project                                        |                  | f          | gwlfe         | 2019-01-15 16:42:03.214271+00 | 2019-02-07 16:09:25.199283+00 |    1827 | f           | Shellpot Creek-Delaware River, HUC-10 Watershed (ID 0204020506)    |          |       |                     |
 26793 | Untitled Project                                        |                  | f          | tr-55         | 2019-02-08 16:04:07.398041+00 | 2019-02-08 16:06:27.425982+00 |    1827 | f           | Lower Little Conewago Creek, HUC-12 Subwatershed (ID 020503060402) |          |       |                     |
 26823 | Middle Chattahoochee                                    |                  | f          | tr-55         | 2019-02-11 19:22:31.269815+00 | 2019-02-11 19:23:00.257979+00 |    9635 | f           | Middle Chattahoochee-Lake Harding, HUC-8 Subbasin (ID 03130002)    |          |       |                     |
 26822 | Untitled Project                                        |                  | f          | gwlfe         | 2019-02-11 18:42:20.606134+00 | 2019-02-11 19:17:23.895478+00 |    9635 | f           | Lower Ogeechee, HUC-8 Subbasin (ID 03060202)                       |          |       |                     |
 26850 | Monticello Site Storm Model                             |                  | f          | tr-55         | 2019-02-13 19:44:09.898297+00 | 2019-02-14 14:35:12.296285+00 |    9647 | f           | Selected Area                                                      |          |       |                     |
 30536 | Oxbow Spring Watershed                                  |                  | f          | gwlfe         | 2020-01-23 17:26:45.934629+00 | 2020-06-17 18:29:55.233087+00 |   10282 | f           | Continental US Medium Resolution                                   |          |       |                     |
 26855 | Monticello Watershed Multi-Year Model                   |                  | f          | gwlfe         | 2019-02-13 20:06:06.609396+00 | 2019-02-14 14:34:56.234249+00 |    9647 | f           | Selected Area                                                      |          |       |                     |
 28103 | Watershed 30-yr                                         |                  | f          | gwlfe         | 2019-05-13 15:37:57.200158+00 | 2019-06-16 13:30:02.271099+00 |    9987 | f           | Delaware High Resolution                                           |          |       |                     |
 27313 | acraft                                                  |                  | f          | tr-55         | 2019-03-08 17:14:02.094119+00 | 2019-03-18 15:45:49.711033+00 |    9763 | f           | 1 Square Km                                                        |          |       |                     |
 26730 | Upper Chattahoochee                                     |                  | f          | tr-55         | 2019-02-04 19:57:35.524516+00 | 2019-02-11 19:20:39.695552+00 |    9635 | f           | Upper Chattahoochee, HUC-8 Subbasin (ID 03130001)                  |          |       |                     |
 26742 | Watershed with Airport Drainage                         |                  | f          | tr-55         | 2019-02-05 21:46:13.401709+00 | 2019-02-06 00:04:47.067601+00 |    9630 | f           | Selected Area                                                      |          |       |                     |
 26743 | Watershed Draining in Canal                             |                  | f          | gwlfe         | 2019-02-06 00:09:02.994246+00 | 2019-02-06 00:10:44.032545+00 |    9630 | f           | Selected Area                                                      |          |       |                     |
 26744 | Watershed Draining in Swamp                             |                  | f          | gwlfe         | 2019-02-06 00:13:15.079293+00 | 2019-02-06 00:13:50.116055+00 |    9630 | f           | Selected Area                                                      |          |       |                     |
 26745 | Watershed Draining in Petite Bayou                      |                  | f          | gwlfe         | 2019-02-06 00:16:36.137533+00 | 2019-02-06 00:17:02.079113+00 |    9630 | f           | Selected Area                                                      |          |       |                     |
 26746 | Undetermined / Whole Park                               |                  | f          | gwlfe         | 2019-02-06 00:19:22.01101+00  | 2019-02-06 00:20:36.824481+00 |    9630 | f           | Selected Area                                                      |          |       |                     |
 31182 | ATN Data Center: Site Map                               |                  | f          | tr-55         | 2020-04-13 20:28:11.79981+00  | 2020-04-26 20:30:45.252746+00 |   10536 | f           | Selected Area                                                      |          |       |                     |
 29542 | Paxton Creek TMDL                                       |                  | f          | gwlfe         | 2019-10-07 19:41:36.596705+00 | 2019-10-31 12:23:04.942364+00 |   10131 | f           | Selected Area                                                      |          |       |                     |
 29571 | 20 wildflower                                           |                  | f          | tr-55         | 2019-10-10 19:45:39.972417+00 | 2019-10-10 20:02:37.342636+00 |   10168 | f           | Selected Area                                                      |          |       |                     |
 29572 | ChurchRoad                                              |                  | f          | tr-55         | 2019-10-10 19:45:42.844539+00 | 2019-10-10 20:02:43.982094+00 |   10172 | f           | Selected Area                                                      |          |       |                     |
 29573 | Workshop Map                                            |                  | f          | tr-55         | 2019-10-10 19:45:45.7431+00   | 2019-10-10 20:02:38.072616+00 |   10170 | f           | Selected Area                                                      |          |       |                     |
 29574 | Untitled Project                                        |                  | f          | tr-55         | 2019-10-10 19:45:54.810848+00 | 2019-10-10 20:02:36.656921+00 |    8080 | f           | Selected Area                                                      |          |       |                     |
 30650 | Untitled Project                                        |                  | f          | tr-55         | 2020-02-11 20:59:53.547474+00 | 2020-02-11 21:00:16.989253+00 |    9099 | f           | North Fabius, HUC-8 Subbasin (ID 07110002)                         |          |       |                     |
 31260 | Untitled Project                                        |                  | t          | gwlfe         | 2020-04-22 13:58:22.228669+00 | 2020-04-22 14:06:55.186249+00 |    2166 | f           | Delaware High Resolution                                           |          |       |                     |
 29668 | UNT to Spring Creek                                     |                  | f          | gwlfe         | 2019-10-23 13:00:57.633953+00 | 2019-10-31 12:23:19.362599+00 |   10131 | f           | Selected Area                                                      |          |       |                     |
 29740 | Brightside Farm Riparian Buffer                         |                  | f          | gwlfe         | 2019-10-28 20:26:19.405101+00 | 2019-10-28 21:49:54.865508+00 |    7834 | f           | Selected Area                                                      |          |       |                     |
 31328 | Untitled Project                                        |                  | f          | tr-55         | 2020-04-27 23:25:45.729798+00 | 2020-04-27 23:26:26.488896+00 |   10582 | f           | Montrose Community Schools, School Districts                       |          |       |                     |
 29904 | 0 Paper Mill Road Newark DE NCC                         |                  | f          | tr-55         | 2019-11-06 16:56:09.066642+00 | 2019-11-06 17:17:42.552594+00 |    7644 | f           | Selected Area                                                      |          |       |                     |
 29905 | Westport                                                |                  | f          | tr-55         | 2019-11-06 17:42:57.943001+00 | 2019-11-14 15:43:39.718452+00 |    9414 | f           | Continental US Medium Resolution                                   |          |       |                     |
 29906 | Federal Hill Prep                                       |                  | f          | tr-55         | 2019-11-06 17:48:31.857151+00 | 2019-11-14 15:42:35.540828+00 |    9414 | f           | Continental US Medium Resolution                                   |          |       |                     |
 29907 | Cherry Hill                                             |                  | f          | tr-55         | 2019-11-06 18:00:27.095108+00 | 2019-11-14 15:48:55.319079+00 |    9414 | f           | Continental US Medium Resolution                                   |          |       |                     |
 28642 | Untitled Project                                        |                  | f          | tr-55         | 2019-06-06 19:46:15.039012+00 | 2019-07-02 18:16:03.264398+00 |    1827 | f           | Selected Area                                                      |          |       |                     |
 28428 | Untitled Project                                        |                  | f          | tr-55         | 2019-05-24 18:35:19.754635+00 | 2019-05-24 18:35:06.598762+00 |    1827 | f           | Selected Area                                                      |          |       |                     |
 31878 | Frederick Elementary School Site Storm                  |                  | f          | tr-55         | 2020-08-18 19:55:59.358+00    | 2020-09-02 21:16:03.871003+00 |    8080 | f           | Selected Area                                                      |          |       |                     |
 30768 | Vermilion Headwaters Watershed Partnership Project Area |                  | f          | gwlfe         | 2020-02-26 02:34:03.810686+00 | 2020-02-26 02:35:11.277482+00 |    9212 | f           | Selected Area                                                      |          |       |                     |
 28119 | Roadway 30-yr                                           |                  | f          | gwlfe         | 2019-05-13 16:17:09.124612+00 | 2019-06-16 13:29:37.306509+00 |    9987 | f           | Selected Area                                                      |          |       |                     |
 31517 | LLA Data Center: MultiYear Model - Itasca, MN Surrogate |                  | f          | gwlfe         | 2020-05-14 23:16:14.125016+00 | 2020-05-14 23:41:49.54507+00  |   10536 | f           | Selected Area                                                      |          |       |                     |
 30064 | Untitled Project                                        |                  | t          | gwlfe         | 2019-11-16 18:51:02.799485+00 | 2019-11-20 15:37:57.475864+00 |    2166 | f           | Delaware High Resolution                                           |          |       |                     |
 31897 | Dulaney High School                                     |                  | f          | tr-55         | 2020-08-21 17:24:32.569965+00 | 2020-09-02 21:17:45.695717+00 |    8080 | f           | Selected Area                                                      |          |       |                     |
 31701 | vinecreek1                                              |                  | f          | tr-55         | 2020-07-15 19:23:37.227031+00 | 2020-07-16 19:14:27.617029+00 |   10634 | f           | Delaware High Resolution                                           |          |       |                     |
 31705 | Tredyffrin Delaware Watershed                           |                  | t          | tr-55         | 2020-07-16 20:08:19.95406+00  | 2020-07-16 20:13:16.9784+00   |   10236 | f           | Selected Area                                                      |          |       |                     |
 31708 | Bearhill Downs2 using shapefile                         |                  | f          | tr-55         | 2020-07-17 21:13:11.763437+00 | 2020-07-20 18:25:30.326471+00 |   10629 | f           | Selected Area                                                      |          |       |                     |
 30316 | WestHanover_SwataraCR                                   |                  | f          | gwlfe         | 2019-12-09 20:38:53.746595+00 | 2019-12-11 13:22:07.92475+00  |   10171 | f           | Selected Area                                                      |          |       |                     |
 31050 | Paint Lick Example                                      |                  | f          | tr-55         | 2020-03-16 20:09:42.179729+00 | 2020-03-17 19:41:20.173837+00 |   10494 | f           | Continental US Medium Resolution                                   |          |       |                     |
 31077 | E90_Outfall                                             |                  | f          | tr-55         | 2020-03-22 16:48:14.936419+00 | 2020-03-27 21:19:28.835498+00 |   10499 | f           | Delaware High Resolution                                           |          |       |                     |
(51 rows)

As can be seen, they are both GWLF-E and TR-55, have AoIs of all origins, some are new and some are old, some are public and some are private. Doesn't seem to be a clear pattern.

rajadain commented 4 years ago

Consider adding an update method to the ProjectUpdateSerializer to reject changes to a project that empty the area of interest unless is_activity is true

rajadain commented 4 years ago

A better check to install would be a database level one, which should prevent non-Django updates to the database that may cause this as well.

Something like the one suggested here: https://dba.stackexchange.com/a/145513

This would require that the existing problem projects be removed, since the database constraint would apply to all data, old and new.

rajadain commented 4 years ago

Generated a list of missing projects using this command in psql on production:

\copy (SELECT p.id, p.name, p.area_of_interest_name, p.modified_at, p.created_at, u.username, u.email, u.last_login, u.date_joined FROM modeling_project p INNER JOIN auth_user u ON p.user_id = u.id WHERE area_of_interest IS NULL) TO '~/broken-projects-20201013.csv' CSV HEADER;