$30 off During Our Annual Pro Sale. View Details »

Databases in Space

Databases in Space

Simple tricks for using geometry fields in MySQL and Laravel

Matt Stauffer

March 06, 2019
Tweet

More Decks by Matt Stauffer

Other Decks in Technology

Transcript

  1. 1 DATABASES
 IN SPACE

  2. None
  3. 1 DATABASES
 IN SPACE

  4. STORY TIME

  5. None
  6. None
  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!

  64. None
  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