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

More Decks by Matt Stauffer

Other Decks in Technology


  1. Usually means “relating to geographic position”, or “how something’s position

    relates to the Earth’s surface” geospatialadjective
  2. • 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
  3. Matt’s trick to remembering: 
 (the equator is a latidude

    line) equilateral ! works for me WHICH IS LATITUDE, WHICH IS LONGITUDE?
  4. 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
  5. • 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
  6. • 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
  7. What if we want to do delivery radius?

    restaurant delivers to me?” INVERTING OUR LOGIC—“WITHIN RANGE”
  8. • 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
  9. • 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
  10. • Introduced in MySQL 5.5; available in PostgreSQL since ~2001

    with PostGIS extension. GEOMETRY COLUMN TYPE
  11. • Text representation of a point:

    representation of a shape:
  12. 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
  13. • 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
  14. • Data stored as binary; convert WKT text to binary

    using ST_* methods TEXT-TO-GEOMETRY CONVERSION • ST_GeomFromText • ST_GeomFromGeoJSON
  15. • 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
  16. • 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
  17. • 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
  18. • 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
  19. • 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
  20. • Get familiar with DB!::Raw • Query!::orderByRaw,

    • Restaurant!::selectRaw(‘Spatial Stuff’)
 !->whereRaw(‘Spatial stuff’)
 !->orderByRaw(‘other Spatial Stuff’)
 !->get(); QUERY BUILDER
  21. • 300,000 records = lots of 500 errors • Load

    subset instead… but which subset? Listen to map boundary changes. LISTEN ON MOVE/ZOOM
  22. • 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
  23. • 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