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:

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 is geospock.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.