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

▶
geographic features in mysql
SMART_READER_LITE
LIVE PREVIEW

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

Sep 06, 2023 599 likes •1.14k views

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

slide-1
SLIDE 1

Geographic Features in MySQL

Tibor Korocz Percona

slide-2
SLIDE 2

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?

slide-3
SLIDE 3

3

Spatial Reference System

slide-4
SLIDE 4

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.

slide-5
SLIDE 5

5

SRIDs

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

slide-6
SLIDE 6

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...
slide-7
SLIDE 7

Distance Calculation

Click to add text

slide-8
SLIDE 8

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
slide-9
SLIDE 9

9

slide-10
SLIDE 10

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.

slide-11
SLIDE 11

11

Distance between London and San Francisco

slide-12
SLIDE 12

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.

slide-13
SLIDE 13

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.

slide-14
SLIDE 14

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.

slide-15
SLIDE 15

15

Is that correct?

Based on https://www.distancecalculator.net

slide-16
SLIDE 16

16

ST_Distance in MySQL 8

Same result because default SRID is 0.

slide-17
SLIDE 17

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

slide-18
SLIDE 18

18

Using SRID 4326 - GPS - Latitude - Longitude

slide-19
SLIDE 19

19

ST_Distance_Sphere works as well

slide-20
SLIDE 20

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

slide-21
SLIDE 21

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

  • f 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)

slide-22
SLIDE 22

22

Calculate distance

slide-23
SLIDE 23

23

Using Index

slide-24
SLIDE 24

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

slide-25
SLIDE 25

25

slide-26
SLIDE 26

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.
slide-27
SLIDE 27

Near by me

Click to add text

slide-28
SLIDE 28

28

What is around me?

slide-29
SLIDE 29

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

slide-30
SLIDE 30

30

One option is using Distance

But we have to be careful with SRIDS.

slide-31
SLIDE 31

31

Zip codes around me

slide-32
SLIDE 32

32

Pt2 does not have specified SRID

slide-33
SLIDE 33

33

Using a rectangle

slide-34
SLIDE 34

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

slide-35
SLIDE 35

35

Using a rectangle

slide-36
SLIDE 36

36

Using Spatial Index

slide-37
SLIDE 37

37

Using a Polygon

slide-38
SLIDE 38

38

Using a Polygon

slide-39
SLIDE 39

39

Using a Polygon

slide-40
SLIDE 40

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.
slide-41
SLIDE 41

A real world usecase

slide-42
SLIDE 42

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

  • n the map and they would like to know

which car crossed that shape.

slide-43
SLIDE 43

Possible Solution?

slide-44
SLIDE 44

44

ST_Envelope

slide-45
SLIDE 45

45

How many angles can have a polygon?

slide-46
SLIDE 46

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

slide-47
SLIDE 47

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.

slide-48
SLIDE 48

Conclusion

slide-49
SLIDE 49

Questions?

slide-50
SLIDE 50

Thank You.

slide-51
SLIDE 51

51

Rate My Session

slide-52
SLIDE 52

52

Thank You Sponsors!!