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. 2.
  2. 5.
  3. 6.
  4. 9.

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

    relates to the Earth’s surface” geospatialadjective
  5. 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
  6. 12.

    Matt’s trick to remembering: 
 (the equator is a latidude

    line) equilateral ! works for me WHICH IS LATITUDE, WHICH IS LONGITUDE?
  7. 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
  8. 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
  9. 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
  10. 31.

    What if we want to do delivery radius?
 
 “Which

    restaurant delivers to me?” INVERTING OUR LOGIC—“WITHIN RANGE”
  11. 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
  12. 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
  13. 41.

    • Introduced in MySQL 5.5; available in PostgreSQL since ~2001

    with PostGIS extension. GEOMETRY COLUMN TYPE
  14. 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
  15. 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
  16. 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
  17. 48.

    • Data stored as binary; convert WKT text to binary

    using ST_* methods TEXT-TO-GEOMETRY CONVERSION • ST_GeomFromText • ST_GeomFromGeoJSON
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 61.

    • 300,000 records = lots of 500 errors • Load

    subset instead… but which subset? Listen to map boundary changes. LISTEN ON MOVE/ZOOM
  25. 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
  26. 64.
  27. 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