Revision 11874
Added by Aaron Marcuse-Kubitza about 11 years ago
| schemas/vegbien.sql | ||
|---|---|---|
| 2497 | 2497 |
location.locationnarrative AS locality, |
| 2498 | 2498 |
CASE |
| 2499 | 2499 |
WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.latitude_deg |
| 2500 |
ELSE county_centroids.latitude
|
|
| 2500 |
ELSE county_centroids."decimalLatitude"
|
|
| 2501 | 2501 |
END AS "decimalLatitude", |
| 2502 | 2502 |
CASE |
| 2503 | 2503 |
WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.longitude_deg |
| 2504 |
ELSE county_centroids.longitude
|
|
| 2504 |
ELSE county_centroids."decimalLongitude"
|
|
| 2505 | 2505 |
END AS "decimalLongitude", |
| 2506 | 2506 |
CASE |
| 2507 | 2507 |
WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.coordsaccuracy_m |
| 2508 |
ELSE _km_to_m(county_centroids.error_km)
|
|
| 2508 |
ELSE _km_to_m(county_centroids."*error_km")
|
|
| 2509 | 2509 |
END AS "coordinateUncertaintyInMeters", |
| 2510 | 2510 |
CASE |
| 2511 | 2511 |
WHEN (coordinates.latitude_deg IS NOT NULL) THEN 'source data'::coordinatesource |
| 2512 |
WHEN (county_centroids.row_num IS NOT NULL) THEN 'georeferencing'::coordinatesource
|
|
| 2512 |
WHEN (county_centroids."*row_num" IS NOT NULL) THEN 'georeferencing'::coordinatesource
|
|
| 2513 | 2513 |
ELSE NULL::coordinatesource |
| 2514 | 2514 |
END AS "coordinateSource_bien", |
| 2515 | 2515 |
CASE |
| 2516 | 2516 |
WHEN (coordinates.latitude_deg IS NOT NULL) THEN NULL::text |
| 2517 |
WHEN (county_centroids.row_num IS NOT NULL) THEN 'county centroid'::text
|
|
| 2517 |
WHEN (county_centroids."*row_num" IS NOT NULL) THEN 'county centroid'::text
|
|
| 2518 | 2518 |
ELSE NULL::text |
| 2519 | 2519 |
END AS "georeferenceProtocol_bien", |
| 2520 | 2520 |
(geoscrub_output.geovalid)::integer AS geovalid_bien, |
| ... | ... | |
| 2549 | 2549 |
LEFT JOIN geoscrub.geoscrub_output ON ((((((ARRAY[geoscrub_output."decimalLatitude"] = ARRAY[coordinates.latitude_deg]) AND (ARRAY[geoscrub_output."decimalLongitude"] = ARRAY[coordinates.longitude_deg])) AND (ARRAY[geoscrub_output.country] = ARRAY[place.country])) AND (ARRAY[geoscrub_output."stateProvince"] = ARRAY[place.stateprovince])) AND (ARRAY[geoscrub_output.county] = ARRAY[place.county])))) |
| 2550 | 2550 |
LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."*GADM country" = COALESCE(geoscrub_output."acceptedCountry", place.country)))) |
| 2551 | 2551 |
LEFT JOIN "newWorld"."newWorldCountries" ON (("newWorldCountries"."*isoCode" = iso_code_gadm."*2-digit iso code")))
|
| 2552 |
LEFT JOIN geoscrub.county_centroids ON ((((place.country = 'United States'::text) AND (county_centroids.state = COALESCE(geoscrub_output."acceptedStateProvince", place.stateprovince))) AND (county_centroids.county = COALESCE(geoscrub_output."acceptedCounty", place.county)))))
|
|
| 2552 |
LEFT JOIN geoscrub.county_centroids ON ((((place.country = 'United States'::text) AND (county_centroids."stateProvince" = COALESCE(geoscrub_output."acceptedStateProvince", place.stateprovince))) AND (county_centroids.county = COALESCE(geoscrub_output."acceptedCounty", place.county)))))
|
|
| 2553 | 2553 |
LEFT JOIN locationevent parent_event ON ((parent_event.locationevent_id = locationevent.parent_id))) |
| 2554 | 2554 |
LEFT JOIN project ON ((project.project_id = COALESCE(locationevent.project_id, parent_event.project_id)))) |
| 2555 | 2555 |
LEFT JOIN stratum ON ((stratum.stratum_id = COALESCE(locationevent.stratum_id, parent_event.stratum_id)))) |
Also available in: Unified diff
bugfix: schemas/vegbien.sql: updated for renamed county_centroids column names