Revision 10554
Added by Aaron Marcuse-Kubitza almost 11 years ago
| VegCore.pg.sql | ||
|---|---|---|
| 217 | 217 |
"latitude_deg" text NOT NULL, |
| 218 | 218 |
"longitude_deg" text NOT NULL, |
| 219 | 219 |
"footprint_geom_WKT" text NOT NULL DEFAULT 'point', |
| 220 |
"official_name" text DEFAULT NULL, |
|
| 220 | 221 |
PRIMARY KEY ("id"),
|
| 221 | 222 |
/*CONSTRAINT "fk_geoplace_place1" FOREIGN KEY ("id") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
| 222 | 223 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='A numerically-defined point'*/; |
| ... | ... | |
| 260 | 261 |
/*!40000 ALTER TABLE "georeferencing" ENABLE KEYS */; |
| 261 | 262 |
|
| 262 | 263 |
-- |
| 264 |
-- Table structure for table "geovalidatable_place" |
|
| 265 |
-- |
|
| 266 |
|
|
| 267 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 268 |
/*!40101 SET character_set_client = utf8 */; |
|
| 269 |
CREATE TABLE "geovalidatable_place" ( |
|
| 270 |
"id" text NOT NULL, |
|
| 271 |
"parent_geoplace" text NOT NULL, |
|
| 272 |
PRIMARY KEY ("id"),
|
|
| 273 |
/*KEY "fk_geovalidatable_place_geoplace1_idx" ("parent_geoplace")*/CHECK (true),
|
|
| 274 |
/*CONSTRAINT "fk_geovalidatable_place_geoplace1" FOREIGN KEY ("parent_geoplace") REFERENCES "geoplace" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 275 |
/*CONSTRAINT "fk_nested_geoplace_geoplace1" FOREIGN KEY ("id") REFERENCES "geoplace" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 276 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='stores only scrubbed geoplaces (GADM places, and distinct point coordinates that GNRS says should be located within them)'*/; |
|
| 277 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 278 |
|
|
| 279 |
-- |
|
| 280 |
-- Dumping data for table "geovalidatable_place" |
|
| 281 |
-- |
|
| 282 |
|
|
| 283 |
/*!40000 ALTER TABLE "geovalidatable_place" DISABLE KEYS */; |
|
| 284 |
/*!40000 ALTER TABLE "geovalidatable_place" ENABLE KEYS */; |
|
| 285 |
|
|
| 286 |
-- |
|
| 263 | 287 |
-- Table structure for table "geovalidation" |
| 264 | 288 |
-- |
| 265 | 289 |
|
| ... | ... | |
| 275 | 299 |
PRIMARY KEY ("id"),
|
| 276 | 300 |
/*KEY "fk_geovalidation_geoplace1_idx" ("input_geoplace")*/CHECK (true),
|
| 277 | 301 |
/*KEY "fk_geovalidation_geoplace2_idx" ("corrected_geoplace")*/CHECK (true),
|
| 278 |
/*CONSTRAINT "fk_geovalidation_georeferencing1" FOREIGN KEY ("id") REFERENCES "georeferencing" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 279 |
/*CONSTRAINT "fk_geovalidation_geoplace1" FOREIGN KEY ("input_geoplace") REFERENCES "scrubbed_geoplace" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 280 |
/*CONSTRAINT "fk_geovalidation_geoplace2" FOREIGN KEY ("corrected_geoplace") REFERENCES "scrubbed_geoplace" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 302 |
/*CONSTRAINT "fk_geovalidation_geoplace1" FOREIGN KEY ("input_geoplace") REFERENCES "geovalidatable_place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 303 |
/*CONSTRAINT "fk_geovalidation_geoplace2" FOREIGN KEY ("corrected_geoplace") REFERENCES "geovalidatable_place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 304 |
/*CONSTRAINT "fk_geovalidation_georeferencing1" FOREIGN KEY ("id") REFERENCES "georeferencing" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 281 | 305 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='The accuracy of the [[VegCore#Coordinates|Coordinates]], taking into account the [[VegCore#Placename|Placename]]'*/; |
| 282 | 306 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 283 | 307 |
|
| ... | ... | |
| 689 | 713 |
/*!40000 ALTER TABLE "sampling_event" ENABLE KEYS */; |
| 690 | 714 |
|
| 691 | 715 |
-- |
| 692 |
-- Table structure for table "scrubbed_geoplace" |
|
| 693 |
-- |
|
| 694 |
|
|
| 695 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 696 |
/*!40101 SET character_set_client = utf8 */; |
|
| 697 |
CREATE TABLE "scrubbed_geoplace" ( |
|
| 698 |
"id" text NOT NULL, |
|
| 699 |
"parent_geoplace" text DEFAULT NULL, |
|
| 700 |
"scrubbed_name" text DEFAULT NULL, |
|
| 701 |
PRIMARY KEY ("id"),
|
|
| 702 |
/*KEY "fk_geoplace_geoplace1_idx" ("parent_geoplace")*/CHECK (true),
|
|
| 703 |
/*CONSTRAINT "fk_geoplace_geoplace1" FOREIGN KEY ("parent_geoplace") REFERENCES "scrubbed_geoplace" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 704 |
/*CONSTRAINT "fk_nested_geoplace_geoplace1" FOREIGN KEY ("id") REFERENCES "geoplace" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 705 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='stores only scrubbed geoplaces (GADM places, and distinct point coordinates that GNRS says should be located within them)'*/; |
|
| 706 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 707 |
|
|
| 708 |
-- |
|
| 709 |
-- Dumping data for table "scrubbed_geoplace" |
|
| 710 |
-- |
|
| 711 |
|
|
| 712 |
/*!40000 ALTER TABLE "scrubbed_geoplace" DISABLE KEYS */; |
|
| 713 |
/*!40000 ALTER TABLE "scrubbed_geoplace" ENABLE KEYS */; |
|
| 714 |
|
|
| 715 |
-- |
|
| 716 | 716 |
-- Table structure for table "soil_observation" |
| 717 | 717 |
-- |
| 718 | 718 |
|
Also available in: Unified diff
schemas/VegCore/VegCore.ERD.mwb: GNRS & geovalidation steps: refactored to separate geoscrubbing (matching a named place) from prepping for geovalidation (uniquifying the lat/long and parent place). note that only bare lat/longs without official placenames will be geovalidated.