pbASe7Zd

1DROP DATABASE IF EXISTS `gis`;
2
3CREATE DATABASE IF NOT EXISTS `gis`
4    DEFAULT CHARACTER SET utf8
5    DEFAULT COLLATE utf8_general_ci;
6
7USE `gis`;
8
9DROP TABLE IF EXISTS user;
10CREATE TABLE IF NOT EXISTS user (
11    id           INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
12    firstname    VARCHAR(48) NOT NULL,
13    sex          ENUM('male', 'female') NOT NULL,
14    age          TINYINT UNSIGNED NOT NULL,
15    position     POINT NOT NULL
16);
17ALTER TABLE user ADD INDEX sex__age (sex, age);
18ALTER TABLE user ADD SPATIAL INDEX(position);
19
20INSERT INTO user (firstname, sex, age, position) VALUES ('Alexander', 'male', 34, POINT(63.429909, 10.393035));
21INSERT INTO user (firstname, sex, age, position) VALUES ('Dina', 'female', 21, POINT(63.426300, 10.392481));
22INSERT INTO user (firstname, sex, age, position) VALUES ('Martin', 'male', 32, POINT(63.422304, 10.432027));
23INSERT INTO user (firstname, sex, age, position) VALUES ('Tina', 'female', 19, POINT(63.430603, 10.373038));
24INSERT INTO user (firstname, sex, age, position) VALUES ('Kristin', 'female', 20, POINT(63.434858, 10.411359));
25INSERT INTO user (firstname, sex, age, position) VALUES ('Mette', 'female', 33, POINT(63.420422, 10.403811));
26INSERT INTO user (firstname, sex, age, position) VALUES ('Andres', 'male', 34, POINT(63.419488, 10.395722));
27INSERT INTO user (firstname, sex, age, position) VALUES ('Sandra', 'female', 25, POINT(63.432053, 10.408738));
28INSERT INTO user (firstname, sex, age, position) VALUES ('Kine', 'female', 29, POINT(63.432302, 10.412643));
29INSERT INTO user (firstname, sex, age, position) VALUES ('Henrik', 'male', 25, POINT(63.421055, 10.443288)); 
30     
31EXPLAIN SELECT id, firstname, sex, age FROM user WHERE sex = 'male' AND age BETWEEN 25 AND 32; 
32     
33SET @distance = 3.5;
34SET @my_place_lng = 63.431592;
35SET @my_place_lat = 10.396210;
36
37SELECT
38    id,
39    firstname,
40    sex,
41    age,
42    ST_Distance_Sphere(Point(@my_place_lng, @my_place_lat), position) AS distance_from_me
43FROM user
44WHERE
45    ST_Contains(ST_MakeEnvelope(
46        Point((@my_place_lng+(@distance/111)), (@my_place_lat+(@distance/111))),
47        Point((@my_place_lng-(@distance/111)), (@my_place_lat-(@distance/111)))
48    ), position)
49ORDER BY distance_from_me ASC;