Running GeoSpock database queries from the Presto CLI
You can use Presto CLI tool to run SQL queries on your ingested data in the GeoSpock database.
Alternatively, you can access the GeoSpock database:
- using a BI tool; see Using Business Intelligence (BI) tools with the GeoSpock database
- over HTTP; see Connecting to GeoSpock database over HTTP
- using JDBC; see Connecting to GeoSpock database using JDBC
CLI installation
Install the CLI using the instructions provided by Presto.
Connecting to Data Analyzer using the CLI
Use the following command to connect to GeoSpock database:
presto --server https://<host>:8446 --user <user> --password
Where:
<host>
is the hostname of GeoSpock database; for example:sqlaccess.<platform_domain_ name>
. So, for example, if your platform domain isgeospock.example.com
, set<host>
to:sqlaccess.geospock.example.com
<user>
is your GeoSpock database username
You will then be prompted for the password to your GeoSpock database account.
If you get an authentication error when running a query, check the username and password used to connect to GeoSpock database.
Using the CLI
The CLI accepts queries that span multiple lines but your query must end with a semicolon ;
For example:
SELECT COUNT(*) FROM geospock.default.smallpoi;
Running geospatial JOIN queries
Before running a geospatial JOIN query using GeoSpock database, make sure you follow the Using the GeoSpock DB Presto connector.
The following queries have been optimized for querying data on the GeoSpock database:
SELECT *
FROM geospock.default.largetable AS large
JOIN geospock.default.smalltable AS small
ON ST_DISTANCE(ST_POINT(small.longitude, small.latitude), ST_POINT(large.longitude, large.latitude)) < 0.0005;
and
SELECT *
FROM geospock.default.largetable AS large,
geospock.default.smalltable AS small
WHERE ST_DISTANCE(ST_POINT(small.longitude, small.latitude), ST_POINT(large.longitude, large.latitude)) < 0.0005;
You can save your query results to a table, rather than streaming them back to the client, so that you can perform further queries on the data; see Saving query results to an external table for instructions on how to do this.