Geographic Features in MySQL Tibor Korocz Percona What do I try - PowerPoint PPT Presentation

Geographic Features in MySQL Tibor Korocz Percona

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

Spatial Reference System 3

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

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

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

Distance Calculation Click to add text

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

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

Distance between London and San Francisco 11

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

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

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

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

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

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

Using SRID 4326 - GPS - Latitude - Longitude 18

ST_Distance_Sphere works as well 19

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

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

Calculate distance 22

Using Index 23

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

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

Near by me Click to add text

What is around me? 28

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

One option is using Distance But we have to be careful with SRIDS. 30

Zip codes around me 31

Pt2 does not have specified SRID 32

Using a rectangle 33

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_meters FROM us WHERE ST_Contains(ST_SRID(ST_MakeEnvelope( (point(@a1, @b1)), (point(@a2, @b2))),4326), pt); 34

Using a rectangle 35

Using Spatial Index 36

Using a Polygon 37

Using a Polygon 38

Using a Polygon 39

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

A real world usecase

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

Possible Solution?

ST_Envelope 44

How many angles can have a polygon? 45

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_gps WHERE ST_CONTAINS(ST_SRID(@shape,4326), gps_points); 46

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

Conclusion

Questions?

Thank You.

Rate My Session 51

Thank You Sponsors!! 52