Database Reference
In-Depth Information
double latR315 = Math.Asin(Math.Sin(latR) * Math.Cos(distR) + Math.Cos(latR) * Math.
Sin(distR) * Math.Cos(rad315));
double dLonR45 = Math.Atan2(Math.Sin(rad45) * Math.Sin(distR) * Math.Cos(latR),
Math.Cos(distR) - Math.Sin(latR) * Math.Sin(latR45));
double dLonR135 = Math.Atan2(Math.Sin(rad135) * Math.Sin(distR) * Math.Cos(latR),
Math.Cos(distR) - Math.Sin(latR) * Math.Sin(latR135));
double dLonR225 = Math.Atan2(Math.Sin(rad225) * Math.Sin(distR) * Math.Cos(latR),
Math.Cos(distR) - Math.Sin(latR) * Math.Sin(latR225));
double dLonR315 = Math.Atan2(Math.Sin(rad315) * Math.Sin(distR) * Math.Cos(latR),
Math.Cos(distR) - Math.Sin(latR) * Math.Sin(latR315));
double lat45 = latR45 * 180.0 / Math.PI;
double lat225 = latR225 * 180.0 / Math.PI;
double lon45 = (((lonR - dLonR45 + Math.PI) % (2 * Math.PI)) - Math.PI) * 180.0 / Math.PI;
double lon135 = (((lonR - dLonR135 + Math.PI) % (2 * Math.PI)) - Math.PI) *180.0 / Math.PI;
double lon225 = (((lonR - dLonR225 + Math.PI) % (2 * Math.PI)) - Math.PI) *180.0 / Math.PI;
double lon315 = (((lonR - dLonR315 + Math.PI) % (2 * Math.PI)) - Math.PI) *180.0 / Math.PI;
box[0].minLat = Math.Min(lat45, lat225);
box[0].maxLat = Math.Max(lat45, lat225);
box[0].minLon = Math.Min(Math.Min(lon45, lon135), Math.Min(lon225,lon315));
box[0].maxLon = Math.Max(Math.Max(lon45, lon135), Math.Max(lon225, lon315));
return box;
}
Listing 14-12.
Customer-based POI Lookup: Altering the table
alter table dbo.LocationsGeo2 add MinLat decimal(9,6);
alter table dbo.LocationsGeo2 add MaxLat decimal(9,6);
alter table dbo.LocationsGeo2 add MinLon decimal(9,6);
alter table dbo.LocationsGeo2 add MaxLon decimal(9,6);
update t
set
t.MinLat = b.MinLat
,t.MinLon = b.MinLon
,t.MaxLat = b.MaxLat
,t.MaxLon = b.MaxLon
from
dbo.LocationsGeo2 t cross apply
dbo.CalcCircleBoundingBox(t.Location.Lat,t.Location.Long,1609) b;
create index IDX_LocationsGeo2_BoundingBox
on dbo.LocationsGeo2(CompanyId, MinLon, MaxLon)
include (MinLat, MaxLat);
Now you can change the query to utilize the bounding box. The query is shown in Listing 14-13. The execution
plan is shown in Figure
14-10
.
