wiki:PSPSAccess
PSPS Help Sections Software Components PSPS User Tools Project Wikis Related sites * -- restricted access

Connecting Directly to the Data Retrieval Layer

The DRL API allows us to write client side programs that can access PSPS data via Simple Object Access Protocol. On this page we provide an examples of a simple standalone program that can send queries to the DRL and a return the result set from the appropriate database. One can use this sort of program in a "robot" or "crawler" in a shell script to query a database repeatedly in an automated fashion.

Note: If you make your own program to do repetitive queries, please give some sleep time of a couple minutes between queries so that the DRL does not get bogged down.

Perl Example

One example we provide is a Perl program named queryDRLClient.pl which is attached to this page. This demonstration application was written by PSPS software engineer Conrad Holmberg. As attached, the program is set to query the SDSS database (SCHEMAGROUP= MOPS and SCHEMA = MOPS). To run this program you will need to change the user name "dummy_username" and the login password "dummy_password" to your login and password. To run the program, we put one or more queries into a file "query.sql" and provide the name of an output file which will receive the query results. Then, at the command prompt one types

perl PerlDemo.pl -t fast -q query.sql -o outputfile.csv

The -t option is for either fast or slow queries. Note that if you use a slow query be sure to use the "SELECT INTO mydb.tablename" syntax. If our query file contains the line

SELECT det_id, field_id, ra_deg, dec_deg FROM detections LIMIT 10

the file outputfile.csv will contain the following result set:

[det_id]:bigint,[field_id]:bigint,[ra_deg]:double,[dec_deg]:double
1,1,277.470015116285,-18.1245504181695
2,1,275.120546309708,-17.0419481271303
3,1,276.546491515264,-18.8204943100002
4,1,277.342867487778,-17.2662945064626
5,1,276.871798391819,-16.6516894786823
6,1,277.720108984163,-18.0596931304613
7,1,275.580692185958,-16.8092245694909
8,1,275.303104145549,-17.2323459013616
9,1,275.282278634363,-17.8967181628463
10,1,276.098944658971,-18.316948956578

If we change the line

use constant SCHEMAGROUP => 'MOPS';

to

use constant SCHEMAGROUP => 'PS1_SCHEMA';

and the line

use constant SCHEMA => 'MOPS';

to

use constant SCHEMA => 'PanSTARRS_3PI';

we can run a query against the ODM version of the 3PI database. In particular, we can submit the query

SELECT TOP 10 objid, ra, dec, gMagBest, rMagBest from Object

and get the result set

[objid]:Integer,[ra]:Float,[dec]:Float,[gMagBest]:Float,[rMagBest]:Float
109501937505879293,193.75058748,1.25737931,23.23,22.431
109501937556915344,193.75569169,1.25408842,23.385,22.963
109501937560561491,193.75605605,1.25087825,18.723,18.446
109501937584929532,193.75849237,1.25757837,20.925,20.528
109501937608451224,193.76084533,1.25065563,25.255,25.415
109501937640244226,193.76402441,1.25315688,14.976,14.549
109501937658833892,193.76588375,1.25287845,20.431,20.229
109501937674264277,193.76742633,1.25319986,22.02,21.311
109501937692919552,193.7692914,1.25759518,22.738,20.629
109501937715755628,193.77157561,1.25432573,23.516,21.365

To run more than one query per input file, terminate each individual query with a semicolon. Do not terminate the final query this was as the program will expect another query in the list and terminate with an annoying (but harmless) error message.

Other Languages

The example shown above uses the Perl scripting language. The PSI application developed by the Pan-STARRS project uses essentially the same method illustrated by this Perl script, but programmed in PHP. The key point is that if one has a software library that can speak "SOAP" your software can make a connection to the DRL by following the simple protocol outlined above. As to whether your favorite programming language has a SOAP library is an exercise left to the reader to explore on the web.

Java Example

The source code for a Java example is in DRLClient.java. To use it, open the DRLclient.java file and add your username and password into the appropriate global variables. To compile, type the following within the console:

javac DRLclient.java

To run

java DRLClient -q sample.sql -t fast -o output.txt

Python Example

The source code for a Python example is in queryDRLClient.py. To use it, open the file with an editor and add your username and password into the appropriate global variables. To run it, just use three arguments in this order: the name of the sql file, the name of the output file, and the type of query (fast or slow).

Note: You need the python library suds in order to run this.

Example:

python queryDRLClient.py sample.sql output.txt slow
Last modified 4 years ago Last modified on 08/23/13 15:54:59

Attachments (3)

Download all attachments as: .zip