1 of 10

SQL Geospatial Functions

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.

Example 2

Find all records that have an area less than 1 square mile (or 2589988 square meters).

geoLength

Takes a GeoJSON and measures its length in the specified units (default is kilometers).

Syntax

geoLength(geoJSON[, units])

Parameters

Parameter

Description

Example 1

Calculate the length, in kilometers, of a manually passed GeoJSON linestring.

SELECT geoLength('{
    "type": "Feature",
    "geometry": {
        "type": "LineString",
        "coordinates": [
            [-104.97963309288025,39.76163265441438],
            [-104.9823260307312,39.76365323407955],
            [-104.99193906784058,39.75616442110704]
        ]
    }
}')

Example 2

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

Parameter

Description

Example

Return a GeoJSON Polygon that removes City Park (polygon2) from Colorado (polygon1).

SELECT geoDifference('{
    "type": "Feature",
    "properties": {
      "name":"Colorado"
    },
    "geometry": {
        "type": "Polygon",
        "coordinates": [[
            [-109.072265625,37.00255267215955],
            [-102.01904296874999,37.00255267215955],
            [-102.01904296874999,41.0130657870063],
            [-109.072265625,41.0130657870063],
            [-109.072265625,37.00255267215955]
        ]]
      }
    }',
    '{
        "type": "Feature",
        "properties": {
          "name":"City Park"
        },
        "geometry": {
            "type": "Polygon",
            "coordinates": [[
                [-104.95973110198975,39.7543828214657],
                [-104.95955944061278,39.744781185675386],
                [-104.95904445648193,39.74422022399989],
                [-104.95835781097412,39.74402223643582],
                [-104.94097709655762,39.74392324244047],
                [-104.9408483505249,39.75434982844515],
                [-104.95973110198975,39.7543828214657]
            ]]
        }
    }'
)

geoDistance

#geoDistance Calculates the distance between two points in units (default is kilometers).

Syntax

geoDistance(point1, point2[, units])

Parameters

Parameter

Description

Example 1

Calculate the distance, in miles, between HarperDB’s headquarters and the Washington Monument.

SELECT geoDistance('[-104.979127,39.761563]', '[-77.035248,38.889475]', 'miles')

Example 2

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

Parameter

Description

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

Parameter

Description

Example 1

Return all locations within the state of Colorado (passed as a GeoJSON string).

SELECT *
FROM dev.locations
WHERE geoContains('{
    "type": "Feature",
    "properties": {
      "name":"Colorado"
    },
    "geometry": {
        "type": "Polygon",
        "coordinates": [[
            [-109.072265625,37.00255267],
            [-102.01904296874999,37.00255267],
            [-102.01904296874999,41.01306579],
            [-109.072265625,41.01306579],
            [-109.072265625,37.00255267]
        ]]
    }
}', geo_data)

Example 2

Return all locations which contain HarperDB Headquarters.

SELECT *
FROM dev.locations
WHERE geoContains(geo_data, '{
    "type": "Feature",
    "properties": {
      "name": "HarperDB Headquarters"
    },
    "geometry": {
        "type": "Polygon",
        "coordinates": [[
            [-104.98060941696167,39.760704817357905],
            [-104.98053967952728,39.76065120861263],
            [-104.98055577278137,39.760642961109674],
            [-104.98037070035934,39.76049450588716],
            [-104.9802714586258,39.76056254790385],
            [-104.9805235862732,39.76076461167841],
            [-104.98060941696167,39.760704817357905]
        ]]
    }
}')

geoEqual

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

Parameter

Description

Example

Find HarperDB Headquarters within all locations within the database.

SELECT *
FROM dev.locations
WHERE geoEqual(geo_data, '{
    "type": "Feature",
    "properties": {
      "name": "HarperDB Headquarters"
    },
    "geometry": {
        "type": "Polygon",
        "coordinates": [[
            [-104.98060941696167,39.760704817357905],
            [-104.98053967952728,39.76065120861263],
            [-104.98055577278137,39.760642961109674],
            [-104.98037070035934,39.76049450588716],
            [-104.9802714586258,39.76056254790385],
            [-104.9805235862732,39.76076461167841],
            [-104.98060941696167,39.760704817357905]
        ]]
    }
}')

geoCrosses

Determines if the geometries cross over each other. Returns boolean.

Syntax

geoCrosses(geo1, geo2)

Parameters

Parameter

Description

Example

Find all locations that cross over a highway.

SELECT *
FROM dev.locations
WHERE geoCrosses(
    geo_data,
    '{
        "type": "Feature",
        "properties": {
          "name": "Highway I-25"
        },
        "geometry": {
            "type": "LineString",
            "coordinates": [
                [-104.9139404296875,41.00477542222947],
                [-105.0238037109375,39.715638134796336],
                [-104.853515625,39.53370327008705],
                [-104.853515625,38.81403111409755],
                [-104.61181640625,38.39764411353178],
                [-104.8974609375,37.68382032669382],
                [-104.501953125,37.00255267215955]
            ]
        }
    }'
)

geoConvert

Converts a series of coordinates into a GeoJSON of the specified type.

Syntax

geoConvert(coordinates, geo_type[, properties])

Parameters

Parameter

Description

Example

Convert a given coordinate into a GeoJSON point with specified properties.

SELECT geoConvert(
    '[-104.979127,39.761563]',
    'point',
    '{
      "name": "HarperDB Headquarters"
    }'
)