Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

    View full-size slide

  2. 1
    DATABASES

    IN SPACE

    View full-size slide

  3. @CENITORIA @DARKBOYWONDER @LOGAN_J_HENSON

    View full-size slide

  4. GEOSPATIAL BASICS

    View full-size slide

  5. Usually means “relating to
    geographic position”, or “how
    something’s position relates to
    the Earth’s surface”
    geospatialadjective

    View full-size slide

  6. • 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

    View full-size slide

  7. THE DATE LINE:

    View full-size slide

  8. Matt’s trick to remembering:

    (the equator is a latidude line)
    equilateral
    ! works for me
    WHICH IS LATITUDE, WHICH IS LONGITUDE?

    View full-size slide

  9. S
    Date line
    (ish)
    Date line
    (ish)
    Equator
    Equator
    Prime Meridian

    View full-size slide

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

    View full-size slide

  11. • 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

    View full-size slide

  12. LET’S BUILD AN APP!

    View full-size slide

  13. OUR APP: A RESTAURANT WEB SITE

    View full-size slide

  14. SIMPLE POINTS

    View full-size slide

  15. BASIC DATABASE STORAGE

    View full-size slide

  16. THROW IT ON A MAP (VANILLA JS)

    View full-size slide

  17. THROW IT ON A MAP (VUE2-GOOGLE-MAPS)

    View full-size slide

  18. THROW IT ON A MAP

    View full-size slide

  19. BROWSER GEOLOCATION

    View full-size slide

  20. DISTANCE CALCULATIONS

    View full-size slide

  21. • 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

    View full-size slide

  22. BASIC DISTANCE: GEOLOCATION

    View full-size slide

  23. BASIC DISTANCE: API CALL

    View full-size slide

  24. BASIC DISTANCE: SOME KINDA MATH?

    View full-size slide

  25. BASIC DISTANCE: SOME KINDA MATH?
    DON’T DO IT

    View full-size slide

  26. BASIC DISTANCE: LOGAN’S MAGIC MATH

    View full-size slide

  27. What if we want to do delivery radius?


    “Which restaurant delivers to me?”
    INVERTING OUR LOGIC—“WITHIN RANGE”

    View full-size slide

  28. • 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

    View full-size slide

  29. LOGAN! MAGIC RADIUS!

    View full-size slide

  30. MYSQL VS. POSTGRESQL

    View full-size slide

  31. (It’s all “spatial”)
    GEOMETRY VS.
    GEOGRAPHY

    View full-size slide

  32. • 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

    View full-size slide

  33. POLYGONAL AREAS

    View full-size slide

  34. • So far… squares &
    circles.
    • Story time!
    BASIC POLYGONS: STORY TIME

    View full-size slide

  35. • So far… squares &
    circles.
    • Story time!
    BASIC POLYGONS: STORY TIME

    View full-size slide

  36. • So far… squares &
    circles.
    • Story time!
    BASIC POLYGONS: STORY TIME

    View full-size slide

  37. • Introduced in MySQL 5.5; available in PostgreSQL
    since ~2001 with PostGIS extension.
    GEOMETRY COLUMN TYPE

    View full-size slide

  38. • 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

    View full-size slide

  39. TABLE TWO: POLYGONS
    Also, screenshot from the restaurants db:

    View full-size slide

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

    View full-size slide

  41. COMMON "WHERE ST_*” PATTERNS

    View full-size slide

  42. • 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

    View full-size slide

  43. IMPORTING DATA

    View full-size slide

  44. • Data stored as binary; convert WKT text to binary
    using ST_* methods
    TEXT-TO-GEOMETRY CONVERSION
    • ST_GeomFromText
    • ST_GeomFromGeoJSON

    View full-size slide

  45. TEXT-TO-GEOMETRY CONVERSION (CONT)

    View full-size slide

  46. • 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

    View full-size slide

  47. • 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

    View full-size slide

  48. • 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

    View full-size slide

  49. • 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

    View full-size slide

  50. RETRIEVING DATA

    View full-size slide

  51. • 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


    View full-size slide

  52. PRO TIP: “HIDE” GEOMETRY FROM JSON
    JSON-ENCODING BINARY ===

    View full-size slide

  53. • 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

    View full-size slide

  54. ST* USING QUERY BUILDER RAW

    View full-size slide

  55. EXPORT TO GEOJSON

    View full-size slide

  56. GOOGLE MAPS

    (OR OTHER MAPS)
    INTERMEDIATE TRICKS

    View full-size slide

  57. • 300,000 records = lots of 500
    errors
    • Load subset instead… but
    which subset? Listen to map
    boundary changes.
    LISTEN ON MOVE/ZOOM

    View full-size slide

  58. • 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

    View full-size slide

  59. EMBED GEOJSON ON YOUR MAPS!

    View full-size slide

  60. Look how far
    we’ve come.

    View full-size slide

  61. • 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

    View full-size slide

  62. LARAVEL MYSQL SPATIAL PACKAGE /
    LARAVEL POSTGIS PACKAGE
    HTTPS://GITHUB.COM/GRIMZY/LARAVEL-MYSQL-SPATIAL
    HTTPS://GITHUB.COM/NJBARRETT/LARAVEL-POSTGIS

    View full-size slide

  63. • Similar calculations, but directly in PHP
    GEOPHP
    HTTPS://GEOPHP.NET/

    View full-size slide

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

    View full-size slide

  65. CONCLUSION
    https://github.com/mattstauffer/alberts-avocado-toast

    View full-size slide