0

I am trying to implement marker clustering to MySQL. I based my SQL on a php code from <Map Clustering Algorithm>. Added to the initial code is degree -> radian -> degree conversion and cluster location calculation. I will start describing the lowest procedure in hierarchi.

Cluster midpoint calculator:

DROP PROCEDURE IF EXISTS map__get_midpoint;

DELIMITER $$
CREATE PROCEDURE map__get_midpoint(OUT midpointLatDg FLOAT(10,6), OUT midpointLngDg FLOAT(10,6))
BEGIN

SELECT COUNT(*) INTO @clusterSize_midpoint FROM pointsInCluster;
SET @X=0,@Y=0,@Z=0;
SET @thisLat=0, @thisLng=0;
SET @midpoint_loopcount = 1;
all_points_in_cluster:  LOOP
    IF  @midpoint_loopcount > @clusterSize_midpoint THEN
        LEAVE all_points_in_cluster;
    END  IF;
    SELECT lat, lng INTO @thisLat, @thisLng FROM pointsInCluster WHERE id=@midpoint_loopcount;

    SET @thisLat=@thisLat * PI() / 180;
    SET @thisLng=@thisLng * PI() / 180;

    SET @a=COS(@thisLat) * COS(@thisLng);
    SET @b=COS(@thisLat) * SIN(@thisLng);
    SET @c=SIN(@thisLat);

    SET @X=@X+@a;
    SET @Y=@Y+@b;
    SET @Z=@Z+@c;

    SET  @midpoint_loopcount = @midpoint_loopcount + 1;
END LOOP;

SET @X = @X / @clusterSize_midpoint;
SET @Y = @Y / @clusterSize_midpoint;
SET @Z = @Z / @clusterSize_midpoint;

SET @midpointHyp = SQRT( @X * @X + @Y * @Y );
SET @midpointLng = ATAN2(@Y, @X);
SET @midpointLat = ATAN2( @Z , @midpointHyp );

SET midpointLatDg = @midpointLat * 180 / PI();
SET midpointLngDg = @midpointLng * 180 / PI();

END $$

DELIMITER ;

Clusterer:

DROP PROCEDURE IF EXISTS map__cluster;

DELIMITER $$
CREATE PROCEDURE map__cluster(IN zoomLVL INT)
BEGIN

CREATE TEMPORARY TABLE temp_objects_to_cluster (id INT NOT NULL AUTO_INCREMENT, ranking INT, lat FLOAT(10,6), lng FLOAT(10,6), inCluster INT DEFAULT '0', type VARCHAR(300), PRIMARY KEY (id) );
INSERT INTO temp_objects_to_cluster (ranking, lat, lng, type) SELECT ranking, lat, lng, type FROM kidTransitTable;

