subsetSQL function

Creating and querying local SQL database for bathymetric data

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

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)

Returns

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

References

NOAA National Centers for Environmental Information. 2022: ETOPO 2022 15 Arc-Second Global Relief Model. NOAA National Centers for Environmental Information. tools:::Rd_expr_doi("doi.org/10.25921/fd45-gt74")

Author(s)

Eric Pante

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.

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)