Revision 13131
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 9354 | 9354 |
-- |
| 9355 | 9355 |
|
| 9356 | 9356 |
CREATE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS |
| 9357 |
SELECT ( SELECT count(*) AS count
|
|
| 9357 |
SELECT ( SELECT count(DISTINCT ROW(coordinates.latitude_deg, coordinates.longitude_deg, coordinates.verbatimlatitude, coordinates.verbatimlongitude)) AS count
|
|
| 9358 | 9358 |
FROM public.coordinates |
| 9359 | 9359 |
WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND ((coordinates.verbatimlatitude IS NOT NULL) OR (coordinates.latitude_deg IS NOT NULL)))) AS "allLats",
|
| 9360 |
( SELECT count(*) AS count
|
|
| 9360 |
( SELECT count(DISTINCT ROW(coordinates.latitude_deg, coordinates.longitude_deg, coordinates.verbatimlatitude, coordinates.verbatimlongitude)) AS count
|
|
| 9361 | 9361 |
FROM public.coordinates |
| 9362 | 9362 |
WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (coordinates.latitude_deg IS NOT NULL))) AS "decimalLats",
|
| 9363 |
( SELECT count(*) AS count
|
|
| 9363 |
( SELECT count(DISTINCT ROW(coordinates.latitude_deg, coordinates.longitude_deg, coordinates.verbatimlatitude, coordinates.verbatimlongitude)) AS count
|
|
| 9364 | 9364 |
FROM public.coordinates |
| 9365 | 9365 |
WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND ((coordinates.verbatimlongitude IS NOT NULL) OR (coordinates.longitude_deg IS NOT NULL)))) AS "allLongs",
|
| 9366 |
( SELECT count(*) AS count
|
|
| 9366 |
( SELECT count(DISTINCT ROW(coordinates.latitude_deg, coordinates.longitude_deg, coordinates.verbatimlatitude, coordinates.verbatimlongitude)) AS count
|
|
| 9367 | 9367 |
FROM public.coordinates |
| 9368 | 9368 |
WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (coordinates.longitude_deg IS NOT NULL))) AS "decimalLongs";
|
| 9369 | 9369 |
|
Also available in: Unified diff
fix: schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: need to DISTINCT the values that are being counted, because the coordinates_unique unique constraint includes other columns as well, so there may be multiple instances of each lat/long