SET @clusterAccuracy=150000; 
SET @DISTANCE=(10000000 >> zoomLVL) / @clusterAccuracy;
SELECT COUNT(*) INTO @amountOfPoints FROM temp_objects_to_cluster;
SET @cluster_allmarkers_loopcount = 1;
allMarkers:  LOOP
    IF  @cluster_allmarkers_loopcount > @amountOfPoints THEN
        LEAVE allMarkers;
    ELSEIF (NOT EXISTS (SELECT 1 FROM temp_objects_to_cluster WHERE id=@cluster_allmarkers_loopcount AND inCluster=0)) THEN 
        ITERATE allMarkers;
    END  IF;
    SELECT lat, lng, ranking, type INTO @singleLat, @singleLng, @singleRanking, @singleType FROM temp_objects_to_cluster WHERE id=@cluster_allmarkers_loopcount;
    DROP TABLE IF EXISTS pointsInCluster;
    CREATE TEMPORARY TABLE pointsInCluster (id INT NOT NULL AUTO_INCREMENT,lat FLOAT(10,6), lng FLOAT(10,6),PRIMARY KEY (id));

    SET @cluster_nextmarkers_loopcount = @cluster_allmarkers_loopcount+1;
    nextMarkers:  LOOP
        IF  @cluster_nextmarkers_loopcount > @amountOfPoints THEN
            LEAVE nextMarkers;
        ELSEIF (NOT EXISTS (SELECT 1 FROM temp_objects_to_cluster WHERE id=@cluster_nextmarkers_loopcount AND inCluster=0)) THEN 
            ITERATE nextMarkers;
        END  IF;
        SELECT lat, lng INTO @targetLat, @targetLng FROM temp_objects_to_cluster WHERE id=@cluster_nextmarkers_loopcount;
        SET @pixels = ABS(@singleLat-@targetLat) + ABS(@singleLng-@targetLng);
        IF @pixels < @DISTANCE THEN
            BEGIN
                UPDATE temp_objects_to_cluster SET inCluster=1 WHERE id=@cluster_nextmarkers_loopcount;
                INSERT INTO pointsInCluster (lat, lng) VALUES (@targetLat, @targetLng);
            END;
        END IF;
        SET  @cluster_nextmarkers_loopcount = @cluster_nextmarkers_loopcount + 1;
    END LOOP;
    SET @clusterSize=0; SELECT COUNT(*) INTO @clusterSize FROM pointsInCluster;
    IF @clusterSize > 0 THEN 
        BEGIN
            INSERT INTO pointsInCluster (lat, lng) VALUES (@singleLat, @singleLng);
            CALL map__get_midpoint (@midPointLat, @midPointLng);
            INSERT INTO fetchReturnTbl (rowData) VALUES (CONCAT('mapItem//lat:', @midPointLat, '/lng:', @midPointLng, '/size:', @clusterSize+1 ));
        END;
    ELSE
        INSERT INTO fetchReturnTbl (rowData) VALUES (CONCAT('mapItem//lat:', @midPointLat, '/lng:', @midPointLng, '/size:1/ranking:/', @singleRanking,'/type:', @singleType));
    END  IF;
    SET  @cluster_allmarkers_loopcount = @cluster_allmarkers_loopcount + 1;
END LOOP;
END $$

DELIMITER ;

Test call:

CREATE TEMPORARY TABLE kidTransitTable(ranking INT NOT NULL AUTO_INCREMENT, kid INT, lat FLOAT(10,6), lng FLOAT(10,6), type VARCHAR(100), PRIMARY KEY (ranking));
INSERT INTO kidTransitTable(kid, lat, lng, type) values (1, 20.000000, -8.945313, 'Africa1'), (2, 19.669294, -8.242188, 'Africa2'), (3, 17.671045, -9.472656, 'Africa3'), (4, 51.635621, 10.917969, 'Europe1');

CREATE TEMPORARY TABLE fetchReturnTbl (rowData VARCHAR(300));

CALL map__cluster(1);
SELECT * FROM fetchReturnTbl;

What causes phpMyAdmin to not return anything and crash without an error message? How do I fix this stuff? Thanks a bunch!

Community
  • 1
  • 1
i_love_nachos
  • 411
  • 1
  • 4
  • 14
  • To be clear, when you say crash you mean the MySQL daemon needs to be restarted on the server? – Isaac Bennetch Mar 11 '14 at 18:29
  • The sql insertion page displays "loading", endlessly. To fix this, I stop the server on MAMP and start again. – i_love_nachos Mar 11 '14 at 18:42
  • have you tried to test each procedure alone in mysql console client? – Packet Tracer Mar 14 '14 at 08:40
  • Yes. The problem seems to revolve around the clusterers inner loop (named nextMarkers). Simplifying the clusterer by removing the inner loop "fixes" the clusterer procedure, yet then it doesn't really do anthing useful. Ideas? – i_love_nachos Mar 14 '14 at 09:44
  • removing the following line will make the procedure work, but obviously not return the needed result: `UPDATE temp_objects_to_cluster SET inCluster=1 WHERE id=@cluster_nextmarkers_loopcount`. ideas? – i_love_nachos Mar 14 '14 at 10:20

0 Answers0