Alex Little

I updated the map grids so they are divided equally, and all appears to be working in that the markers divided and combined in an expected way. However I did find there were quite a few little bugs etc with the reclustering algorithm which were all related to floating points and the number of decimal places.

The problems appear to be due to rounding errors when I’m storing the coordinates in the database. For example, when I think I’m storing say “51.2″, actually “51.2000000000001″ gets stored in the database, so then when I look up all the points matching 51.2 the database doesn’t return anything!

I have found a way around this, though I’m not sure how much of a hack it is! The solution seemed to be to treat the coordinates as strings when adding them to the prepared statement for updating and querying on. So instead of using:

String sql ="SELECT * FROM mytable WHERE lat = ?";
Connection conn = conn.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setDouble(1,51.2);

I’m using:

String sql ="SELECT * FROM mytable WHERE lat = ?";
Connection conn = conn.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
DecimalFormat f = new DecimalFormat("#.000000000000");
pstmt.setString(1,f.format(51.2));

This all appears to be working for storing/retrieving the correct values/records – note that the values are still stored as datatype decimal(18,15). My only concern is that I’ve only tested it against a MySQL database and I’m not sure whether other rdbmss would accept decimals/floats passed in string format.

Another possible solution that was suggested to me was to multiply up the coordinates so they are stored as integers in the database – but as this would be a fairly significant change at this stage (and may introduce more bugs!!), I’ll stick with the solution I’ve implemented for now

Have updated to use double rather than float and that appears to give me the precision I now need and it’s done the trick – found I had one or two other little bugs too that needed fixed so has taken me longer to get this sorted than I would’ve liked.

Also, now that it’s done there appears to be some slight anomalies when the clustering is viewed on Google maps, though this is actually just to do with where the grid boundaries are and the concentration of points around intersections of the grid lines. Take the image below as an example:

The lines are the grid squares, the black dots represent the actual locations and the red dots where the points have been clustered for a given square. If the above was represented on Google maps, then there would be 4 markers relatively close together (possibly overlapping), whereas you would probably expect the points to be clustered to a single point, some where in the middle of the image.

Anyway, this only seems to be cropping up on certain zoom levels (to do with where the divisions of the squares occur) and in certain areas and I don’t think there’s much I can do about it unless I implemented a ‘proper’ clustering algorithm – as opposed to an approximation to one.