Tweet
Tweet

Slide 1

Slide 1 text

1 DATABASES
 IN SPACE

Slide 2

Slide 2 text

No content

Slide 3

Slide 3 text

1 DATABASES
 IN SPACE

Slide 4

Slide 4 text

STORY TIME

Slide 5

Slide 5 text

No content

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

@CENITORIA @DARKBOYWONDER @LOGAN_J_HENSON

Slide 8

Slide 8 text

GEOSPATIAL BASICS

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

• 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

Slide 11

Slide 11 text

THE DATE LINE:

Slide 12

Slide 12 text

Matt’s trick to remembering: 
 (the equator is a latidude line) equilateral ! works for me WHICH IS LATITUDE, WHICH IS LONGITUDE?

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

• 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

Slide 16

Slide 16 text

LET’S BUILD AN APP!

Slide 17

Slide 17 text

OUR APP: A RESTAURANT WEB SITE

Slide 18

Slide 18 text

SIMPLE POINTS

Slide 19

Slide 19 text

BASIC DATABASE STORAGE

Slide 20

Slide 20 text

THROW IT ON A MAP (VANILLA JS)

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

THROW IT ON A MAP

Slide 23

Slide 23 text

BROWSER GEOLOCATION

Slide 24

Slide 24 text

DISTANCE CALCULATIONS

Slide 25

Slide 25 text

• 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

Slide 26

Slide 26 text

BASIC DISTANCE: GEOLOCATION

Slide 27

Slide 27 text

BASIC DISTANCE: API CALL

Slide 28

Slide 28 text

BASIC DISTANCE: SOME KINDA MATH?

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

BASIC DISTANCE: LOGAN’S MAGIC MATH

Slide 31

Slide 31 text

What if we want to do delivery radius?
 
 “Which restaurant delivers to me?” INVERTING OUR LOGIC—“WITHIN RANGE”

Slide 32

Slide 32 text

• 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

Slide 33

Slide 33 text

LOGAN! MAGIC RADIUS!

Slide 34

Slide 34 text

MYSQL VS. POSTGRESQL

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

• 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

Slide 37

Slide 37 text

POLYGONAL AREAS

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

• 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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

COMMON "WHERE ST_*” PATTERNS

Slide 46

Slide 46 text

• 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

Slide 47

Slide 47 text

IMPORTING DATA

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

TEXT-TO-GEOMETRY CONVERSION (CONT)

Slide 50

Slide 50 text

• 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

Slide 51

Slide 51 text

• 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

Slide 52

Slide 52 text

• 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

Slide 53

Slide 53 text

• 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

Slide 54

Slide 54 text

RETRIEVING DATA

Slide 55

Slide 55 text

• 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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

• 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

Slide 58

Slide 58 text

ST* USING QUERY BUILDER RAW

Slide 59

Slide 59 text

EXPORT TO GEOJSON

Slide 60

Slide 60 text

GOOGLE MAPS
 (OR OTHER MAPS) INTERMEDIATE TRICKS

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

• 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

Slide 63

Slide 63 text

EMBED GEOJSON ON YOUR MAPS!

Slide 64

Slide 64 text

No content

Slide 65

Slide 65 text

Look how far we’ve come.

Slide 66

Slide 66 text

• 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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

• 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

Slide 70

Slide 70 text

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