Databases in Space

Simple tricks for using geometry fields in MySQL and Laravel

Transcript

1. 1 DATABASES
 IN SPACE
3. 1 DATABASES
 IN SPACE
4. STORY TIME
7. @CENITORIA @DARKBOYWONDER @LOGAN_J_HENSON
8. GEOSPATIAL BASICS
9. Usually means “relating to geographic position”, or “how something’s position relates to the Earth’s surface” geospatialadjective
10. • Latitude: parallel to the equator; north is positive, south is negative • Longitude: perpendicular to the equator; west of Greenwich, England is negative, east of it is positive • Both are 0 at the “center of the world”, which is the equator north/south, and the “prime meridian”, or Greenwich, England, east/west • Resets on the other side of the world, at the antimeridian: Fiji, mainly LATITUDE AND LONGITUDE
11. THE DATE LINE:
12. Matt’s trick to remembering: 
 (the equator is a latidude line) equilateral ! works for me WHICH IS LATITUDE, WHICH IS LONGITUDE?
13. S Date line (ish) Date line (ish) Equator Equator Prime Meridian
14. HOW FAR BETWEEN DEGREES? • All latitude & longitude degrees are not the same distance apart. So there’s no easy formula for “how far is one degree.” • Some folks use degrees, minutes, seconds (51° 28' 38'' N)… just math. +51.477222
15. • GIS: Geographic Information System • OpenGIS: Open spec released by OGC consortium for web geo interop; kinda like the FIG but with less drama and for geospatial • ArcGIS: $$$ Windows desktop software everyone uses; expensive, proprietary, Windows-only, etc. • PostGIS: “Spatial and geographic objects for PostgreSQL” • WKT: “Well-known text”; representing geometry as ASCII • WKB: “Well-known binary”; WKT but blobby • GeoJSON: WKT-ish, but JSON TERMS
16. LET’S BUILD AN APP!
17. OUR APP: A RESTAURANT WEB SITE
18. SIMPLE POINTS
19. BASIC DATABASE STORAGE
20. THROW IT ON A MAP (VANILLA JS)
21. THROW IT ON A MAP (VUE2-GOOGLE-MAPS)
22. THROW IT ON A MAP
23. BROWSER GEOLOCATION
24. DISTANCE CALCULATIONS
25. • Five closest restaurants… how? • Update our geolocation call: • /api/restaurants/near?latitude=-84&longitude=45 • Make a scope on the Restaurant model: • scopeClosestTo($latitude, $longitude) • … sort by… something? BASIC DISTANCE
26. BASIC DISTANCE: GEOLOCATION
27. BASIC DISTANCE: API CALL
28. BASIC DISTANCE: SOME KINDA MATH?
29. BASIC DISTANCE: SOME KINDA MATH? DON’T DO IT
30. BASIC DISTANCE: LOGAN’S MAGIC MATH
31. What if we want to do delivery radius?
 
 “Which restaurant delivers to me?” INVERTING OUR LOGIC—“WITHIN RANGE”
32. • Basic math and stuff in that endpoint • Restaurant longitude +- (delivery_distance / 2) • Restaurant latitude +- (delivery_distance / 2) • SELECT query example for that boundary • BUT WAIT!…. DRAWING SQUARES
33. LOGAN! MAGIC RADIUS!
34. MYSQL VS. POSTGRESQL
35. (It’s all “spatial”) GEOMETRY VS. GEOGRAPHY
36. • Spatial Reference Identifier—for geometries or geographies • MySQL supports over 5000 SR’s • Most common: • SRID 0 (MySQL default) just means “flat” (fine for geometry, not for geography) • SRID 4326 (WGS84) is most common; what GPSes uses • SRID 3857 is what Google Maps and Bing use SRID
37. POLYGONAL AREAS
38. • So far… squares & circles. • Story time! BASIC POLYGONS: STORY TIME
39. • So far… squares & circles. • Story time! BASIC POLYGONS: STORY TIME
40. • So far… squares & circles. • Story time! BASIC POLYGONS: STORY TIME
41. • Introduced in MySQL 5.5; available in PostgreSQL since ~2001 with PostGIS extension. GEOMETRY COLUMN TYPE
42. • Text representation of a point:
 POINT(LAT, LONG),SRID • Text representation of a shape:
 POLYGON((LONG1 LAT1,LONG2 LAT2,LONG3 LAT3,LONG1 AT1),SRID) • Others:
 LINESTRING, MULTIPOINT, MULTIPOLYGON, MULTILINESTRING, GEOMETRYCOLLECTION GEOMETRY COLUMN EXAMPLES
43. TABLE TWO: POLYGONS Also, screenshot from the restaurants db:
44. Geometry methods that can be used for select, order, and where statements (and other things—to cover later) • ST_CONTAINS: second value is within first value • ST_WITHIN: first value is within second value • ST_INTERSECTS: two shapes intersect • ST_TOUCHES: two shapes have touching boundaries ST_* METHODS
45. COMMON "WHERE ST_*” PATTERNS
46. • Centroid is the center point of this shape— technically “the intersection of all straight lines that divide the shape into two parts of equal moment about the line” • In SQL: Centroid(service_regions.shape) • Less fragile “overlaps” by using “centroid is within” CENTROIDS
47. IMPORTING DATA
48. • Data stored as binary; convert WKT text to binary using ST_* methods TEXT-TO-GEOMETRY CONVERSION • ST_GeomFromText • ST_GeomFromGeoJSON
49. TEXT-TO-GEOMETRY CONVERSION (CONT)
50. • Not a “file”; more like a directory of ~10 files • .shp, .shx, and .dbf files have to be in there, but there are usually more • Designed by ESRI—sort of like the spatial equivalent of .xlsx SHAPEFILES
51. • Created by an Internet working group () instead of an existing shadowy monolithic organization • Easy to convert to and from GeoJSON, and viable in any modern tools GEOJSON
52. • Simple stuff. For example: CSV Restaurant Name Latitude Longitude Albert’s Downtown Gainesville 29.6521566 29.6521566 Albert’s Hunters Crossing 29.7014027 -82.3903569 Albert’s Butler Plaza 29.6222603 -82.3813747
53. • GDAL (Geospatial Data Abstraction Library - https:// www.gdal.org/) • Ogr2ogr (https://www.gdal.org/ogr2ogr.html) • brew install gdal • Can convert between all geospatial file formats, and even query properties of one with a SQL-like syntax CONVERTING FILE FORMATS
54. RETRIEVING DATA
55. • Data stored as binary; convert binary to text using ST_* methods • ST_AsText(some geometry field or content in here) • Humans are bad at reading binary, turns out ! GEOMETRY-TO-TEXT CONVERSION
56. PRO TIP: “HIDE” GEOMETRY FROM JSON JSON-ENCODING BINARY ===
57. • Get familiar with DB!::Raw • Query!::orderByRaw,
 Query!::whereRaw,
 Query!::selectRaw,
 Query!::otherStuff(DB!::Raw()) • Restaurant!::selectRaw(‘Spatial Stuff’)
 !->whereRaw(‘Spatial stuff’)
 !->orderByRaw(‘other Spatial Stuff’)
 !->get(); QUERY BUILDER
58. ST* USING QUERY BUILDER RAW
59. EXPORT TO GEOJSON
60. GOOGLE MAPS
 (OR OTHER MAPS) INTERMEDIATE TRICKS
61. • 300,000 records = lots of 500 errors • Load subset instead… but which subset? Listen to map boundary changes. LISTEN ON MOVE/ZOOM
62. • Each data point lookup needs to be fast • Embed less data; look up data when they click the point • Simpler initial list: • [{lat: 1, long: 2, name: ‘abc’}, {}, {}] MORE DATA ON POPUP
63. EMBED GEOJSON ON YOUR MAPS!
65. Look how far we’ve come.
66. • Laravel friends GeoCodio: https://www.geocod.io/ • Google Maps Platform: https://developers.google.com/maps/ documentation/ • MySQL spatial types documentation: https://dev.mysql.com/doc/ refman/5.7/en/spatial-type-overview.html • Similar work with Algolia: https://scotch.io/tutorials/achieving- geo-search-with-laravel-scout-and-algolia • Intro tutorial: https://medium.com/@brice_hartmann/getting- started-with-geospatial-data-in-laravel-94502dc74d55 LINKS
67. LARAVEL MYSQL SPATIAL PACKAGE / LARAVEL POSTGIS PACKAGE HTTPS://GITHUB.COM/GRIMZY/LARAVEL-MYSQL-SPATIAL HTTPS://GITHUB.COM/NJBARRETT/LARAVEL-POSTGIS
68. • Similar calculations, but directly in PHP GEOPHP HTTPS://GEOPHP.NET/
69. • Wicket (http://arthur-e.github.io/Wicket/sandbox-gmaps3.html) • GeoJSON by Civil Service USA (https://github.com/CivilServiceUSA/ civil-services-geojson-app) • Sequel Pro USEFUL TOOLS
70. CONCLUSION https://github.com/mattstauffer/alberts-avocado-toast