Ensure good spread of geographical data from SQL database

Question

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.

Answer 1

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.