CLR Types - Pro SQL Server Internals - page 284

Calculation of the bounding box corner points can be done with a CLR table-valued function, as shown in
Listing 14-11. Listing 14-12 shows the T-SQL code that alters the table and creates a nonclustered index there.
Listing 14-11. Customer-based POI Lookup: Calculating bounding box coordinates
private struct BoundingBox
{
public double minLat;
public double maxLat;
public double minLon;
public double maxLon;
}
private static void CircleBoundingBox_FillValues(
object obj, out SqlDouble MinLat, out SqlDouble MaxLat,
out SqlDouble MinLon, out SqlDouble MaxLon)
{
BoundingBox box = (BoundingBox)obj;
MinLat = new SqlDouble(box.minLat);
MaxLat = new SqlDouble(box.maxLat);
MinLon = new SqlDouble(box.minLon);
MaxLon = new SqlDouble(box.maxLon);
}
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.None,
IsDeterministic = true, IsPrecise = false,
SystemDataAccess = SystemDataAccessKind.None,
FillRowMethodName = "CircleBoundingBox_FillValues",
TableDefinition = "MinLat float, MaxLat float, MinLon float, MaxLon float"
)]
public static IEnumerable CalcCircleBoundingBox(SqlDouble lat, SqlDouble lon, SqlInt32 distance)
{
if (lat.IsNull || lon.IsNull || distance.IsNull)
return null;
BoundingBox[] box = new BoundingBox[1];
double latR = Math.PI / 180 * lat.Value;
double lonR = Math.PI / 180 * lon.Value;
double rad45 = 0.785398163397448300; // RADIANS(45.)
double rad135 = 2.356194490192344800; // RADIANS(135.)
double rad225 = 3.926990816987241400; // RADIANS(225.)
double rad315 = 5.497787143782137900; // RADIANS(315.)
double distR = distance.Value * 1.4142135623731 * Math.PI / 20001600.0;
double latR45 = Math.Asin(Math.Sin(latR) * Math.Cos(distR) + Math.Cos(latR) * Math.
Sin(distR) * Math.Cos(rad45));
double latR135 = Math.Asin(Math.Sin(latR) * Math.Cos(distR) + Math.Cos(latR) * Math.
Sin(distR) * Math.Cos(rad135));
double latR225 = Math.Asin(Math.Sin(latR) * Math.Cos(distR) + Math.Cos(latR) * Math.
Sin(distR) * Math.Cos(rad225));