Spatial Index

Talk about how spatial index works.

Transcript

1. SPATIAL INDEX DAVIDE MENDOLIA @DAVIDEME HTTP://DAVIDEME.COM
2. SPACE
3. INDEX CARDS
4. WHAT THEY ARE MADE FOR ?
5. BURGERS
6. HELP YOU TO FIND BURGERS!
7. WHAT THEY ARE MADE FOR ? ACCESS GEOGRAPHICAL DATA BOUNDING BOX QUERIES IN A EFFICIENT WAY
8. LET’S FIND OUT WHERE IS THE CLOSEST MCDONALDS WITH THIS BURGER SALAD
9. WHERE ARE WE ?
10. 40.434905,-4.552728
11. LATITUDE 40.434905 LONGITUDE -4.552728
12. LONGITUDE [-180:ROYAL OBSERVATORY:180] LATITUDE [-90:EQUATOR:90]
13. LET’S DEFINE A BOUNDING BOX
14. +/- 50 KM DISTANCE
15. NW : [40.919402,-5.020975] SE : [39.993614,-3.826212]
16. LET’S TRY THIS!
17. HOW INDEX MULTI-COLUMN WORKS
18. FIRST COLUMN: LONGITUDE
19. SECOND COLUMN: LATITUDE
20. HOW SPATIAL INDEX CAN OPTIMIZE IT ?
21. HOW SPATIAL INDEX CAN OPTIMIZE IT ? USING ONLY ONE INDEXED COLUMN CONTAINING A GEOHASH FUNCTION BASED ON LONGITUDE AND LATITUDE
22. LET’S SPLIT THE WORLD
23. LET’S SPLIT THE WORLD AGAIN
24. UNTIL 21333102301001102233031023.....
25. OK , THAT’S NICE WHEN BUT WHEN SHOULD I STOP TO SPLIT THE WORLD ?
26. IT DEPENDS !
27. IT DEPENDS OF WHAT ? THE ACCURACY OF THE LOCATION WITH GPS YOU WANT ACCURACY IN METERS LET’S USE 1 METER
28. ERATOSTHENES AND PYTHAGORAS
29. THE EARTH IS A GLOBE
30. DISTANCE BETWEEN LONGITUDE VARY DEPENDING ON THE LATITUDE
31. SO THE PRECISION WILL VARY DEPENDING ON LATITUDE
32. PRECISION DEPEND ON HOW MANY CELL YOU SPLIT THE WORLD 4, 16, 256,... HOW MANY DIGITS YOU WANT TO USE 1M 20M 100M 4 16 256 25 21 19 13 11 10 7 6 5
33. HOW TO TRANSFORM LAT-LON IN THE CELL IDENTIFIER
34. HOW TO TRANSFORM LAT-LON IN THE CELL IDENTIFIER
35. HOW TO TRANSFORM LAT-LON IN THE CELL IDENTIFIER
36. HOW TO QUERY IT ? 6,12 6,13 6,14 6,15 6,16 6,176,18 6,19 6,206,21 7,12 7,13 7,14 7,15 7,16 7,177,18 7,19 7,207,21 8,12 8,13 8,14 8,15 8,16 8,178,18 8,19 8,208,21 9,12 9,13 9,14 9,15 9,16 9,179,18 9,19 9,209,21 10,12 10,13 10,14 10,15 10,16 10,17 10,18 10,19 10,20 10,21 11,12 11,13 11,14 11,15 11,16 11,17 11,18 11,19 11,20 11,21 6,11 7,11 8,11 9,11 10,11 11,11
37. NW-SE 6,12 6,13 6,14 6,15 6,16 6,176,18 6,19 6,206,21 7,12 7,13 7,14 7,15 7,16 7,177,18 7,19 7,207,21 8,12 8,13 8,14 8,15 8,16 8,178,18 8,19 8,208,21 9,12 9,13 9,14 9,15 9,16 9,179,18 9,19 9,209,21 10,12 10,13 10,14 10,15 10,16 10,17 10,18 10,19 10,20 10,21 11,12 11,13 11,14 11,15 11,16 11,17 11,18 11,19 11,20 11,21 6,11 7,11 8,11 9,11 10,11 11,11
38. RETRIEVE ALL THE CELL 6,12 6,13 6,14 6,15 6,16 6,176,18 6,19 6,206,21 7,12 7,13 7,14 7,15 7,16 7,177,18 7,19 7,207,21 8,12 8,13 8,14 8,15 8,16 8,178,18 8,19 8,208,21 9,12 9,13 9,14 9,15 9,16 9,179,18 9,19 9,209,21 10,12 10,13 10,14 10,15 10,16 10,17 10,18 10,19 10,20 10,21 11,12 11,13 11,14 11,15 11,16 11,17 11,18 11,19 11,20 11,21 6,11 7,11 8,11 9,11 10,11 11,11
39. ADVANTAGES ONLY NEED TO ACCESS TO ONE COLUMN EASY TO CACHE IN MEMCACHE OR HASHMAP COMPATIBLE WITH PARTITIONED DATABASES
40. QUESTIONS ?