Geo functions

​

greatCircleDistance

greatCircleDistance(lon1Deg, lat1Deg, lon2Deg, lat2Deg)

• lon1Deg, lon2Deg — Longitude of each point, in degrees, in the range [-180, 180].
• lat1Deg, lat2Deg — Latitude of each point, in degrees, in the range [-90, 90].

• The distance between the two points, in meters (Float64). Roughly 111,195 m per degree of latitude.

SELECT distinct_id
FROM users
WHERE greatCircleDistance(
  CAST(user_properties ->> 'lng' AS DOUBLE PRECISION),
  CAST(user_properties ->> 'lat' AS DOUBLE PRECISION),
  -0.1276,
  51.5072
) <= 50000

​

geoDistance

geoDistance(lon1Deg, lat1Deg, lon2Deg, lat2Deg)

• lon1Deg, lon2Deg — Longitude of each point, in degrees, in the range [-180, 180].
• lat1Deg, lat2Deg — Latitude of each point, in degrees, in the range [-90, 90].

• The distance between the two points, in meters (Float64).

SELECT distinct_id
FROM users
WHERE geoDistance(
  CAST(user_properties ->> 'lng' AS DOUBLE PRECISION),
  CAST(user_properties ->> 'lat' AS DOUBLE PRECISION),
  -122.4194,
  37.7749
) < 50000

​

pointInPolygon

pointInPolygon((x, y), [(a, b), (c, d), ...])

• (x, y) — The point to test, as a tuple of coordinates (Tuple).
• [(a, b), (c, d), ...] — The polygon, as an array of vertex tuples forming a closed ring (Array of Tuple).

• 1 if the point is inside the polygon, 0 otherwise (UInt8).

SELECT distinct_id
FROM users
WHERE pointInPolygon(
  (
    CAST(user_properties ->> 'lng' AS DOUBLE PRECISION),
    CAST(user_properties ->> 'lat' AS DOUBLE PRECISION)
  ),
  [(-0.51, 51.28), (0.33, 51.28), (0.33, 51.69), (-0.51, 51.69)]
) = 1

​

greatCircleAngle

greatCircleAngle(lon1Deg, lat1Deg, lon2Deg, lat2Deg)

• lon1Deg, lon2Deg — Longitude of each point, in degrees.
• lat1Deg, lat2Deg — Latitude of each point, in degrees.

• The central angle between the two points, in degrees (Float64).

SELECT distinct_id
FROM users
WHERE greatCircleAngle(
  CAST(user_properties ->> 'lng' AS DOUBLE PRECISION),
  CAST(user_properties ->> 'lat' AS DOUBLE PRECISION),
  0.0,
  0.0
) < 1.0

​

geoToH3

geoToH3(lat, lon, resolution)

• lat — Latitude of the point, in degrees (Float64).
• lon — Longitude of the point, in degrees (Float64).
• resolution — H3 grid resolution, in the range [0, 15], where 0 is coarsest and 15 is finest (UInt8).

• The H3 cell index (UInt64), or 0 on error.

SELECT distinct_id
FROM users
WHERE geoToH3(
  CAST(user_properties ->> 'lat' AS DOUBLE PRECISION),
  CAST(user_properties ->> 'lng' AS DOUBLE PRECISION),
  7
) = 617439712828686335

​

h3IsValid

h3IsValid(h3index)

• h3index — The H3 cell index to check (UInt64).

• 1 if the value is a valid H3 index, 0 otherwise (UInt8).

SELECT distinct_id
FROM users
WHERE h3IsValid(
  geoToH3(
    CAST(user_properties ->> 'lat' AS DOUBLE PRECISION),
    CAST(user_properties ->> 'lng' AS DOUBLE PRECISION),
    7
  )
) = 1