Calculating Geo distances from a given address or lat/long.
To calculate the distance in feet from a given lat/long to another lat/long, use the following formula in Cloud9QL:
select *, round(6371000 * 2 * ASIN(SQRT((SIN((latitude - {HOME_LAT}) * 0.0087266 / 2) ^ 2 + COS({HOME_LAT} * 0.0174533) * COS({HOME_LAT} * 0.0174533) * SIN((longitude - {HOME_LONG}) * 0.0087266 / 2) ^ 2)))/0.3048,0) as distance_feet
where latitude and longitude are part of the dataset. {HOME_LAT} and {HOME_LONG} are parameters that's the static location to compare from.
If you only have an address and not a lat/long for the address, then you can convert it using geocod.io and pass it in as a loop join parameter.
Example:
select concat ("$c9_address$(1528 Webster St)$[Street Address]$",", Oakland") as address limit 1;
select geocode(address,<geocode.io api key>);
select latitude as home_lat, longitude as home_long, $c9_radius$(500)$[Radius (Feet)]$ as feet
In this case [Street Address] is a filter parameter that the use inputs the address into, which then gets converted into a lat/long.
This can be then passed in as a loop join into the second query.
LOOP JOIN home_lat = {HOME_LAT} AND home_long = {HOME_LONG} AND feet = {FEET}
Second query example:
SELECT *,
round(6371000 * 2 * ASIN(SQRT((SIN((latitude - {HOME_LAT}) * 0.0087266 / 2) ^ 2 + COS({HOME_LAT} * 0.0174533) * COS({HOME_LAT} * 0.0174533) * SIN((longitude - {HOME_LONG}) * 0.0087266 / 2) ^ 2)))/0.3048,0) as distance_feet
where 6371000 * 2 * ASIN(SQRT((SIN((latitude - {HOME_LAT}) * 0.0087266 / 2) ^ 2 + COS({HOME_LAT} * 0.0174533) * COS({HOME_LAT} * 0.0174533) * SIN((longitude - {HOME_LONG}) * 0.0087266 / 2) ^ 2)))/0.3048 <= {FEET}
and datetime >= $c9_daterange$($c9_now-1y)$[Date]${date|yyyy-MM-dd HH:mm:ss|from}$
and and datetime < $c9_daterange$($c9_now)$[Date]${date|yyyy-MM-dd HH:mm:ss|to}$
order by datetime desc
limit 1000;
This will provide the distance of the locations in the second dataset to a specific address and the feet distance between the two points for each row.
Please sign in to leave a comment.
0 comments