Geographic Features in MySQL

1 .Geographic Features in MySQL Tibor Korocz Percona 

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? 2 

3 .Spatial Reference System 3 

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. 4 

5 .SRIDs SRID details example: http://epsg.io/3857 5 

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... 6 

7 .Distance Calculation Click to add text 

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 8 

9 .9 

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. 10 

11 .Distance between London and San Francisco 11 

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. 12 

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. 13 

14 .MySQL 5.7 introduced ST_Distance_Sphere ST_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. 14 

15 .Is that correct? Based on https://www.distancecalculator.net 15 

16 .ST_Distance in MySQL 8 Same result because default SRID is 0. 16 

17 .Using SRID 4326 - GPS - Latitude - Longitude SET @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]. 17 

18 .Using SRID 4326 - GPS - Latitude - Longitude 18 

19 .ST_Distance_Sphere works as well 19 

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 20 

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) 21 

22 .Calculate distance 22 

23 .Using Index 23 

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.” 24 

25 .25 

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. 26 

27 .Near by me Click to add text 

28 .What is around me? 28 

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 29