Revision 10554
Added by Aaron Marcuse-Kubitza almost 11 years ago
| VegCore.my.sql | ||
|---|---|---|
| 215 | 215 |
`latitude_deg` varbinary(767) NOT NULL, |
| 216 | 216 |
`longitude_deg` varbinary(767) NOT NULL, |
| 217 | 217 |
`footprint_geom_WKT` varbinary(767) NOT NULL DEFAULT 'point', |
| 218 |
`official_name` varbinary(767) DEFAULT NULL, |
|
| 218 | 219 |
PRIMARY KEY (`id`), |
| 219 | 220 |
CONSTRAINT `fk_geoplace_place1` FOREIGN KEY (`id`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
| 220 | 221 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='A numerically-defined point'; |
| ... | ... | |
| 258 | 259 |
/*!40000 ALTER TABLE `georeferencing` ENABLE KEYS */; |
| 259 | 260 |
|
| 260 | 261 |
-- |
| 262 |
-- Table structure for table `geovalidatable_place` |
|
| 263 |
-- |
|
| 264 |
|
|
| 265 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 266 |
/*!40101 SET character_set_client = utf8 */; |
|
| 267 |
CREATE TABLE `geovalidatable_place` ( |
|
| 268 |
`id` varbinary(767) NOT NULL, |
|
| 269 |
`parent_geoplace` varbinary(767) NOT NULL, |
|
| 270 |
PRIMARY KEY (`id`), |
|
| 271 |
KEY `fk_geovalidatable_place_geoplace1_idx` (`parent_geoplace`), |
|
| 272 |
CONSTRAINT `fk_geovalidatable_place_geoplace1` FOREIGN KEY (`parent_geoplace`) REFERENCES `geoplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 273 |
CONSTRAINT `fk_nested_geoplace_geoplace1` FOREIGN KEY (`id`) REFERENCES `geoplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 274 |
) 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)'; |
|
| 275 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 276 |
|
|
| 277 |
-- |
|
| 278 |
-- Dumping data for table `geovalidatable_place` |
|
| 279 |
-- |
|
| 280 |
|
|
| 281 |
/*!40000 ALTER TABLE `geovalidatable_place` DISABLE KEYS */; |
|
| 282 |
/*!40000 ALTER TABLE `geovalidatable_place` ENABLE KEYS */; |
|
| 283 |
|
|
| 284 |
-- |
|
| 261 | 285 |
-- Table structure for table `geovalidation` |
| 262 | 286 |
-- |
| 263 | 287 |
|
| ... | ... | |
| 273 | 297 |
PRIMARY KEY (`id`), |
| 274 | 298 |
KEY `fk_geovalidation_geoplace1_idx` (`input_geoplace`), |
| 275 | 299 |
KEY `fk_geovalidation_geoplace2_idx` (`corrected_geoplace`), |
| 276 |
CONSTRAINT `fk_geovalidation_georeferencing1` FOREIGN KEY (`id`) REFERENCES `georeferencing` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 277 |
CONSTRAINT `fk_geovalidation_geoplace1` FOREIGN KEY (`input_geoplace`) REFERENCES `scrubbed_geoplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 278 |
CONSTRAINT `fk_geovalidation_geoplace2` FOREIGN KEY (`corrected_geoplace`) REFERENCES `scrubbed_geoplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 300 |
CONSTRAINT `fk_geovalidation_geoplace1` FOREIGN KEY (`input_geoplace`) REFERENCES `geovalidatable_place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 301 |
CONSTRAINT `fk_geovalidation_geoplace2` FOREIGN KEY (`corrected_geoplace`) REFERENCES `geovalidatable_place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 302 |
CONSTRAINT `fk_geovalidation_georeferencing1` FOREIGN KEY (`id`) REFERENCES `georeferencing` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 279 | 303 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='The accuracy of the [[VegCore#Coordinates|Coordinates]], taking into account the [[VegCore#Placename|Placename]]'; |
| 280 | 304 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 281 | 305 |
|
| ... | ... | |
| 687 | 711 |
/*!40000 ALTER TABLE `sampling_event` ENABLE KEYS */; |
| 688 | 712 |
|
| 689 | 713 |
-- |
| 690 |
-- Table structure for table `scrubbed_geoplace` |
|
| 691 |
-- |
|
| 692 |
|
|
| 693 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 694 |
/*!40101 SET character_set_client = utf8 */; |
|
| 695 |
CREATE TABLE `scrubbed_geoplace` ( |
|
| 696 |
`id` varbinary(767) NOT NULL, |
|
| 697 |
`parent_geoplace` varbinary(767) DEFAULT NULL, |
|
| 698 |
`scrubbed_name` varbinary(767) DEFAULT NULL, |
|
| 699 |
PRIMARY KEY (`id`), |
|
| 700 |
KEY `fk_geoplace_geoplace1_idx` (`parent_geoplace`), |
|
| 701 |
CONSTRAINT `fk_geoplace_geoplace1` FOREIGN KEY (`parent_geoplace`) REFERENCES `scrubbed_geoplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 702 |
CONSTRAINT `fk_nested_geoplace_geoplace1` FOREIGN KEY (`id`) REFERENCES `geoplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 703 |
) 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)'; |
|
| 704 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 705 |
|
|
| 706 |
-- |
|
| 707 |
-- Dumping data for table `scrubbed_geoplace` |
|
| 708 |
-- |
|
| 709 |
|
|
| 710 |
/*!40000 ALTER TABLE `scrubbed_geoplace` DISABLE KEYS */; |
|
| 711 |
/*!40000 ALTER TABLE `scrubbed_geoplace` ENABLE KEYS */; |
|
| 712 |
|
|
| 713 |
-- |
|
| 714 | 714 |
-- Table structure for table `soil_observation` |
| 715 | 715 |
-- |
| 716 | 716 |
|
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.