public void AdvancedExample2_ApproximateBoundingCircleQuery()
{
Ctx.From <WeatherGeoTable>()
.Where(x => x.month == 1)
.Select(x => new
{
distance = (BqFunc.Acos(BqFunc.Sin(39.737567 * BqFunc.PI() / 180)
* BqFunc.Sin((x.lat / 1000) * BqFunc.PI() / 180)
+ BqFunc.Cos(39.737567 * BqFunc.PI() / 180)
* BqFunc.Cos((x.lat / 1000) * BqFunc.PI() / 180)
* BqFunc.Cos((-104.9847179 - (x.@long / 1000)) * BqFunc.PI() / 180)) * 180 / BqFunc.PI())
* 60 * 1.1515,
temp = BqFunc.Average(x.mean_temp),
lat = BqFunc.Average(x.lat / 1000),
@long = BqFunc.Average(x.@long / 1000)
})
.GroupBy(x => x.distance)
.Into()
.Where(x => x.distance < 100)
.Select(x => new { x.distance, x.lat, x.@long, x.temp })
.OrderBy(x => x.distance)
.Limit(100)
.ToString()
.Is(@"
SELECT
[distance],
[lat],
[long],
[temp]
FROM
(
SELECT
((((ACOS(((SIN(((39.737567 * PI()) / 180)) * SIN(((([lat] / 1000) * PI()) / 180))) + ((COS(((39.737567 * PI()) / 180)) * COS(((([lat] / 1000) * PI()) / 180))) * COS((((-104.9847179 - ([long] / 1000)) * PI()) / 180))))) * 180) / PI()) * 60) * 1.1515) AS [distance],
AVG([mean_temp]) AS [temp],
AVG(([lat] / 1000)) AS [lat],
AVG(([long] / 1000)) AS [long]
FROM
[weather_geo.table]
WHERE
([month] = 1)
GROUP BY
[distance]
)
WHERE
([distance] < 100)
ORDER BY
[distance]
LIMIT 100
".TrimSmart());
}
public void Trigonometric()
{
Ctx.Select(() => BqFunc.Acos(0.5)).ToFlatSql().Is("SELECT ACOS(0.5)");
Ctx.Select(() => BqFunc.Acosh(2.4)).ToFlatSql().Is("SELECT ACOSH(2.4)");
Ctx.Select(() => BqFunc.Asin(1.2)).ToFlatSql().Is("SELECT ASIN(1.2)");
Ctx.Select(() => BqFunc.Asinh(2.3)).ToFlatSql().Is("SELECT ASINH(2.3)");
Ctx.Select(() => BqFunc.Atan(2.3)).ToFlatSql().Is("SELECT ATAN(2.3)");
Ctx.Select(() => BqFunc.Atanh(0.4)).ToFlatSql().Is("SELECT ATANH(0.4)");
Ctx.Select(() => BqFunc.Cos(2.3)).ToFlatSql().Is("SELECT COS(2.3)");
Ctx.Select(() => BqFunc.Cosh(2.3)).ToFlatSql().Is("SELECT COSH(2.3)");
Ctx.Select(() => BqFunc.Sin(2.3)).ToFlatSql().Is("SELECT SIN(2.3)");
Ctx.Select(() => BqFunc.Sinh(2.3)).ToFlatSql().Is("SELECT SINH(2.3)");
Ctx.Select(() => BqFunc.Tan(2.3)).ToFlatSql().Is("SELECT TAN(2.3)");
Ctx.Select(() => BqFunc.Tanh(2.3)).ToFlatSql().Is("SELECT TANH(2.3)");
Ctx.Select(() => BqFunc.Atan2(2.4, 1.2)).ToFlatSql().Is("SELECT ATAN2(2.4, 1.2)");
}
