Application Programming Interface

The V_S profile database is equipped with an application programming interface (API) that provides access to data via HTTP requests. The API functionality is accessible directly through the web interface, and also through any program capable of submitting HTTP requests. The web interface is useful if you quickly want to look at data in a tabular form, but it is cumbersome if you want to develop an end-to-end workflow. Rather than having to parse an HTML table generated by the server, the API exposes the underlying data directly in JSON format so you can work with it more directly and incorporate it into a workflow.

Users can access the API by authenticating, retrieving an authorization token, and submitting a request that includes that token in the header. The authorization token is required because we want to make sure that we return the information only to people who have an active account and are authorized to retrieve the data they have requested, and avoid attacks by unathorized users and bots.

Authentication / Authorization

The first step in using the API is to authenticate and retrieve an authorization token. To authenticate, submit an HTTP GET request to the following uniform resource locator (URL)

https://www.vspdb.org/users/login

You must use the following in your authentication request:

Your username and password using the Basic Auth authentication service
The {"Accept":"application/json"} header. This identifies that JSON data is being requested
The {"User-Agent":"XY"} header where XY contains info about your browser (see example below). Requests are often rejected when a "User-Agent" header is omitted.

Programs specifically designed for making HTTP requests include curl, postman, and others. In this documentation, we provide examples using the Python requests package. The example below authenticates the user using HTTP Basic Auth and retrieves an authorization token. The User-Agent token indicates that the request is being made by the Google Chrome browser. You may need to look up the appropriate header for your own browser application. We suggest never hard-coding your username and password into a script. You might accidentally share the script, and divulge your authentication credentials. We have used the getpass package here to prevent that by requiring users to enter their credentials each time the code is executed. The token is valid for 120 minutes, after which you will need to re-authenticate and retrieve a new token. It is important not to share your token with others.

import requests
from requests.auth import HTTPBasicAuth
import getpass
import json

email = input('email: ')
password = getpass.getpass('password: ')
url = 'http://www.vspdb.org/users/login'
headers = {}
headers['User-Agent'] = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) 134.0.6998.118 Safari/537.36"
headers['Accept'] = "application/json"
r = requests.get(url, headers=headers, auth=HTTPBasicAuth(email,password))
token = json.loads(r.text)['token']

Submitting a Request

After retrieving an authorization token, you can now submit a GET request to the desired URL, and the requested data will be returned in JSON format. You must include the following headers in your HTTP request:

{"Accept":"application/json"}
{"User-Agent":"XY"}
{"Authorization":"Bearer {}".format(token)}. This header contains the token that was provided to you after successful authentication.

An example request is provided in the Python script below. We are assuming you have already retrieved an authorization token and stored it in a variable called "token". In this case, the first 50 entries from the sites table are retrieved. The URL structure and available query string parameters for customizing the request are discussed in the next section.

import pandas as pd
import requests
import json

headers = {}
headers["Accept"] = "application/json"
headers["User-Agent"] = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) 134.0.6998.118 Safari/537.36"
headers["Authorization"] = "Bearer {}".format(token)
url = 'http://www.vspdb.org/sites?limit=50'
r= requests.get(url, headers=headers)
df = pd.DataFrame.from_dict(json.loads(r.text))

Requests are submitted to an endpoint, which corresponds to a table in the database. The "sites" endpoint was used in the example above. Submitting a request without specifying an endpoint will produce an error. The endpoint is necessary so we know which query to run to retrieve the data you have requested. The endpoints correspond to table names converted to pluralized lower camel case with underscores removed. All of the tables except for the users table are accessible. The users table is not accessible for privacy purposes.

table name	endpoint
boring_metadata	boringMetadata
citations	citations
cpt_data	cptData
cpt_metadata	cptMetadata
dispersion_curve_data	dispersionCurveData
dispersion_curve_metadata	dispersionCurveMetadata
files	files
gradataion_data	gradationData
gradation_metadata	gradationMetadata
ground_waters	groundWaters
hvsr_mean_curves	hvsrMeanCurves
hvsr_metadata	hvsrMetadata
hvsr_polar_curves	hvsrPolarCurves
index_properties	indexProperties
samples	samples
site_geologies	siteGeologies
sites	sites
spt_data	sptData
spt_metadata	sptMetadata
stratigraphies	stratigraphies
tests	tests
travel_time_data	travelTimeData
travel_time_metadata	travelTimeMetadata
velocity_metadata	velocityMetadata
vp_layer_data	vpLayerData
vp_point_data	vpPointData
vs_layer_data	vsLayerData
vs_point_data	vsPointData

