SQL Geospatial Functions
Last updated
Last updated
HarperDB geospatial features require data to be stored in a single column using the , 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.
The geoArea() function returns the area of one or more features in square meters.
geoArea(geoJSON)
geoJSON
Required. One or more features.
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).
Takes a GeoJSON and measures its length in the specified units (default is kilometers).
geoLength(geoJSON[, units])
geoJSON
Required. GeoJSON to measure.
units
Optional. Specified as a string. Options are ‘degrees’, ‘radians’, ‘miles’, or ‘kilometers’. Default is ‘kilometers’.
Calculate the length, in kilometers, of a manually passed GeoJSON linestring.
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.
Returns a new polygon with the difference of the second polygon clipped from the first polygon.
geoDifference(polygon1, polygon2)
polygon1
Required. Polygon or MultiPolygon GeoJSON feature.
polygon2
Required. Polygon or MultiPolygon GeoJSON feature to remove from polygon1.
Return a GeoJSON Polygon that removes City Park (polygon2) from Colorado (polygon1).
Calculates the distance between two points in units (default is kilometers).
geoDistance(point1, point2[, units])
point1
Required. GeoJSON Point specifying the origin.
point2
Required. GeoJSON Point specifying the destination.
units
Optional. Specified as a string. Options are ‘degrees’, ‘radians’, ‘miles’, or ‘kilometers’. Default is ‘kilometers’.
Calculate the distance, in miles, between HarperDB’s headquarters and the Washington Monument.
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.
Determines if point1 and point2 are within a specified distance from each other, default units are kilometers. Returns a Boolean.
geoNear(point1, point2, distance[, units])
point1
Required. GeoJSON Point specifying the origin.
point2
Required. GeoJSON Point specifying the destination.
distance
Required. The maximum distance in units as an integer or decimal.
units
Optional. Specified as a string. Options are ‘degrees’, ‘radians’, ‘miles’, or ‘kilometers’. Default is ‘kilometers’.
Return all locations within 50 miles of a given point.
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.
Determines if geo2 is completely contained by geo1. Returns a Boolean.
geoContains(geo1, geo2)
geo1
Required. Polygon or MultiPolygon GeoJSON feature.
geo2
Required. Polygon or MultiPolygon GeoJSON feature tested to be contained by geo1.
Return all locations within the state of Colorado (passed as a GeoJSON string).
Return all locations which contain HarperDB Headquarters.
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.
geoEqual(geo1, geo2)
geo1
Required. GeoJSON geometry or feature.
geo2
Required. GeoJSON geometry or feature.
Find HarperDB Headquarters within all locations within the database.
Determines if the geometries cross over each other. Returns boolean.
geoCrosses(geo1, geo2)
geo1
Required. GeoJSON geometry or feature.
geo2
Required. GeoJSON geometry or feature.
Find all locations that cross over a highway.
Converts a series of coordinates into a GeoJSON of the specified type.
geoConvert(coordinates, geo_type[, properties])
coordinates
Required. One or more coordinates
geo_type
Required. GeoJSON geometry type. Options are ‘point’, ‘lineString’, ‘multiLineString’, ‘multiPoint’, ‘multiPolygon’, and ‘polygon’
properties
Optional. Escaped JSON array with properties to be added to the GeoJSON output.
Convert a given coordinate into a GeoJSON point with specified properties.