Matt Stauffer
March 06, 2019
1.5k

# Databases in Space

Simple tricks for using geometry fields in MySQL and Laravel

March 06, 2019

## Transcript

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

relates to the Earth’s surface” geospatialadjective
7. ### • 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

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

line) equilateral ! works for me WHICH IS LATITUDE, WHICH IS LONGITUDE?

Meridian
11. ### 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
12. ### • 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

22. ### • 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

28. ### What if we want to do delivery radius?    “Which

restaurant delivers to me?” INVERTING OUR LOGIC—“WITHIN RANGE”
29. ### • Basic math and stuﬀ in that endpoint • Restaurant

longitude +- (delivery_distance / 2) • Restaurant latitude +- (delivery_distance / 2) • SELECT query example for that boundary • BUT WAIT!…. DRAWING SQUARES

33. ### • Spatial Reference Identiﬁer—for geometries or geographies • MySQL supports

over 5000 SR’s • Most common: • SRID 0 (MySQL default) just means “ﬂat” (ﬁne for geometry, not for geography) • SRID 4326 (WGS84) is most common; what GPSes uses • SRID 3857 is what Google Maps and Bing use SRID

35. ### • So far… squares & circles. • Story time! BASIC

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

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

POLYGONS: STORY TIME
38. ### • Introduced in MySQL 5.5; available in PostgreSQL since ~2001

with PostGIS extension. GEOMETRY COLUMN TYPE
39. ### • 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

41. ### Geometry methods that can be used for select, order, and

where statements (and other things—to cover later) • ST_CONTAINS: second value is within ﬁrst value • ST_WITHIN: ﬁrst value is within second value • ST_INTERSECTS: two shapes intersect • ST_TOUCHES: two shapes have touching boundaries ST_* METHODS

43. ### • 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

45. ### • Data stored as binary; convert WKT text to binary

using ST_* methods TEXT-TO-GEOMETRY CONVERSION • ST_GeomFromText • ST_GeomFromGeoJSON

47. ### • Not a “ﬁle”; more like a directory of ~10

ﬁles • .shp, .shx, and .dbf ﬁles have to be in there, but there are usually more • Designed by ESRI—sort of like the spatial equivalent of .xlsx SHAPEFILES
48. ### • 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
49. ### • Simple stuﬀ. 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
50. ### • GDAL (Geospatial Data Abstraction Library - https:// www.gdal.org/) •

Ogr2ogr (https://www.gdal.org/ogr2ogr.html) • brew install gdal • Can convert between all geospatial ﬁle formats, and even query properties of one with a SQL-like syntax CONVERTING FILE FORMATS

52. ### • 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

54. ### • 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. ### • 300,000 records = lots of 500 errors • Load

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

62. ### • 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

65. ### • 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