New answers tagged clustering
You might find CrimeStat useful for this: http://www.icpsr.umich.edu/CrimeStat/
How about building the clusters based on the coordinates: SELECT kmeans, count(*), ST_Centroid(ST_Collect(geom)) AS geom, n_items FROM ( SELECT kmeans(ARRAY[log, lat], 5) OVER (), geom, n_items FROM product ) AS ksub GROUP BY kmeans ORDER BY kmeans;
An easy way to cut this query's work in half is to use a.routeid < b.routeid instead of a.routeid <> b.routeid. This prevents PostGIS from making the same line comparisons twice. Another change that may be helpful is to add a spatial index operator. With the query as is, I get the following plan: Nested Loop (cost=0.00..58.73 rows=1 width=72) ...
Is the mean point spacing in X consistent within a group, like these are mile markers? I'd create rectangles centered on each point with a width slightly larger than the point spacing, and a very small height. Then union them, and use the new polygons to group the points. This assumes the points are in groups in X, but may have other points nearby in Y. ...
I'm not at a computer that has access to PostGIS right now, but I feel as though this algorithm might work. Of course if you have vertical groups, you would need to use an exclusion or inclusion clause for ST_Y(). (EDIT): Previous algorithm worked, but became a data hog for large sets. Create TABLE #TheDistances ( ID int IDENTITY(1,1), ID1 int, ID2 ...
Top 50 recent answers are included