Ensure good spread of geographical data from SQL database
To display data on google maps, I have >50k rows of data in SQL server 2005 with the following (simplified) structure
PointID
Latitude
Longitude
TypeID
I can select a small subset within a bounding box, ensuring a decent mix of Types like this:
.....
(
@NELat float,
@NELong float,
@SWLat float,
@SWLong float
)
as
select top 100 PointID, Latitude, Longitude,
rank() over (partition by PointTable.TypeID order by newid()) as NewRank
from PointTable
where
(
CONVERT(float, PointTable.Latitude) >= @SWLat and CONVERT(float, PointTable.Latitude) <= @NELat and
CONVERT(float, PointTable.Longitude) >= @SWLong and CONVERT(float, PointTable.Longitude) <= @NELong
)
order by NewRank
Unfortunately the initial data is biased towards one particular geographical location.
What's the most efficient/computationally quickest way to ensure that the retrieved data has a decent geographical spread?
I don't want to cluster the data, just to show a more even spread of 开发者_运维技巧data across the bounding box. Could I possibly create a subset of bounding box grids, and partition over them? Any suggestions would be a great help!
I had been looking into the geography datatype available to SQL sever 2008, but it doesn't look like it's available in 2005. Also I am aware float
is not the best datatype for storing coordinates, but that wasn't up to me.
What I ended up doing is the following:
Extended my table to include the Hierarchical Triangular Mesh ID for the given lat/lon.
PointID
Latitude
Longitude
TypeID
HTMID
HTMID was generated using the functions in the spatial data library from "Using Table Valued Functions in SQL Server 2005 to Implement a Spatial Data Library", with source code downloadable from Codeplex (note I had to generate a new sampleKey.snk to build the example project. I followed these instructions)
Then the HTMID can be rounded and used to group close-by points together.
.....
(
@NELat float,
@NELong float,
@SWLat float,
@SWLong float
)
as
select top 100 PointID, Latitude, Longitude,
rank() over (partition by PointTable.TypeID order by newid()) as NewRank,
rank() over (partition by round(PointTable.HTMID,-7) order by newid()) as HTMRank
from PointTable
where
(
CONVERT(float, PointTable.Latitude) >= @SWLat and CONVERT(float, PointTable.Latitude) <= @NELat and
CONVERT(float, PointTable.Longitude) >= @SWLong and CONVERT(float, PointTable.Longitude) <= @NELong
)
order by HTMRank, NewRank
This may not be entirely accurate, and I wouldn't use this HTMID to calculate anything more precise without diving into the technical details more deeply - but it certainly achieves what I want it to.
精彩评论