Query String Parameters

Users can customize their requests by providing query string parameters.

First query string parameter

The first query string parameter comes after a question mark (?) at the end of the url. For example: https://www.vspdb.org/sites?limit=50

Second and subsequent query string parameters

The 2nd and subsequent query string parameters are separated by an ampersand (&). For example: https://www.vsdpb.org/sites?limit=50&page=1

List of Supported Query String Parameters

A list of supported query string parameters is provided below. Spaces are not allowed in URL's, and are often replaced with either "%20" or "+". The examples below use "+" to represent white space because it looks cleaner than "%20".

query string parameter	default	description	example
limit	20	number of records per page	?limit=50
order	id+asc	field to sort by and direction	?order=id+desc
page	1	page number	?page=3
fields	-	comma-separated list of fields to return	?fields=id,site_name
where	-	comma-separated list of conditions used to filter data	?where=site_latitude>35.921
contain	-	comma-separated list of related tables to include in query	?contain=sites
matching	-	table to include in query	?matching=sites

The limit, order, and page query string parameters are pretty straightforward, and there is really nothing more to say about them. However, the fields, where, contain, and matching query string parameters warrant a bit of discussion.

Fields

Users might want to return only certain fields from a table to save memory and speed up queries. In that case, a comma separated list of fields can be specified. For example: ?fields=id,site_name would return only id and site_name, and would not return the other fields. If you are using the web interface, the table headings are hard-coded and will therefore still appear even if their corresponding fields are not specified. The corresponding entries will be empty. If you are submitting an HTTP request through Python or another program, headings will correspond only to the requested data.

Where

The where field allows users to retrieve data that satisfy certain conditions and are used to create a SQL statement excerpt "WHERE [field] [operator] [value]". For example, to retrieve sites with latitude larger than 35.921°, the following should query string parameter would be ?where=site_latitude>35.921. This query string parameter will be translated to the SQL excerpt WHERE site_latitude > 35.921 when the query is executed. Available operators depend on data type as described in the table below.

type	operators
numeric (e.g., float, int)	>, >=, =, <=, <, BETWEEN, IN, NOT IN
string	>, >=, =, <=, <, BETWEEN, LIKE, NOT LIKE, IN, NOT IN
boolean	true, false

The query string parameters are reasonably intuitive, perhaps with the exception of applying multiple conditions, the equal sign (=) for float fields, BETWEEN, IN and NOT IN, and LIKE and NOT LIKE, which are discussed in more detail below.

Applying Multiple Conditions

Multiple operators can be applied using an "AND" or "OR" separator. For example,

"?where=site_latitude<35.921+OR+site_longitude>-118.0"

Furthermore, conditions can be grouped using parenthesis like this:

"?where=(site_latitude>35.0+AND+site_latitude<36.0)+OR+(site_longitude>-118.0+AND+site_longitude<-117.0)"

Equal sign (=) for float fields

You should avoid using the equal sign (=) operator for float-valued fields because floats are stored in an approximate manner. For example, if a latitude appears as 35.921 in the database, you should recognize that its value may not be precisely 35.921 because we format outputs for presentation purposes. Therefore, performing a query "WHERE latitude=35.921" is unlikely to return the entry you're looking for. You could use a combination of greater than (>) and less than (<) operators to search for a narrow range instead. It is fine to use the equal sign (=) operator for integer and string fields.

BETWEEN

The BETWEEN operator is a shorthand replacement for a greater than and less than operator. For example,

"where=latitude>35.0+AND+latitude<36.0"

can be replaced by

"where=latitude+BETWEEN+35.0+AND+36.0"

LIKE and NOT LIKE

The LIKE operator is used to search for a specified pattern in a column of strings.
The NOT LIKE operator returns the complementary set of entries excluded by the LIKE operator.
Two wildcards are often used with LIKE and NOT LIKE statements:

The percent sign (%) is a placeholder for an arbitrary number of characters
The underscore (_) is a placeholder for a single character

Consider the examples below in which we search on a field called "test_name". Note that the value is enclosed in quotes.

Query String	SQL Statement Excerpt	Description
where=test_name+LIKE+"v%"	WHERE test_name LIKE 'v%'	Finds any value that starts with "v"
where=test_name+NOT+LIKE+"v%"	WHERE test_name NOT LIKE 'v%'	Finds any value that does not start with "v"
where=test_name+LIKE+"%v"	WHERE test_name LIKE '%v'	Finds any value that ends with "v"
where=test_name+LIKE+"%v%"	WHERE test_name LIKE '%v%'	Finds any value that contains "v" in any position
where=test_name+LIKE+"_v"	WHERE test_name LIKE '_v'	Finds any value that contains "v" in the 2^nd position
where=test_name+LIKE+"v_%"	WHERE test_name LIKE 'v_%'	Finds any value that starts with "v" and is at least 2 characters in length
where=test_name+LIKE+"v__%"	WHERE test_name LIKE 'v__%'	Finds any value that starts with "v" and is at least 3 characters in length
where=test_name+LIKE+"v%n"	WHERE test_name LIKE 'v%n'	Finds any value that starts with "v" and ends with "n"

IN and NOT IN Operators

The IN operator is used to select values from an array of options, and is shorthand for multiple OR conditions.
The NOT IN operator returns the complementary set of entries excluded by the IN operator.
Since the IN and NOT IN operators imply an equal sign (=), you should not use them for float fields for the reasons explained above.
Consider the examples below in which we search on an integer field called "id".
Note that the value array is a comma-separated list contained within parenthesis.

Query String	SQL Statement Excerpt	Description
where=id+IN+(1,2,3)	WHERE id IN (1, 2, 3)	Finds entries with id = 1, 2, or 3
where=id+NOT+IN+(1,2,3)	WHERE id NOT IN (1, 2, 3)	Finds entries with id ≠ 1, 2, or 3

Contain

The contain query string parameter is used to join tables together. Tables must be associated with each other through a primary key / foreign key constraint in order for the contain statement to work. For example, the tests table has a foreign key "site_id", with the corresponding primary key being "id" sites table. If we submit a GET query to the tests endpoint, we could include a ?contain=Sites query string parameter to also retrieve data from the associated site for each test.

Many tables have a one-to-many relationship. For example, a site can have multiple tests, but a test can only have one site. A contain statement like "/sites?contain=tests" might therefore return multiple test entries per site, while a contain statement like "/tests?contain=sites" will only return one site entry per test. In some cases, an entity specified by a contain statement might not exist. For example, there are many different types of tests (velocity profiles, cone penetration tests, boring logs). So a contain statement like "/tests?contain=cptMetadata" will return data from entries in the tests table regardless of whether they have associated cptMetadata entries. An empty array would be returned for the "cpt_metadata" field in that case. If you'd like to restrict the returned values to entities in the endpoint that have an entry in the associated table, you should use matching instead, as described in the next subsection.

If you are using the web interface, the contain statement will have no influence on the results displayed in the HTML table. That's because the HTML table used to display the results is hard-coded, and will not adapt to receiving the contained data. However, if you are submitting an HTTP request through Python or another program, you will see that the associated data are included as nested JSON entities. For example:

Deep contains can be used to associated tables that do not share a primary / foreign key constraint through other tables that form a chain of connected constraints. For example, let's say we want to retrieve cptMetadata and its associated site data. The cpt_metadata table does not have site_id as a foreign key. Rather, it has test_id. The tests table has site_id as a foreign key. We can use "?contain=Tests.Sites" in our GET request to the cptMetadata endpoint to retrieve associated test and site data. This will introduce an extra level of nesting in the JSON string that is returned. For example:

One issue when you use the contain query string parameter is that the tables you are including might have fields with the same name as the table associated with the endpoint. For example, the primary key in every table is called "id". Fields provided in the "where" and "order" query string parameters become ambiguous in that case. To remove the ambiguity, you should include the table name before the ambiguous field name, separated using a dot (.) operator. For example:

