· 7 years ago · Sep 26, 2018, 05:18 PM
1--
2-- This example inserts a massive set of lat/lng points into a database, then selects them back
3-- grouped into grid-squares
4--
5
6 -- Switch to the test table (don't pollute the real database)
7 USE `test`;
8
9--
10-- CREATE SOME DUMMY DATA
11--
12
13 -- Create our points table
14 -- using FLOAT(10,6) as anything more accurate is a bit useless (for my application anyhow)
15 -- the index on lat and lng is important for speed
16 DROP TABLE IF EXISTS `points`;
17 CREATE TABLE `points` (
18 `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
19 `lat` FLOAT(10,6) NOT NULL,
20 `lng` FLOAT(10,6) NOT NULL,
21 INDEX `latlng` (`lat`,`lng`)
22 ) ENGINE=InnoDB;
23
24 -- Insert a blank row into the db (you'll see why in a moment)
25 INSERT INTO `points` (lat, lng) VALUES (0, 0);
26
27 -- Using that first "seed" insert new rows, repeat this statement to get an exponential amount of rows
28 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 2
29 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 4
30 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 8
31 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 16
32 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 32
33 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 64
34 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 128
35 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 256
36 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 512
37 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 1024
38 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 2048
39 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 4096
40 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 8192
41 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 16384
42 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 32768
43 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 65536
44 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 131072
45 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 262144
46 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 524288
47 INSERT INTO `points` (lat, lng) SELECT (RAND() * 180 - 90), (RAND() * 360 - 180) FROM `points`; -- 1048576
48
49 -- If you've gone the whole hog and inserted a million rows then you might need to wait till your index is fully populated before continueing...
50 -- but really, that last statement took 25 minutes
51 -- go put the kettle on :-)
52
53 -- Delete that first row with crap values
54 DELETE FROM `points` WHERE lat=0 AND lng=0;
55
56--
57-- SELECT SOME GROUPED DATA
58--
59
60 SET @lng_min = -20; -- LEFT
61 SET @lng_max = 20; -- RIGHT
62 SET @lat_min = 0; -- TOP
63 SET @lat_max = 20; -- BOTTOM
64 SET @gridsize = 5; -- for rounding logic
65
66 -- Select the points that fit within the grid, grouped into grid-squares
67 SELECT
68 (ROUND(lat / @gridsize) * @gridsize) AS rlat,
69 (ROUND(lng / @gridsize) * @gridsize) AS rlng,
70 AVG(lat) AS alat,
71 AVG(lng) AS alng,
72 COUNT(*) AS numpoints
73 FROM `points`
74 WHERE
75 lat >= @lat_min AND
76 lat <= @lat_max AND
77 lng >= @lng_min AND
78 lng <= @lng_max
79 GROUP BY rlat, rlng;