PSQLQueryTool Glossary
(11 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
− | { | + | {{DavinciWiki_Procedure| |
− | + | In order to make the most of the MSFF PSQL Mars database, you must be familiar with the schema and table layout of the database. | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | In order to make the most of the MSFF PSQL Mars database, you must be familiar with the schema and table layout of the database | + | |
There are two sources for this information in the ''reference'' schema: | There are two sources for this information in the ''reference'' schema: | ||
− | # reference.pubmars_glossary | + | # '''reference.pubmars_glossary''' |
− | #: This is a VIEW based on joining several of the postgres control tables which define the actual Mars data tables; it includes details about all tables in the ''public, reference, thmpub'' schemas. Field descriptions will be null | + | #: - This is a VIEW based on joining several of the postgres control tables which define the actual Mars data tables; it includes details about all tables in the ''public, reference, thmpub'' schemas. Field descriptions will be null if the table author did not populate a descriptive comment about the column. |
− | #: ''Suggested selection-list fields'': schema_name, table_name, field_name, datatype_name, description | + | #: - ''Suggested selection-list fields'': schema_name, table_name, field_name, datatype_name, description |
− | # reference.glossary | + | # '''reference.glossary''' |
− | #: This is a hand-updated table describing all of the tables under the control of THEMIS Mission-Operations; it includes details about all the tables in the ''reference'' schema and all THEMIS schemas. The descriptions provided | + | #: This is a hand-updated table describing all of the tables under the control of THEMIS Mission-Operations; it includes details about all the tables in the ''reference'' schema and all THEMIS schemas. The descriptions provided in this table may be more verbose than those in the ''pubmars_glossary'' view |
− | #: ''Suggested selection-list fields:'' table_name, field_name, value_type, description | + | #: - ''Suggested selection-list fields:'' table_name, field_name, value_type, description |
− | #: ''Suggested constraints:'' schema_name='thmpub' | + | #: - ''Suggested constraints:'' schema_name='thmpub' |
− | The examples provided below | + | The examples provided below will help you explore the tables and columns available for your use in database queries. This is only intended to get you started; you should refine the ''constraints-list'' and the ''order-by-list'' according to your needs. |
− | + | | | |
− | + | ||
* Use the following commands in each of the following examples | * Use the following commands in each of the following examples | ||
− | Write the query; for more information see [[Writing_PSQL_DB_Queries|Writing PSQL-DB Queries]] | + | '''1) Write the query;''' for more information see [[Writing_PSQL_DB_Queries|Writing PSQL-DB Queries]] |
− | dv> query="select schema_name, table_name, field_name, datatype_name, description from reference.pubmars_glossary order by 1,2,3" | + | dv> query="select schema_name, table_name, field_name, datatype_name, description from\ |
+ | reference.pubmars_glossary order by 1,2,3" | ||
− | Access the PSQLQueryTool from Davinci; see the function page for a complete description of all available options | + | '''2) Access the PSQLQueryTool from Davinci;''' see the [[themis3db]] function page for a complete description of all available options |
dv> url=themis3db(query,psql=1,header=1) | dv> url=themis3db(query,psql=1,header=1) | ||
Using PSQL password ... | Using PSQL password ... | ||
Read TEXT file: 1 lines | Read TEXT file: 1 lines | ||
− | + | (A '''very''' long URL may be repeated to your screen; | |
+ | HINT: copy URL into your browser to preview results -and error messages- like [http://tiny.cc/2htdex this]) | ||
− | Read in the results | + | '''3) Read in the results;''' the two options shown here are most useful for these Glossary examples: |
− | : "read_lines" is better for queries that return fewer lines | + | : - "read_lines" is better for queries that return fewer lines |
− | : "copy" is better for queries that return hundreds of lines or verbose fields | + | : - "copy" is better for queries that return hundreds of lines or verbose fields |
dv> gloss=read_lines(url) | dv> gloss=read_lines(url) | ||
Downloading.done. | Downloading.done. | ||
Line 71: | Line 65: | ||
* Example-2: Explore the columns available in a single table | * Example-2: Explore the columns available in a single table | ||
− | dv> query="select schema_name, table_name, field_name, datatype_name, description from reference.pubmars_glossary where table_name='qubgeom' order by | + | dv> query="select schema_name, table_name, field_name, datatype_name, description from reference.pubmars_glossary |
− | dv> url=themis3db(query, | + | where table_name='qubgeom' order by field_name;" |
+ | dv> url=themis3db(query,psql=1,header=0,xformat=1) | ||
dv> qubgeom=read_lines(url) | dv> qubgeom=read_lines(url) | ||
Downloading.done. | Downloading.done. | ||
Line 99: | Line 94: | ||
+ | * Notes for THEMIS-Team Users | ||
+ | # If you are connecting to the PSQL database with the Team password, then you will be connected to the ''thmteam'' schema, not the ''thmpub'' schema | ||
+ | #: All tables within ''thmpub'' and ''thmteam'' are identical in layout, so the results returned from either glossary table/view will be valid. | ||
+ | #: However, if you need to specify a SCHEMA in a query (usually to avoid confusion), then use ''thmteam'' | ||
+ | # Table names in the ''thmpub/thmteam'' schema generally indicate the kind of information that is contained in the table: | ||
+ | #: Geometry Information Tables: qubgeom, frmgeom, projgeom, pgisgeom, and geomqlt | ||
+ | #: Processing and File Information Tables: status, imgproc, stage, imgidx, tlm | ||
+ | #: Derived Science Tables: irqubsci, irfrmsci, vissci, class, feature | ||
+ | #: MYSQL Equivalent Tables: thm3_header, thm3_quality, thm3_qube | ||
+ | # Primary keys are critical for joining tables; some tables have multiple primary keys (see ''reference.glossary.pkey=1'' for a complete list) | ||
+ | #: FILE_ID is used as the first PK in every table (except feature) | ||
+ | #: FRAMELET_ID is used in the *frm* and *qub* tables | ||
+ | #: BAND and/or BAND_IDX is used in qubgeom, frmgeom, ir*sci, and vissci | ||
+ | #: POINT_ID is used to identify the corners and center of images and/or framelets | ||
+ | # When querying for FRAMELET_ID=0, use the *qub* version of the table; it will significantly improve the speed of your results | ||
+ | | | ||
+ | *[[:category:String_Functions|General String Manipulation Functions]] | ||
+ | *[[themis3db]] | ||
+ | | | ||
+ | *[[Writing_PSQL_DB_Queries]] | ||
}} | }} | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
[[category:Procedures]] | [[category:Procedures]] | ||
− |
Latest revision as of 11:14, 16 April 2014
Contents: Description, Procedure, Functions Used, Related Functions
Description In order to make the most of the MSFF PSQL Mars database, you must be familiar with the schema and table layout of the database. There are two sources for this information in the reference schema:
The examples provided below will help you explore the tables and columns available for your use in database queries. This is only intended to get you started; you should refine the constraints-list and the order-by-list according to your needs.
Procedure
1) Write the query; for more information see Writing PSQL-DB Queries dv> query="select schema_name, table_name, field_name, datatype_name, description from\ reference.pubmars_glossary order by 1,2,3" 2) Access the PSQLQueryTool from Davinci; see the themis3db function page for a complete description of all available options dv> url=themis3db(query,psql=1,header=1) Using PSQL password ... Read TEXT file: 1 lines (A very long URL may be repeated to your screen; HINT: copy URL into your browser to preview results -and error messages- like this) 3) Read in the results; the two options shown here are most useful for these Glossary examples:
dv> gloss=read_lines(url) Downloading.done. Read TEXT file: 2665 lines Text Buffer with 2665 lines of text --OR-- dv> copy(url,$TMPDIR) Downloading.done. [path to file repeated to screen; review results outside of Davinci]
dv> query="select distinct table_name from reference.glossary where schema_name='thmpub' order by table_name;" dv> tablst=read_lines(url) Downloading.done. Read TEXT file: 19 lines Text Buffer with 19 lines of text 1: class 2: feature 3: frmgeom 4: geomqlt 5: imgidx 6: imgproc 7: irfrmsci 8: irqubsci 9: pgisgeom 10: projgeom 11: qubgeom 12: stage 13: status 14: themis_details 15: thm3_header 16: thm3_quality 17: thm3_qube 18: tlm 19: vissci
dv> query="select schema_name, table_name, field_name, datatype_name, description from reference.pubmars_glossary where table_name='qubgeom' order by field_name;" dv> url=themis3db(query,psql=1,header=0,xformat=1) dv> qubgeom=read_lines(url) Downloading.done. Read TEXT file: 145 lines Text Buffer with 145 lines of text dv> qubgeom[,55:60] Text Buffer with 6 lines of text 1: *************************** 10. row *************************** 2: schema_name: thmpub 3: table_name: qubgeom 4: field_name: lat 5: datatypename: float8 6: description: Latitude of this point_id on the planet Mars
dv> query="select schema_name, table_name, field_name, description from reference.glossary where field_name like '%temp%';" dv> tempflds=read_lines(url) Downloading.done. Read TEXT file: 253 lines Text Buffer with 253 lines of text 1: *************************** 1. row *************************** 2: table_name: imgidx 3: field_name: focal_plane_temperature 4: description: Temperature in Kelvin of the VIS camera focal plane array at the time of the observation 5: ....
|
DavinciWiki Mini-Nav Bar Contents
Contact Developers
All other topics
Functions Used
Related Procedures |