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 Slide

  2. View Slide

  3. 1
    DATABASES

    IN SPACE

    View Slide

  4. STORY TIME

    View Slide

  5. View Slide

  6. View Slide

  7. @CENITORIA @DARKBOYWONDER @LOGAN_J_HENSON

    View Slide

  8. GEOSPATIAL BASICS

    View Slide

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

    View Slide

  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

    View Slide

  11. THE DATE LINE:

    View Slide

  12. Matt’s trick to remembering:

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  16. LET’S BUILD AN APP!

    View Slide

  17. OUR APP: A RESTAURANT WEB SITE

    View Slide

  18. SIMPLE POINTS

    View Slide

  19. BASIC DATABASE STORAGE

    View Slide

  20. THROW IT ON A MAP (VANILLA JS)

    View Slide

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

    View Slide

  22. THROW IT ON A MAP

    View Slide

  23. BROWSER GEOLOCATION

    View Slide

  24. DISTANCE CALCULATIONS

    View Slide

  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

    View Slide

  26. BASIC DISTANCE: GEOLOCATION

    View Slide

  27. BASIC DISTANCE: API CALL

    View Slide

  28. BASIC DISTANCE: SOME KINDA MATH?

    View Slide

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

    View Slide

  30. BASIC DISTANCE: LOGAN’S MAGIC MATH

    View Slide

  31. What if we want to do delivery radius?


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

    View Slide

  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

    View Slide

  33. LOGAN! MAGIC RADIUS!

    View Slide

  34. MYSQL VS. POSTGRESQL

    View Slide

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

    View Slide

  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

    View Slide

  37. POLYGONAL AREAS

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

  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

    View Slide

  45. COMMON "WHERE ST_*” PATTERNS

    View Slide

  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

    View Slide

  47. IMPORTING DATA

    View Slide

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

    View Slide

  49. TEXT-TO-GEOMETRY CONVERSION (CONT)

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  54. RETRIEVING DATA

    View Slide

  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


    View Slide

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

    View Slide

  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

    View Slide

  58. ST* USING QUERY BUILDER RAW

    View Slide

  59. EXPORT TO GEOJSON

    View Slide

  60. GOOGLE MAPS

    (OR OTHER MAPS)
    INTERMEDIATE TRICKS

    View Slide

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

    View Slide

  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

    View Slide

  63. EMBED GEOJSON ON YOUR MAPS!

    View Slide

  64. View Slide

  65. Look how far
    we’ve come.

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide