Databases in Space

Databases in Space

Simple tricks for using geometry fields in MySQL and Laravel

60187fe0ab07ea5a46572a3ab05f61dd?s=128

Matt Stauffer

March 06, 2019
Tweet

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