Revision 11907
Added by Aaron Marcuse-Kubitza about 11 years ago
| vegbien.sql | ||
|---|---|---|
| 877 | 877 |
|
| 878 | 878 |
|
| 879 | 879 |
-- |
| 880 |
-- Name: locationplace__set_location_current_place(); Type: FUNCTION; Schema: public; Owner: - |
|
| 881 |
-- |
|
| 882 |
|
|
| 883 |
CREATE FUNCTION locationplace__set_location_current_place() RETURNS trigger |
|
| 884 |
LANGUAGE plpgsql |
|
| 885 |
AS $$ |
|
| 886 |
BEGIN |
|
| 887 |
UPDATE location SET place_id = new.place_id |
|
| 888 |
WHERE location_id = new.location_id; |
|
| 889 |
|
|
| 890 |
RETURN new; |
|
| 891 |
END; |
|
| 892 |
$$; |
|
| 893 |
|
|
| 894 |
|
|
| 895 |
-- |
|
| 880 | 896 |
-- Name: make_family_higher_plant_group(); Type: FUNCTION; Schema: public; Owner: - |
| 881 | 897 |
-- |
| 882 | 898 |
|
| ... | ... | |
| 2051 | 2067 |
sourceaccessioncode text, |
| 2052 | 2068 |
parent_id integer, |
| 2053 | 2069 |
authorlocationcode text, |
| 2070 |
place_id integer, |
|
| 2054 | 2071 |
accesslevel accesslevel, |
| 2055 | 2072 |
accessconditions text, |
| 2056 | 2073 |
sublocationxposition_m double precision, |
| ... | ... | |
| 2194 | 2211 |
|
| 2195 | 2212 |
|
| 2196 | 2213 |
-- |
| 2197 |
-- Name: locationplace; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2198 |
-- |
|
| 2199 |
|
|
| 2200 |
CREATE TABLE locationplace ( |
|
| 2201 |
locationplace_id integer NOT NULL, |
|
| 2202 |
location_id integer NOT NULL, |
|
| 2203 |
place_id integer NOT NULL, |
|
| 2204 |
identifier_id integer |
|
| 2205 |
); |
|
| 2206 |
|
|
| 2207 |
|
|
| 2208 |
-- |
|
| 2209 |
-- Name: TABLE locationplace; Type: COMMENT; Schema: public; Owner: - |
|
| 2210 |
-- |
|
| 2211 |
|
|
| 2212 |
COMMENT ON TABLE locationplace IS 'Equivalent to VegBank''s place table.'; |
|
| 2213 |
|
|
| 2214 |
|
|
| 2215 |
-- |
|
| 2216 | 2214 |
-- Name: method; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2217 | 2215 |
-- |
| 2218 | 2216 |
|
| ... | ... | |
| 2545 | 2543 |
stratum.stratumname AS stratum__name, |
| 2546 | 2544 |
COALESCE(locationevent__communities__array(locationevent.locationevent_id), locationevent__communities__array(parent_event.locationevent_id)) AS communities, |
| 2547 | 2545 |
COALESCE(locationevent__contributors__array(locationevent.locationevent_id), locationevent__contributors__array(parent_event.locationevent_id)) AS plot__collectors |
| 2548 |
FROM ((((((((((((((source
|
|
| 2546 |
FROM (((((((((((((source |
|
| 2549 | 2547 |
JOIN location USING (source_id)) |
| 2550 | 2548 |
LEFT JOIN locationevent USING (location_id)) |
| 2549 |
LEFT JOIN place USING (place_id)) |
|
| 2551 | 2550 |
LEFT JOIN location parent_location ON ((parent_location.location_id = location.parent_id))) |
| 2552 |
LEFT JOIN locationplace ON ((locationplace.location_id = COALESCE(parent_location.location_id, location.location_id)))) |
|
| 2553 |
LEFT JOIN place USING (place_id)) |
|
| 2554 | 2551 |
LEFT JOIN coordinates USING (coordinates_id)) |
| 2555 | 2552 |
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])))) |
| 2556 | 2553 |
LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."*GADM country" = COALESCE(geoscrub_output."acceptedCountry", place.country)))) |
| ... | ... | |
| 3619 | 3616 |
|
| 3620 | 3617 |
|
| 3621 | 3618 |
-- |
| 3619 |
-- Name: locationplace; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 3620 |
-- |
|
| 3621 |
|
|
| 3622 |
CREATE TABLE locationplace ( |
|
| 3623 |
locationplace_id integer NOT NULL, |
|
| 3624 |
location_id integer NOT NULL, |
|
| 3625 |
place_id integer NOT NULL, |
|
| 3626 |
identifier_id integer |
|
| 3627 |
); |
|
| 3628 |
|
|
| 3629 |
|
|
| 3630 |
-- |
|
| 3631 |
-- Name: TABLE locationplace; Type: COMMENT; Schema: public; Owner: - |
|
| 3632 |
-- |
|
| 3633 |
|
|
| 3634 |
COMMENT ON TABLE locationplace IS 'Equivalent to VegBank''s place table.'; |
|
| 3635 |
|
|
| 3636 |
|
|
| 3637 |
-- |
|
| 3622 | 3638 |
-- Name: locationplace_locationplace_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| 3623 | 3639 |
-- |
| 3624 | 3640 |
|
| ... | ... | |
| 8551 | 8567 |
|
| 8552 | 8568 |
|
| 8553 | 8569 |
-- |
| 8570 |
-- Name: fki_location_place_id_fkey; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 8571 |
-- |
|
| 8572 |
|
|
| 8573 |
CREATE INDEX fki_location_place_id_fkey ON location USING btree (place_id); |
|
| 8574 |
|
|
| 8575 |
|
|
| 8576 |
-- |
|
| 8554 | 8577 |
-- Name: graphic_locationevent_id_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 8555 | 8578 |
-- |
| 8556 | 8579 |
|
| ... | ... | |
| 9391 | 9414 |
|
| 9392 | 9415 |
|
| 9393 | 9416 |
-- |
| 9417 |
-- Name: locationplace__set_location_current_place; Type: TRIGGER; Schema: public; Owner: - |
|
| 9418 |
-- |
|
| 9419 |
|
|
| 9420 |
CREATE TRIGGER locationplace__set_location_current_place AFTER INSERT ON locationplace FOR EACH ROW EXECUTE PROCEDURE locationplace__set_location_current_place(); |
|
| 9421 |
|
|
| 9422 |
|
|
| 9423 |
-- |
|
| 9394 | 9424 |
-- Name: party_source_id_self_ref; Type: TRIGGER; Schema: public; Owner: - |
| 9395 | 9425 |
-- |
| 9396 | 9426 |
|
| ... | ... | |
| 9792 | 9822 |
|
| 9793 | 9823 |
|
| 9794 | 9824 |
-- |
| 9825 |
-- Name: location_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 9826 |
-- |
|
| 9827 |
|
|
| 9828 |
ALTER TABLE ONLY location |
|
| 9829 |
ADD CONSTRAINT location_place_id_fkey FOREIGN KEY (place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 9830 |
|
|
| 9831 |
|
|
| 9832 |
-- |
|
| 9795 | 9833 |
-- Name: location_source_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 9796 | 9834 |
-- |
| 9797 | 9835 |
|
| ... | ... | |
| 10715 | 10753 |
|
| 10716 | 10754 |
|
| 10717 | 10755 |
-- |
| 10718 |
-- Name: locationplace; Type: ACL; Schema: public; Owner: - |
|
| 10719 |
-- |
|
| 10720 |
|
|
| 10721 |
REVOKE ALL ON TABLE locationplace FROM PUBLIC; |
|
| 10722 |
REVOKE ALL ON TABLE locationplace FROM bien; |
|
| 10723 |
GRANT ALL ON TABLE locationplace TO bien; |
|
| 10724 |
GRANT SELECT ON TABLE locationplace TO bien_read; |
|
| 10725 |
|
|
| 10726 |
|
|
| 10727 |
-- |
|
| 10728 | 10756 |
-- Name: method; Type: ACL; Schema: public; Owner: - |
| 10729 | 10757 |
-- |
| 10730 | 10758 |
|
| ... | ... | |
| 11077 | 11105 |
|
| 11078 | 11106 |
|
| 11079 | 11107 |
-- |
| 11108 |
-- Name: locationplace; Type: ACL; Schema: public; Owner: - |
|
| 11109 |
-- |
|
| 11110 |
|
|
| 11111 |
REVOKE ALL ON TABLE locationplace FROM PUBLIC; |
|
| 11112 |
REVOKE ALL ON TABLE locationplace FROM bien; |
|
| 11113 |
GRANT ALL ON TABLE locationplace TO bien; |
|
| 11114 |
GRANT SELECT ON TABLE locationplace TO bien_read; |
|
| 11115 |
|
|
| 11116 |
|
|
| 11117 |
-- |
|
| 11080 | 11118 |
-- Name: methodtaxonclass; Type: ACL; Schema: public; Owner: - |
| 11081 | 11119 |
-- |
| 11082 | 11120 |
|
Also available in: Unified diff
bugfix: schemas/vegbien.sql: location: added place_id which is autopopulated from the current locationplace. join on this in plot.**, to avoid a 1:many join when a location has multiple locationplaces.