T0uX0sEM

1-- Table of locations, around 0.5 million points
2CREATE TABLE locations (
3  id INT(11) NOT NULL PRIMARY KEY,
4  longitude float(11,6) DEFAULT NULL,
5  latitude float(10,6) DEFAULT NULL,
6  lonLat point NOT NULL DEFAULT '',
7  boundaryId INT(11) DEFAULT NULL
8) ENGINE=MyISAM;
9
10-- Populate the lonLat field
11UPDATE locations SET lonLat = POINTFROMTEXT(CONCAT('point(',longitude,' ',latitude,')')) WHERE longitude IS NOT NULL AND latitude IS NOT NULL;
12
13-- Add spatial index on lonLat
14ALTER TABLE locations ADD SPATIAL INDEX(lonLat);
15
16-- Table of around 350 exact boundaries, some overlapping
17CREATE TABLE IF NOT EXISTS boundaries (
18  id INT(11) NOT NULL PRIMARY KEY,
19  llgeom geometry NOT NULL
20);
21
22-- Add spatial index on boundary llgeom:
23ALTER TABLE `boundaries` ADD SPATIAL(`llgeom`);
24	
25UPDATE locations
26LEFT JOIN boundaries ON Within(lonLat, llgeom)
27SET boundaryId = boundaries.id;
28	
29UPDATE locations
30LEFT JOIN boundaries ON ST_Within(lonLat, llgeom)
31SET boundaryId = boundaries.id;
32	
33BEGIN
34  DECLARE b, loc_id INT;
35  DECLARE loc_point point;
36  DECLARE cur_1 CURSOR FOR SELECT lonLat, id FROM locations;
37  DECLARE CONTINUE HANDLER FOR NOT FOUND
38  SET b = 1;
39  OPEN cur_1;
40  REPEAT
41    FETCH cur_1 INTO loc_point, loc_id;
42    BEGIN
43        DECLARE a TEXT;
44        DECLARE cur_2 CURSOR FOR SELECT id FROM boundaries WHERE Within(loc_point, boundaries.llgeom);
45        OPEN cur_2;
46        FETCH cur_2 INTO a;
47        UPDATE locations SET boundaryId = a WHERE id = loc_id;
48        CLOSE cur_2;
49    END;
50    UNTIL b = 1
51  END REPEAT;
52  CLOSE cur_1;
53END