Clustering of results?

Dec 1, 2008 at 7:13 AM
Hi spatial guru's.

would be awesomesauce uber hawtness if we could get some of our lat longs CLUSTERED, to reduce the size of the result set, over the wire.

example made up sql code.

DECLARE (@MyBoundingShape GEOMETRY = 'POLYGON((....))'  -- This could be a box, of 1 mile by 1 mile / 1 km by 1km .. or a circle of what not...

SELECT GeoData
FORM SomeTableWithAGeographyField
WHERE GeoData.STClustered(@MyBoundingShape)

or

SELECT GeoData
FORM SomeTableWithAGeographyField
WHERE GeoData.STClustered(1000) -- 1000 == 1000 metres or 1000 feet, etc .. to define a box or a radius or something.


This way, we get one GEOGRAPHY result for the clustered area instead of multiple results and then having to cluster them in our application .net code. It sucks when i have to grab 10,000 pins and cluster them .. with all that data going down the wire :(
Dec 9, 2008 at 2:34 PM
I may be missing the question, but you can do this already using

@myboundingshape.STIntersects(geometryColumn)  
or 
@mycentrepoint.STDistance(geometryColumn) < @somedistance  

hth jd
Apr 15, 2009 at 8:19 AM
er. nope.

what i ment was this.

imagine you have a database table that contains all the lat/long (ie GEOGRAPHY) points for crime in a city. Then create a polygon square and say 'can i have all the crime points that occured within this square)". that's using STIntersects, etc.

Now, i get 150,000 results. arg. Now, with this 150K results, can u cluster this (somehow) so everything in 500meter squares is now 'one' result, instead of 'n' number of results.

that's what i was meaning.
Developer
Oct 15, 2009 at 10:12 PM

One way to do it would be to use GROUP BY on rounded coordinates:

DECLARE @resolution float(max) = 500; -- meters

SELECT ROUND(point.STX / @resolution, 0) * @resolution as cluster_x, ROUND(point.STY / @resolution, 0) * @resolution as cluster_y, COUNT(*) cluster_size, geometry::Point(cluster_x, cluster_y, 0) as cluster_point

FROM ...

WHERE point.STDistance(@center) < @dist

GROUP BY cluster_x, cluster_y