mysql geographic features in - percona
TRANSCRIPT
![Page 1: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/1.jpg)
Geographic Features in MySQL
Tibor KoroczPercona
![Page 2: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/2.jpg)
2
What do I try to answer today?
- Can MySQL 8 help us with the common usecases?- Distance Calculation.- What is near by me?
- Does MySQL 8 give us better options/solutions for these problems than MySQL 5.7?
![Page 3: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/3.jpg)
3
Spatial Reference System
![Page 4: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/4.jpg)
4
Projections - Geography
Projections - SRID 0 in in MySQL since many years. But MySQL did not know what the coordinates mean. In MySQL 8 finally we have this metadata which can put these coordinates in context. Projections are Cartesian systems, meaning that they are flat planes with orthogonal X and Y axes.
Geography - Geographic SRSs are ellipsoids with latitude and longitude coordinates. All the meridians meet at the North Pole and at the South Pole. The length of a degree of longitude varies from 0 to more than 111km. Functions had to be changed in MySQL 8 to support Geographic calculations.
![Page 6: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/6.jpg)
6
What is new?
- Spatial Reference Systems (SRIDs)- 4326 = WGS 84 (“World Geodetic System - GPS coordinates”)- 3857 = WGS 84 / Pseudo-Mercator -- Spherical Mercator, Google Maps,
OpenStreetMap, Bing, ArcGIS, ESRI- SRID aware spatial datatypes
- CREATE TABLE table1 (g GEOMETRY SRID 4326);- SRID aware spatial indexes
- CREATE TABLE table1 (g GEOMETRY SRID 4326 NOT NULL, SPATIAL INDEX(g));
- SRID aware spatial functions- ST_Distance, ST_Within, ST_Intersects, ST_Contains, ST_Crosses, etc...
![Page 7: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/7.jpg)
Distance CalculationClick to add text
![Page 8: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/8.jpg)
8
MySQL 5.7 already has spatial data types!
- Most of the people does not realise but MySQL already supports spatial data types, like:
- GEOMETRY- POINT- LINESTRING- POLYGON
![Page 9: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/9.jpg)
9
![Page 10: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/10.jpg)
10
Distance calculation in MySQL 5.7
- We could use ST_Distance function.
It does not give us an actual distance in mile or kilometers as it does not take into account that we have latitude and longitude,rather than X and Y on plane.
![Page 11: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/11.jpg)
11
Distance between London and San Francisco
![Page 12: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/12.jpg)
12
ST_GeomFromText(wkt[, srid])
ST_GeomFromText(wkt[, srid]) - Constructs a geometry value of any type using its WKT representation and SRID.If the geometry argument is NULL or not a syntactically well-formed geometry, or if the SRID argument is NULL, the return value is NULL.
WKT - The Well-Known Text (WKT) representation of geometry values is designed for exchanging geometry data in ASCII form.
Example: POINT(10 15)
Be careful, this is not the same like POINT(10,5). This is geometry type in MySQL.
![Page 13: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/13.jpg)
13
Haversine Formula
- Use stored function and implement haversine formula,create DEFINER = CURRENT_USER function haversine_distance_sp (lat1 double, lon1 double, lat2 double, lon2 double) returns double begin…. set phi1 = radians(lat1); set phi2 = radians(lat2); set d_phi = radians(lat2-lat1); set d_lambda = radians(lon2-lon1); set a = sin(d_phi/2) * sin(d_phi/2) + cos(phi1) * cos(phi2) * sin(d_lambda/2) * sin(d_lambda/2); set c = 2 * atan2(sqrt(a), sqrt(1-a)); set d = R * c; return d; End; Full function can be found here.
![Page 14: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/14.jpg)
14
MySQL 5.7 introduced ST_Distance_SphereST_Distance_Sphere - Returns the minimum spherical distance between two points and/or multipoints on a sphere, in meters, or NULL if any geometry argument is NULL or empty.
![Page 16: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/16.jpg)
16
ST_Distance in MySQL 8
Same result because default SRID is 0.
![Page 17: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/17.jpg)
17
Using SRID 4326 - GPS - Latitude - LongitudeSET @sanfrancisco = ST_GeomFromText('POINT(122.4 37.8)',4326);ERROR 3617 (22S03): Latitude 122.400000 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000].
![Page 18: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/18.jpg)
18
Using SRID 4326 - GPS - Latitude - Longitude
![Page 19: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/19.jpg)
19
ST_Distance_Sphere works as well
![Page 20: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/20.jpg)
20
Datatypes with SRID in MySQL 8
CREATE TABLE `test_distance` ( `id` int(10) NOT NULL AUTO_INCREMENT, `city` varchar(50) DEFAULT NULL, `latitude` double(15,5) DEFAULT NULL, `longitude` double(15,5) DEFAULT NULL, `pt` point NOT NULL /*!80003 SRID 4326 */, PRIMARY KEY (`id`), SPATIAL KEY `idx_spatial_pt` (`pt`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
![Page 21: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/21.jpg)
21
Populate a table
insert into test_distance (city,latitude,longitude,pt) values ('london','51.509865','-0.118092',point(51.509865,-0.118092));
ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column 'pt'. The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID of the geometry or the SRID property of the column.
We have to define the correct SRID.
insert into test_distance (city,latitude,longitude,pt) values ('london','51.509865','-0.118092',ST_GeomFromText('point(51.509865 -0.118092)',4326));Query OK, 1 row affected (0.09 sec)
![Page 22: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/22.jpg)
22
Calculate distance
![Page 23: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/23.jpg)
23
Using Index
![Page 24: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/24.jpg)
24
What is R-Tree?
What I could found in MySQL manual:
“A tree data structure used for spatial indexing of multi-dimensional data such as geographical coordinates, rectangles or polygons.”
What IBM’s manual says:
“The R-tree access method organizes data in a tree-shaped structure called an R-tree index. The index uses a bounding box, which is a rectilinear shape that completely contains the bounded object or objects. Bounding boxes can enclose data objects or other bounding boxes.”
![Page 25: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/25.jpg)
25
![Page 26: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/26.jpg)
26
Distance - Conclusion
- MySQL 5.7 could use only SRID 0.- You had to calculate the distance by yourself or use ST_Distance_Shepere which does the
calculation.
- MySQL 8 can use different SRIDs.- The default SRID is still SRID 0.- You always have to make sure you are using the right SRID.- The result can be different.
![Page 27: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/27.jpg)
Near by meClick to add text
![Page 28: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/28.jpg)
28
What is around me?
![Page 29: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/29.jpg)
29
US zipcodes
CREATE TABLE `us` ( `id` int(10) NOT NULL AUTO_INCREMENT, `zipcode` char(5) DEFAULT NULL, `city` varchar(50) DEFAULT NULL,…. `latitude` double(15,5) DEFAULT NULL, `longitude` double(15,5) DEFAULT NULL, `some_field` tinyint(4) DEFAULT NULL, `pt` point NOT NULL /*!80003 SRID 4326 */, `pt2` point NOT NULL, PRIMARY KEY (`id`), SPATIAL KEY `idx_spatial_pt2` (`pt2`), SPATIAL KEY `idx_spatial_pt` (`pt`)) ENGINE=InnoDB AUTO_INCREMENT=40976 DEFAULT CHARSET=latin1
![Page 30: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/30.jpg)
30
One option is using Distance
But we have to be careful with SRIDS.
![Page 31: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/31.jpg)
31
Zip codes around me
![Page 32: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/32.jpg)
32
Pt2 does not have specified SRID
![Page 33: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/33.jpg)
33
Using a rectangle
![Page 34: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/34.jpg)
34
Using a rectangle
set @radius = 2;set @a= (select -74.0059413);set @b= (select 40.7127837);set @a1= (select (@a+(@radius/111)));set @a2= (select (@a-(@radius/111)));set @b1= (select (@b+(@radius/111)));set @b2= (select (@b-(@radius/111)));
SELECT id,city,zipcode,latitude,longitude,ST_Distance_Sphere(ST_SRID(Point(@a, @b),4326), pt) AS distance_in_metersFROM usWHERE ST_Contains(ST_SRID(ST_MakeEnvelope((point(@a1, @b1)), (point(@a2, @b2))),4326), pt);
![Page 35: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/35.jpg)
35
Using a rectangle
![Page 36: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/36.jpg)
36
Using Spatial Index
![Page 37: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/37.jpg)
37
Using a Polygon
![Page 38: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/38.jpg)
38
Using a Polygon
![Page 39: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/39.jpg)
39
Using a Polygon
![Page 40: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/40.jpg)
40
Near by me - Conclusion
- We can easily use distance calculation and list the results.- MySQL can create an envelope which covers the area where we are looking for.- We can simply use polygons.- They can use spatial indexes.
![Page 41: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/41.jpg)
A real world usecase
![Page 42: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/42.jpg)
42
The Problem
- Customer is collecting GPS data from cars in every few seconds.- One GPS data is one row in the table.- They are using longitude and latitude.
Their customer can draw any kind of shape on the map and they would like to know which car crossed that shape.
![Page 43: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/43.jpg)
Possible Solution?
![Page 44: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/44.jpg)
44
ST_Envelope
![Page 45: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/45.jpg)
45
How many angles can have a polygon?
![Page 46: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/46.jpg)
46
A large polygon
SET @shape = ST_GEOMFROMTEXT( 'POLYGON((-74.0812570352 40.6940266249, -73.8395578164 40.6940266249, -73.8395578164 40.8386006946, -74.0812570352 40.8386006946, 74.0912570352 40.9386006946…..-74.0812570352 40.6940266249) )');
SELECT id,car FROM table_car_gpsWHERE ST_CONTAINS(ST_SRID(@shape,4326), gps_points);
![Page 47: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/47.jpg)
47
ExteriorRing - InteriorRing
ST_ExteriorRing(poly)
Returns the exterior ring of the Polygon value poly as a LineString.
ST_InteriorRingN(poly, N)
Returns the N-th interior ring for the Polygon value poly as a LineString. Rings are numbered beginning with 1.
![Page 48: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/48.jpg)
Conclusion
![Page 49: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/49.jpg)
Questions?
![Page 50: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/50.jpg)
Thank You.
![Page 51: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/51.jpg)
51
Rate My Session
![Page 52: MySQL Geographic Features in - Percona](https://reader031.vdocuments.us/reader031/viewer/2022012211/61df35bbf039665aef4c0dcc/html5/thumbnails/52.jpg)
52
Thank You Sponsors!!