Revision 11907
Added by Aaron Marcuse-Kubitza almost 11 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 451 | 451 |
|
| 452 | 452 |
|
| 453 | 453 |
-- |
| 454 |
-- Name: locationplace__set_location_current_place(); Type: FUNCTION; Schema: public; Owner: - |
|
| 455 |
-- |
|
| 456 |
|
|
| 457 |
|
|
| 458 |
|
|
| 459 |
|
|
| 460 |
-- |
|
| 454 | 461 |
-- Name: make_family_higher_plant_group(); Type: FUNCTION; Schema: public; Owner: - |
| 455 | 462 |
-- |
| 456 | 463 |
|
| ... | ... | |
| 1040 | 1047 |
sourceaccessioncode varchar(255), |
| 1041 | 1048 |
parent_id int(11), |
| 1042 | 1049 |
authorlocationcode varchar(255), |
| 1050 |
place_id int(11), |
|
| 1043 | 1051 |
accesslevel varchar(255), |
| 1044 | 1052 |
accessconditions varchar(255), |
| 1045 | 1053 |
sublocationxposition_m double precision, |
| ... | ... | |
| 1181 | 1189 |
|
| 1182 | 1190 |
|
| 1183 | 1191 |
-- |
| 1184 |
-- Name: locationplace; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1185 |
-- |
|
| 1186 |
|
|
| 1187 |
CREATE TABLE locationplace ( |
|
| 1188 |
locationplace_id int(11) NOT NULL, |
|
| 1189 |
location_id int(11) NOT NULL, |
|
| 1190 |
place_id int(11) NOT NULL, |
|
| 1191 |
identifier_id int(11) |
|
| 1192 |
); |
|
| 1193 |
|
|
| 1194 |
|
|
| 1195 |
-- |
|
| 1196 |
-- Name: TABLE locationplace; Type: COMMENT; Schema: public; Owner: - |
|
| 1197 |
-- |
|
| 1198 |
|
|
| 1199 |
|
|
| 1200 |
|
|
| 1201 |
|
|
| 1202 |
-- |
|
| 1203 | 1192 |
-- Name: method; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1204 | 1193 |
-- |
| 1205 | 1194 |
|
| ... | ... | |
| 2296 | 2285 |
|
| 2297 | 2286 |
|
| 2298 | 2287 |
-- |
| 2288 |
-- Name: locationplace; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2289 |
-- |
|
| 2290 |
|
|
| 2291 |
CREATE TABLE locationplace ( |
|
| 2292 |
locationplace_id int(11) NOT NULL, |
|
| 2293 |
location_id int(11) NOT NULL, |
|
| 2294 |
place_id int(11) NOT NULL, |
|
| 2295 |
identifier_id int(11) |
|
| 2296 |
); |
|
| 2297 |
|
|
| 2298 |
|
|
| 2299 |
-- |
|
| 2300 |
-- Name: TABLE locationplace; Type: COMMENT; Schema: public; Owner: - |
|
| 2301 |
-- |
|
| 2302 |
|
|
| 2303 |
|
|
| 2304 |
|
|
| 2305 |
|
|
| 2306 |
-- |
|
| 2299 | 2307 |
-- Name: locationplace_locationplace_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| 2300 | 2308 |
-- |
| 2301 | 2309 |
|
| ... | ... | |
| 6968 | 6976 |
|
| 6969 | 6977 |
|
| 6970 | 6978 |
-- |
| 6979 |
-- Name: fki_location_place_id_fkey; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 6980 |
-- |
|
| 6981 |
|
|
| 6982 |
CREATE INDEX fki_location_place_id_fkey ON location (place_id); |
|
| 6983 |
|
|
| 6984 |
|
|
| 6985 |
-- |
|
| 6971 | 6986 |
-- Name: graphic_locationevent_id_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 6972 | 6987 |
-- |
| 6973 | 6988 |
|
| ... | ... | |
| 7808 | 7823 |
|
| 7809 | 7824 |
|
| 7810 | 7825 |
-- |
| 7826 |
-- Name: locationplace__set_location_current_place; Type: TRIGGER; Schema: public; Owner: - |
|
| 7827 |
-- |
|
| 7828 |
|
|
| 7829 |
|
|
| 7830 |
|
|
| 7831 |
|
|
| 7832 |
-- |
|
| 7811 | 7833 |
-- Name: party_source_id_self_ref; Type: TRIGGER; Schema: public; Owner: - |
| 7812 | 7834 |
-- |
| 7813 | 7835 |
|
| ... | ... | |
| 8193 | 8215 |
|
| 8194 | 8216 |
|
| 8195 | 8217 |
-- |
| 8218 |
-- Name: location_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 8219 |
-- |
|
| 8220 |
|
|
| 8221 |
ALTER TABLE location |
|
| 8222 |
ADD CONSTRAINT location_place_id_fkey FOREIGN KEY (place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 8223 |
|
|
| 8224 |
|
|
| 8225 |
-- |
|
| 8196 | 8226 |
-- Name: location_source_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 8197 | 8227 |
-- |
| 8198 | 8228 |
|
| ... | ... | |
| 9080 | 9110 |
|
| 9081 | 9111 |
|
| 9082 | 9112 |
-- |
| 9083 |
-- Name: locationplace; Type: ACL; Schema: public; Owner: - |
|
| 9084 |
-- |
|
| 9085 |
|
|
| 9086 |
|
|
| 9087 |
|
|
| 9088 |
|
|
| 9089 |
|
|
| 9090 |
|
|
| 9091 |
|
|
| 9092 |
-- |
|
| 9093 | 9113 |
-- Name: method; Type: ACL; Schema: public; Owner: - |
| 9094 | 9114 |
-- |
| 9095 | 9115 |
|
| ... | ... | |
| 9442 | 9462 |
|
| 9443 | 9463 |
|
| 9444 | 9464 |
-- |
| 9465 |
-- Name: locationplace; Type: ACL; Schema: public; Owner: - |
|
| 9466 |
-- |
|
| 9467 |
|
|
| 9468 |
|
|
| 9469 |
|
|
| 9470 |
|
|
| 9471 |
|
|
| 9472 |
|
|
| 9473 |
|
|
| 9474 |
-- |
|
| 9445 | 9475 |
-- Name: methodtaxonclass; Type: ACL; Schema: public; Owner: - |
| 9446 | 9476 |
-- |
| 9447 | 9477 |
|
| schemas/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.