Revision 13903
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/schemas/util.sql | ||
|---|---|---|
| 2380 | 2380 |
|
| 2381 | 2381 |
|
| 2382 | 2382 |
-- |
| 2383 |
-- Name: lat_long_in_new_world(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
|
|
| 2383 |
-- Name: lat_long_in_new_world(geocoord); Type: FUNCTION; Schema: util; Owner: -
|
|
| 2384 | 2384 |
-- |
| 2385 | 2385 |
|
| 2386 |
CREATE FUNCTION lat_long_in_new_world(latitude_deg double precision, longitude_deg double precision) RETURNS boolean
|
|
| 2386 |
CREATE FUNCTION lat_long_in_new_world(point geocoord) RETURNS boolean
|
|
| 2387 | 2387 |
LANGUAGE sql IMMUTABLE |
| 2388 | 2388 |
AS $_$ |
| 2389 | 2389 |
/* use function rather than operator+search_path to allow inlining, which |
| 2390 | 2390 |
enables util.new_world() to only be evaluated once */ |
| 2391 |
SELECT util.contained_within_approx(($1, $2), util.new_world())
|
|
| 2391 |
SELECT util.contained_within_approx($1, util.new_world())
|
|
| 2392 | 2392 |
$_$; |
| 2393 | 2393 |
|
| 2394 | 2394 |
|
| 2395 | 2395 |
-- |
| 2396 |
-- Name: FUNCTION lat_long_in_new_world(latitude_deg double precision, longitude_deg double precision); Type: COMMENT; Schema: util; Owner: -
|
|
| 2396 |
-- Name: FUNCTION lat_long_in_new_world(point geocoord); Type: COMMENT; Schema: util; Owner: -
|
|
| 2397 | 2397 |
-- |
| 2398 | 2398 |
|
| 2399 |
COMMENT ON FUNCTION lat_long_in_new_world(latitude_deg double precision, longitude_deg double precision) IS '
|
|
| 2399 |
COMMENT ON FUNCTION lat_long_in_new_world(point geocoord) IS '
|
|
| 2400 | 2400 |
**WARNING**: this includes false positives above and below the New World |
| 2401 | 2401 |
bounding box, as described in util.bounding_box() |
| 2402 | 2402 |
'; |
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 1360 | 1360 |
|
| 1361 | 1361 |
|
| 1362 | 1362 |
-- |
| 1363 |
-- Name: lat_long_in_new_world(double, double); Type: FUNCTION; Schema: public; Owner: -
|
|
| 1363 |
-- Name: lat_long_in_new_world(util.geocoord); Type: FUNCTION; Schema: public; Owner: -
|
|
| 1364 | 1364 |
-- |
| 1365 | 1365 |
|
| 1366 | 1366 |
|
| 1367 | 1367 |
|
| 1368 | 1368 |
|
| 1369 | 1369 |
-- |
| 1370 |
-- Name: FUNCTION lat_long_in_new_world(latitude_deg double, longitude_deg double); Type: COMMENT; Schema: public; Owner: -
|
|
| 1370 |
-- Name: FUNCTION lat_long_in_new_world(point util.geocoord); Type: COMMENT; Schema: public; Owner: -
|
|
| 1371 | 1371 |
-- |
| 1372 | 1372 |
|
| 1373 | 1373 |
|
| trunk/schemas/public_.sql | ||
|---|---|---|
| 2182 | 2182 |
|
| 2183 | 2183 |
|
| 2184 | 2184 |
-- |
| 2185 |
-- Name: lat_long_in_new_world(double precision, double precision); Type: FUNCTION; Schema: public; Owner: -
|
|
| 2185 |
-- Name: lat_long_in_new_world(util.geocoord); Type: FUNCTION; Schema: public; Owner: -
|
|
| 2186 | 2186 |
-- |
| 2187 | 2187 |
|
| 2188 |
CREATE FUNCTION lat_long_in_new_world(latitude_deg double precision, longitude_deg double precision) RETURNS boolean
|
|
| 2188 |
CREATE FUNCTION lat_long_in_new_world(point util.geocoord) RETURNS boolean
|
|
| 2189 | 2189 |
LANGUAGE sql IMMUTABLE |
| 2190 | 2190 |
AS $_$ |
| 2191 |
SELECT util.lat_long_in_new_world($1, $2)
|
|
| 2191 |
SELECT util.lat_long_in_new_world($1) |
|
| 2192 | 2192 |
$_$; |
| 2193 | 2193 |
|
| 2194 | 2194 |
|
| 2195 | 2195 |
-- |
| 2196 |
-- Name: FUNCTION lat_long_in_new_world(latitude_deg double precision, longitude_deg double precision); Type: COMMENT; Schema: public; Owner: -
|
|
| 2196 |
-- Name: FUNCTION lat_long_in_new_world(point util.geocoord); Type: COMMENT; Schema: public; Owner: -
|
|
| 2197 | 2197 |
-- |
| 2198 | 2198 |
|
| 2199 |
COMMENT ON FUNCTION lat_long_in_new_world(latitude_deg double precision, longitude_deg double precision) IS '
|
|
| 2199 |
COMMENT ON FUNCTION lat_long_in_new_world(point util.geocoord) IS '
|
|
| 2200 | 2200 |
wrapper that prevents views from getting dropped when the util schema is reinstalled |
| 2201 | 2201 |
'; |
| 2202 | 2202 |
|
| ... | ... | |
| 6047 | 6047 |
analytical_stem.scrubbed_species_binomial AS "speciesBinomial", |
| 6048 | 6048 |
analytical_stem.scrubbed_taxon_name_with_author AS "scientificName" |
| 6049 | 6049 |
FROM analytical_stem |
| 6050 |
WHERE ((((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND (COALESCE((analytical_stem."isNewWorld_bien")::boolean, false) OR lat_long_in_new_world(analytical_stem."decimalLatitude__@DwC__@vegpath.org", analytical_stem."decimalLongitude__@DwC__@vegpath.org"))) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem.scrubbed_family IS NOT NULL)) AND (analytical_stem."decimalLatitude__@DwC__@vegpath.org" IS NOT NULL)) AND (analytical_stem."decimalLongitude__@DwC__@vegpath.org" IS NOT NULL)) AND COALESCE((analytical_stem."coordinateUncertaintyInMeters__@DwC__@vegpath.org" <= _km_to_m((10)::double precision)), true));
|
|
| 6050 |
WHERE ((((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND (COALESCE((analytical_stem."isNewWorld_bien")::boolean, false) OR lat_long_in_new_world(ROW(analytical_stem."decimalLatitude__@DwC__@vegpath.org", analytical_stem."decimalLongitude__@DwC__@vegpath.org")))) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem.scrubbed_family IS NOT NULL)) AND (analytical_stem."decimalLatitude__@DwC__@vegpath.org" IS NOT NULL)) AND (analytical_stem."decimalLongitude__@DwC__@vegpath.org" IS NOT NULL)) AND COALESCE((analytical_stem."coordinateUncertaintyInMeters__@DwC__@vegpath.org" <= _km_to_m((10)::double precision)), true));
|
|
| 6051 | 6051 |
|
| 6052 | 6052 |
|
| 6053 | 6053 |
-- |
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 2182 | 2182 |
|
| 2183 | 2183 |
|
| 2184 | 2184 |
-- |
| 2185 |
-- Name: lat_long_in_new_world(double precision, double precision); Type: FUNCTION; Schema: public; Owner: -
|
|
| 2185 |
-- Name: lat_long_in_new_world(util.geocoord); Type: FUNCTION; Schema: public; Owner: -
|
|
| 2186 | 2186 |
-- |
| 2187 | 2187 |
|
| 2188 |
CREATE FUNCTION lat_long_in_new_world(latitude_deg double precision, longitude_deg double precision) RETURNS boolean
|
|
| 2188 |
CREATE FUNCTION lat_long_in_new_world(point util.geocoord) RETURNS boolean
|
|
| 2189 | 2189 |
LANGUAGE sql IMMUTABLE |
| 2190 | 2190 |
AS $_$ |
| 2191 |
SELECT util.lat_long_in_new_world($1, $2)
|
|
| 2191 |
SELECT util.lat_long_in_new_world($1) |
|
| 2192 | 2192 |
$_$; |
| 2193 | 2193 |
|
| 2194 | 2194 |
|
| 2195 | 2195 |
-- |
| 2196 |
-- Name: FUNCTION lat_long_in_new_world(latitude_deg double precision, longitude_deg double precision); Type: COMMENT; Schema: public; Owner: -
|
|
| 2196 |
-- Name: FUNCTION lat_long_in_new_world(point util.geocoord); Type: COMMENT; Schema: public; Owner: -
|
|
| 2197 | 2197 |
-- |
| 2198 | 2198 |
|
| 2199 |
COMMENT ON FUNCTION lat_long_in_new_world(latitude_deg double precision, longitude_deg double precision) IS '
|
|
| 2199 |
COMMENT ON FUNCTION lat_long_in_new_world(point util.geocoord) IS '
|
|
| 2200 | 2200 |
wrapper that prevents views from getting dropped when the util schema is reinstalled |
| 2201 | 2201 |
'; |
| 2202 | 2202 |
|
| ... | ... | |
| 6047 | 6047 |
analytical_stem.scrubbed_species_binomial AS "speciesBinomial", |
| 6048 | 6048 |
analytical_stem.scrubbed_taxon_name_with_author AS "scientificName" |
| 6049 | 6049 |
FROM analytical_stem |
| 6050 |
WHERE ((((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND (COALESCE((analytical_stem."isNewWorld_bien")::boolean, false) OR lat_long_in_new_world(analytical_stem."decimalLatitude__@DwC__@vegpath.org", analytical_stem."decimalLongitude__@DwC__@vegpath.org"))) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem.scrubbed_family IS NOT NULL)) AND (analytical_stem."decimalLatitude__@DwC__@vegpath.org" IS NOT NULL)) AND (analytical_stem."decimalLongitude__@DwC__@vegpath.org" IS NOT NULL)) AND COALESCE((analytical_stem."coordinateUncertaintyInMeters__@DwC__@vegpath.org" <= _km_to_m((10)::double precision)), true));
|
|
| 6050 |
WHERE ((((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND (COALESCE((analytical_stem."isNewWorld_bien")::boolean, false) OR lat_long_in_new_world(ROW(analytical_stem."decimalLatitude__@DwC__@vegpath.org", analytical_stem."decimalLongitude__@DwC__@vegpath.org")))) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem.scrubbed_family IS NOT NULL)) AND (analytical_stem."decimalLatitude__@DwC__@vegpath.org" IS NOT NULL)) AND (analytical_stem."decimalLongitude__@DwC__@vegpath.org" IS NOT NULL)) AND COALESCE((analytical_stem."coordinateUncertaintyInMeters__@DwC__@vegpath.org" <= _km_to_m((10)::double precision)), true));
|
|
| 6051 | 6051 |
|
| 6052 | 6052 |
|
| 6053 | 6053 |
-- |
Also available in: Unified diff
schemas/util.sql: lat_long_in_new_world(): take a geocoord param instead of separate lat/long params