PSQLQueryTool Glossary

Contents: Description, Procedure 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. The examples, provided here allow you to access the descriptions of the tables and columns available for you to use in your queries. There are two sources for this information in the reference schema: reference.pubmars_glossary This is a VIEW based on joining several of the postgres control tables which define the actual Mars data tables; this includes tables in the public schema. 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 reference.glossary This is a hand-updated table describing all of the tables under the control of THEMIS Mission-Operations; the descriptions provided here may be more verbose than those in the pubmars_glossary Suggested selection-list fields: table_name, field_name, value_type, description Suggested constraints: schema_name='thmpub' The examples provided below are intended to get you started; however, you should refine the constraints-list and the order-by-list according to your needs. Procedure Use the following commands in each of the following examples 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" Access the PSQLQueryTool from Davinci; see the 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 [URL may be repeated to screen; HINT: copy into your browser to preview results like (make-link?) this] Read in the results using one of the two options shown here: "read_lines" is better for queries that return fewer lines "copy" is better for queries that return hundreds of lines or verbose fields 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] Example-1: Get a list of available THEMIS tables 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 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 column name;" dv> url=themis3db(query,header=0,psql=1,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 Example-3: Search for available columns related to the concept of "temperature" 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 Functions Function Categories Common Procedures Glossary Download Davinci Change Log News Mailing List Contact Developers davinci-dev [AT] mars.asu.edu All other topics See navigation on the left Functions Used copy read_lines themis3db Related Procedures Writing PSQL-DB Queries