HarperDB geospatial features require data to be stored in a single column using the GeoJSON standard, a standard commonly used in geospatial technologies. Geospatial functions are available to be used in SQL statements.
If you are new to GeoJSON you should check out the full specification here: http://geojson.org/. There are a few important things to point out before getting started.
All GeoJSON coordinates are stored in [longitude, latitude] format.
Coordinates or GeoJSON geometries must be passed as string when written directly in a SQL statement.
Note if you are using Postman for you testing. Due to limitations in the Postman client, you will need to escape quotes in your strings and your SQL will need to be passed on a single line.
In the examples contained in the left-hand navigation, schema and table names may change, but all GeoJSON data will be stored in a column named geo_data.
geoArea
The geoArea() function returns the area of one or more features in square meters.
Syntax
geoArea(geoJSON)
Parameters
Parameter
Description
Example 1
Calculate the area, in square meters, of a manually passed GeoJSON polygon.
Find all data plus the calculated length in miles of the GeoJSON, restrict the response to only lengths less than 5 miles, and return the data in order of lengths smallest to largest.
SELECT *, geoLength(geo_data, 'miles') as length
FROM dev.locations
WHERE geoLength(geo_data, 'miles') < 5
ORDER BY length ASC
geoDifference
Returns a new polygon with the difference of the second polygon clipped from the first polygon.
Syntax
geoDifference(polygon1, polygon2)
Parameters
Example
Return a GeoJSON Polygon that removes City Park (polygon2) from Colorado (polygon1).
Find all locations that are within 40 kilometers of a given point, return that distance in miles, and sort by distance in an ascending order.
SELECT *, geoDistance('[-104.979127,39.761563]', geo_data, 'miles') as distance
FROM dev.locations
WHERE geoDistance('[-104.979127,39.761563]', geo_data, 'kilometers') < 40
ORDER BY distance ASC
geoNear
Determines if point1 and point2 are within a specified distance from each other, default units are kilometers. Returns a Boolean.
Syntax
geoNear(point1, point2, distance[, units])
Parameters
Example 1
Return all locations within 50 miles of a given point.
SELECT *
FROM dev.locations
WHERE geoNear('[-104.979127,39.761563]', geo_data, 50, 'miles')
Example 2
Return all locations within 2 degrees of the earth of a given point. (Each degree lat/long is about 69 miles [111 kilometers]). Return all data and the distance in miles, sorted by ascending distance.
SELECT *, geoDistance('[-104.979127,39.761563]', geo_data, 'miles') as distance
FROM dev.locations
WHERE geoNear('[-104.979127,39.761563]', geo_data, 2, 'degrees')
ORDER BY distance ASC
geoContains
Determines if geo2 is completely contained by geo1. Returns a Boolean.
Syntax
geoContains(geo1, geo2)
Parameters
Example 1
Return all locations within the state of Colorado (passed as a GeoJSON string).
Determines if two GeoJSON features are the same type and have identical X,Y coordinate values. For more information see https://developers.arcgis.com/documentation/spatial-references/. Returns a Boolean.
Syntax
geoEqual(geo1, geo2)
Parameters
Example
Find HarperDB Headquarters within all locations within the database.