https://www.vspdb.org/tests?contain=Sites&where=tests.id>20

If you use the "fields=" operator, you should not include the table name for the table associated with the endpoint, but you should include the table name for contained tables. For example:

https://www.vspdb.org/tests?contain=Sites&where=tests.id>20&fields=id,test_name,sites.id,sites.site_name

The table below contains a list of table names, contain field names, and associated tables.

table name	contain field name	associated tables
boring_metadata	boringMetadata	tests, groundWaters, samples, sptMetadata, stratigraphies
citations	citations	siteGeologies, tests
cpt_data	cptData	cptMetadata
cpt_metadata	cptMetadata	tests, cptMetadata
dispersion_curve_data	dispersionCurveData	dispersionCurveMetadata
dispersion_curve_metadata	dispersionCurveMetadata	tests, dispersionCurveData
gradataion_data	gradationData	gradationMetadata
gradation_metadata	gradationMetadata	samples, gradationData
ground_waters	groundWaters	boringMetadata
hvsr_mean_curves	hvsrMeanCurves	hvsrMetadata
hvsr_metadata	hvsrMetadata	tests, hvsrMeanCurves
hvsr_polar_curves	hvsrPolarCurves	hvsrMetadata
index_properties	indexProperties	samples
samples	samples	boringMetadata, gradationMetadata, indexProperties
site_geologies	siteGeologies	sites, citations
sites	sites	siteGeologies, tests
spt_data	sptData	sptMetadata
spt_metadata	sptMetadata	boringMetadata, sptData
stratigraphies	stratigraphies	boringMetadata
tests	tests	sites, citations, boringMetadata, cptMetadata, dipsersionCurveMetadata, hvsrMetadata, travelTimeMetadata, VelocityMetadata
travel_time_data	travelTimeData	travelTimeMetadata
velocity_metadata	velocityMetadata	tests, travelTimeData
vp_layer_data	vpLayerData	velocityMetadata
vp_point_data	vpPointData	velocityMetadata
vs_layer_data	vsLayerData	velocityMetadata
vs_point_data	fsPointData	velocityMetadata

Matching

The "matching" operator serves a similar function to "contain" except that results are only returned if an entity exists in the matching table. In this manner "contain" is similar to a LEFT JOIN and "matching" is similar to an INNER JOIN, with the only difference being that the results are returned in a nested array rather than a flattened array (this saves space for transfers over the network by avoiding repetition). One difference is that "contain" can accommodate a comma-separated list of associated tables, but matching can accommodate only a single table. For example, "/tests?matching=cptMetadata" will return tests and associated cptMetadata only for tests that contain cptMetadata. Deep associations can be achieved for "matching" using the dot (.) operator in the same way as for "contain". Data will be returned using the "_matchingData" JSON key. For example:

Example Queries

Below are a few example queries that show how to specify endpoints and query string parameters. This is not an exhaustive list, but just a few examples.

example 1

Query 20 sites sorted in descending order by site_latitude:
https://www.vspdb.org/sites?limit=20&sort=site_latitude&direction=desc

example 2

Query 20 entries from vs_layer_data with associated velocity_metadata:
https://www.vspdb.org/vsLayerData?limit=20&contain=VelocityMetadata

example 2

Query 20 entries from vs_layer_data with associated velocity_metadata, tests, and sites where site_latitude is between 35.0 and 36.0 in descending order by site_latitude:
https://www.vspdb.org/vsLayerData?limit=20&contain=VelocityMetadata

Possible Problems

The database is pretty complicated and there may be bugs in the code. If so, please contact us. Here is a list of a few things to watch out for.

Requesting huge amounts of data

There is a limit to the amount of data that can be returned by the server. You will receive an error message if you request an amount of data that exceeds the limit. In that case, you will need to retrieve the data in batches using the "page" query string parameter.

Filters that return no data

You might get an empty result if you request data that doesn't exist. For example, asking for sites with id<0 is going to fail because the primary key is an integer greater than or equal to zero. Also, doing something like site_latitude<35.0&site_latitude>36.0 will return no data because it's impossible for a number to be smaller than 35.0 and larger than 36.0.