Revision 4954
Added by Aaron Marcuse-Kubitza about 12 years ago
| vegbien.sql | ||
|---|---|---|
| 98 | 98 |
|
| 99 | 99 |
|
| 100 | 100 |
-- |
| 101 |
-- Name: rankedplacename; Type: TYPE; Schema: public; Owner: - |
|
| 102 |
-- |
|
| 103 |
|
|
| 104 |
CREATE TYPE rankedplacename AS ( |
|
| 105 |
rank placerank, |
|
| 106 |
verbatimrank text, |
|
| 107 |
placename text |
|
| 108 |
); |
|
| 109 |
|
|
| 110 |
|
|
| 111 |
-- |
|
| 101 | 112 |
-- Name: taxonrank; Type: TYPE; Schema: public; Owner: - |
| 102 | 113 |
-- |
| 103 | 114 |
|
| ... | ... | |
| 945 | 956 |
stateprovince text, |
| 946 | 957 |
county text, |
| 947 | 958 |
municipality text, |
| 948 |
site text |
|
| 959 |
site text, |
|
| 960 |
otherranks rankedplacename[] |
|
| 949 | 961 |
); |
| 950 | 962 |
|
| 951 | 963 |
|
| ... | ... | |
| 953 | 965 |
-- Name: TABLE placepath; Type: COMMENT; Schema: public; Owner: - |
| 954 | 966 |
-- |
| 955 | 967 |
|
| 956 |
COMMENT ON TABLE placepath IS 'To include a place at a rank with no explicit column, create a place for it and point to it using place_id. To include multiple such places, chain the places together using parent_id, as a form of ordered linked list. Note that lower-level places should point to higher-level places.';
|
|
| 968 |
COMMENT ON TABLE placepath IS 'To include a placename at a rank with no explicit column, add it to the otherranks array.';
|
|
| 957 | 969 |
|
| 958 | 970 |
|
| 959 | 971 |
-- |
| 972 |
-- Name: COLUMN placepath.otherranks; Type: COMMENT; Schema: public; Owner: - |
|
| 973 |
-- |
|
| 974 |
|
|
| 975 |
COMMENT ON COLUMN placepath.otherranks IS 'Put ranks in path order, so that lower-level places come after higher-level places.'; |
|
| 976 |
|
|
| 977 |
|
|
| 978 |
-- |
|
| 960 | 979 |
-- Name: plantobservation; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 961 | 980 |
-- |
| 962 | 981 |
|
| ... | ... | |
| 4500 | 4519 |
-- Name: placepath_unique_within_datasource_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4501 | 4520 |
-- |
| 4502 | 4521 |
|
| 4503 |
CREATE UNIQUE INDEX placepath_unique_within_datasource_by_name ON placepath USING btree (datasource_id, (COALESCE(place_id, 2147483647)), (COALESCE(continent, '\\N'::text)), (COALESCE(country, '\\N'::text)), (COALESCE(stateprovince, '\\N'::text)), (COALESCE(county, '\\N'::text)), (COALESCE(municipality, '\\N'::text)), (COALESCE(site, '\\N'::text))) WHERE (placecode IS NULL); |
|
| 4522 |
CREATE UNIQUE INDEX placepath_unique_within_datasource_by_name ON placepath USING btree (datasource_id, (COALESCE(place_id, 2147483647)), (COALESCE(continent, '\\N'::text)), (COALESCE(country, '\\N'::text)), (COALESCE(stateprovince, '\\N'::text)), (COALESCE(county, '\\N'::text)), (COALESCE(municipality, '\\N'::text)), (COALESCE(site, '\\N'::text)), (COALESCE(otherranks, ARRAY[]::rankedplacename[]))) WHERE (placecode IS NULL);
|
|
| 4504 | 4523 |
|
| 4505 | 4524 |
|
| 4506 | 4525 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: placepath: Added otherranks field, analogous to taxonpath.otherranks