Database Reference
In-Depth Information
Location geography not null,
primary key(Id)
);
create table dbo.LocationsGeoIndexed
(
Id int not null identity(1,1),
Location geography not null,
primary key(Id)
);
-- 241,402 rows
;with Latitudes(Lat)
as
(
select convert(float,40.0)
union all
select convert(float,Lat + 0.01)
from Latitudes
where Lat < convert(float,48.0)
)
,Longitudes(Lon)
as
(
select convert(float,-120.0)
union all
select Lon - 0.01
from Longitudes
where Lon > -123
)
insert into dbo.Locations(Latitude, Longitude)
select Latitudes.Lat, Longitudes.Lon
from Latitudes cross join Longitudes
option (maxrecursion 0);
insert into dbo.LocationsGeo(Location)
select geography::Point(Latitude, Longitude, 4326)
from dbo.Locations;
insert into dbo.LocationsGeoIndexed(Location)
select Location
from dbo.LocationsGeo;
create spatial index Idx_LocationsGeoIndexed_Spatial
on dbo.LocationsGeoIndexed(Location);
■
We store location information in relational format using the
decimal(9,6)
data type rather than
float
.
Decimal data types use six bytes less storage space per pair of values, and they provide accuracy which exceeds that
of commercial-grade GpS receivers.
Tip
