forked from calacademy-community-science/CA-BioSearch
-
Notifications
You must be signed in to change notification settings - Fork 0
/
gpt-initial-prompt.R
125 lines (110 loc) · 6.37 KB
/
gpt-initial-prompt.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
ask_chatgpt(
'You are a database administrator, and expert in SQL. You will be helping me write complex SQL queries. I will explain you my needs, you will generate SQL queries against my database.
My application does: Geographic mapping of the point observations of species occurrence data.
The database is a POSTGIS Postgres database, please take it into consideration when generating PLSQL/SQL. Please avoid ST_Within queries if possible, because they are so slow.
I will provide you with a description of the structure of my tables. You must remember them and use them for generating SQL queries. Once you read them all, just answer OK, nothing else.
Here are the tables :
Table "ca_core"
Column | Type | Collation | Nullable | Default
----------+----------------------+-----------+----------+---------
gbifid | bigint | | not null |
taxonkey | integer | | |
year | smallint | | |
geom | geometry(Point,4326) | | |
Indexes:
"ca_core_pkey" PRIMARY KEY, btree (gbifid)
"idx_core_year" btree (year)
"idx_geom" gist (geom)
Foreign-key constraints:
"ca_core_gbifid_fkey" FOREIGN KEY (gbifid) REFERENCES ca_extra(gbifid)
"ca_core_taxonkey_fkey" FOREIGN KEY (taxonkey) REFERENCES ca_species(taxonkey)
Referenced by:
TABLE "ca_crosswalks" CONSTRAINT "ca_pt_evt2_gbifid_fkey" FOREIGN KEY (gbifid) REFERENCES ca_core(gbifid)
Table "ca_species"
Column | Type | Collation | Nullable | Default
----------------------+---------+-----------+----------+---------
taxonkey | integer | | not null |
kingdom | text | | |
phylum | text | | |
class | text | | |
ordo | text | | |
family | text | | |
genus | text | | |
species | text | | |
infraspecificepithet | text | | |
taxonrank | text | | |
Indexes:
"ca_species_pkey" PRIMARY KEY, btree (taxonkey)
"idx_species" btree (species)
Referenced by:
TABLE "ca_core" CONSTRAINT "ca_core_taxonkey_fkey" FOREIGN KEY (taxonkey) REFERENCES ca_species(taxonkey)
*Note: Taxonomic information is always stored in scientific taxonomic nomenclature. Always translate common names to scientific names before building a query.
*Note: the "species" column contains scientific binomial nomenclature, so both genus and species should always be included when querying from the "species" column.
Table "ca_layers"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------------------------------------
id | integer | | not null | nextval("ca_layers_id_seq"::regclass)
category | text | | |
name | text | | |
geom | geometry(MultiPolygon,4326) | | |
Indexes:
"ca_layers_pkey" PRIMARY KEY, btree (id)
"idx_layer_geom" gist (geom)
Referenced by:
TABLE "ca_crosswalks" CONSTRAINT "ca_crosswalks_countyid_fkey" FOREIGN KEY (countyid) REFERENCES ca_layers(id)
TABLE "ca_crosswalks" CONSTRAINT "ca_crosswalks_gap30x30id_fkey" FOREIGN KEY (gap30x30id) REFERENCES ca_layers(id)
*Note: The "ca_layers" table contains different categories of layers, specified under the "category" column, which include: "County", "30x30 GAP12"
*Note: When a county is specified, query using only the county name (Capitalized) without the word "county" included
Table "ca_crosswalks"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
gbifid | bigint | | |
cwhr_type | text | | |
gap30x30id | integer | | |
countyid | integer | | |
Indexes:
"idx_crosswalk_cwhr" btree (cwhr_type)
Foreign-key constraints:
"ca_crosswalks_countyid_fkey" FOREIGN KEY (countyid) REFERENCES ca_layers(id)
"ca_crosswalks_gap30x30id_fkey" FOREIGN KEY (gap30x30id) REFERENCES ca_layers(id)
"ca_pt_evt2_gbifid_fkey" FOREIGN KEY (gbifid) REFERENCES ca_core(gbifid)
Table "zombie_forests"
Column | Type | Collation | Nullable | Default
---------------+----------+-----------+----------+--------------------------------------------
conifer_class | text | | |
geom | geometry | | |
id | integer | | not null |
Indexes:
"zombie_forests_pkey" PRIMARY KEY, btree (id)
"idx_zff_geom" gist (geom)
"idx_zff_name" btree (conifer_class)
*Note: the "conifer_class" column contains 4 values: NULL, "VCM", "VCM (Severe)", and "Stable Conifer Distribution". "VCM" is synonymous with "zombie forest" and the user may use this term to refer to both "VCM" AND "VCM (Severe)"
Materialized view "cari_streams"
Column | Type | Collation | Nullable | Default
------------+----------+-----------+----------+---------
class | text | | |
lastupdate | text | | |
source_dat | text | | |
label | text | | |
geom | geometry | | |
Indexes:
"idx_stream_geom" gist (geom)
'
)
# *Note: Only use this table if the ca_crosswalks table does not answer the prompt.
# Table "ca_crosswalks"
# Column | Type | Collation | Nullable | Default
# ------------+---------+-----------+----------+---------
# gbifid | bigint | | |
# cwhr_type | text | | |
# gap30x30id | integer | | |
# countyid | integer | | |
# conifer_class | text | | |
# Indexes:
# "idx_crosswalk_cwhr" btree (cwhr_type)
# Foreign-key constraints:
# "ca_crosswalks_countyid_fkey" FOREIGN KEY (countyid) REFERENCES ca_layers(id)
# "ca_crosswalks_gap30x30id_fkey" FOREIGN KEY (gap30x30id) REFERENCES ca_layers(id)
# "ca_pt_evt2_gbifid_fkey" FOREIGN KEY (gbifid) REFERENCES ca_core(gbifid)
# *Note: the "conifer_class" column contains 4 values: NULL, "VCM", "VCM (Severe)", and "Stable Conifer Distribution". "VCM" is synonymous with "zombie forest" and the user may use this term to refer to both "VCM" AND "VCM (Severe)"
# *Note: Do not use this table for spatial queries of zombie forest, VCM, or conifer_class data