subsetSQL: Creating and querying local SQL database for bathymetric data

In marmap: Import, Plot and Analyze Bathymetric and Topographic Data

Creating and querying local SQL database for bathymetric data

Description

subsetSQL queries the local SQL database created with setSQL to extract smaller data subsets.

Usage

setSQL(bathy, header = TRUE, sep = ",", db.name = "bathy_db")
subsetSQL(min_lon, max_lon, min_lat, max_lat, db.name = "bathy_db")

Arguments

bathy	A text file containing a comma-separated, three-column table with longitude, latitude and depth data (no default)
header	does the xyz file contains a row of column names (default = TRUE)
sep	character separating columns in the xyz file, (default=",")
min_lon	minimum longitude of the data to be extracted from the local SQL database
max_lon	maximum longitude of the data to be extracted from the local SQL database
min_lat	minimum latitude of the data to be extracted from the local SQL database
max_lat	maximum latitude of the data to be extracted from the local SQL database
db.name	The name of (or path to) the SQL database to be created on disk by setSQL or from which subsetSQL will extract data ("bathy_db" by default)

Details

Functions setSQL and subsetSQL were built to work together. setSQL builds an SQL database and saves it on disk. subsetSQL queries that local database and the fields min_lon, max_lon, etc, are used to extract a subset of the database. The functions were built as two entities so that multiple queries can be done multiple times, without re-building the database each time. These functions were designed to access the very large (>5Go) ETOPO 2022 file that can be downloaded from the NOAA website (https://www.ncei.noaa.gov/products/etopo-global-relief-model)

Value

setSQL returns TRUE if the database was successfully created. subsetSQL returns a matrix of class bathy that can directly be used with plot.bathy.

Note

If unspecified, db.name is set to "bathy_db" by default. Thus, theere must be no database file called bathy_db in the working directory prior to running setSQL unless a different name is used for the new database. Make sure that your "bathy" input is a xyz text file (for function setSQL) with 3 columns containing longitude, latitude and depth data, in that order. setSQL and subsetSQL were modified on Nov. 2, 2014 to comply with RSQLite 1.0.0.

Author(s)

Eric Pante

References

NOAA National Centers for Environmental Information. 2022: ETOPO 2022 15 Arc-Second Global Relief Model. NOAA National Centers for Environmental Information. \Sexpr[results=rd]{tools:::Rd_expr_doi("doi.org/10.25921/fd45-gt74")}

Examples

## Not run: 
# load NW Atlantic data
data(nw.atlantic)

# write data to disk as a comma-separated text file
write.table(nw.atlantic, "NW_Atlantic.csv", sep=",", quote=FALSE, row.names=FALSE)

# prepare SQL database
setSQL(bathy="NW_Atlantic.csv")

# uses data from the newly-created SQL database:
subsetSQL(min_lon=-70,max_lon=-50,
             min_lat=35, max_lat=41) -> test

# visualize the results (of class bathy)
summary(test)

# remove temporary database and CSV files
system("rm bathy_db") # remove file, for unix-like systems
system("rm NW_Atlantic.csv") # remove file, for unix-like systems

## End(Not run)

marmap documentation built on March 31, 2023, 6:59 p.m.