Guest User

Untitled

a guest
Jan 2nd, 2021
40
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT d.name, d.houseNumber, d.postalCode, d.city, d.street, d.latitude, d.longitude, d.distance, d.BusinessID,  d.BusinessType, b.Description
  2.         FROM (
  3.         SELECT z.name,
  4.             z.houseNumber,
  5.             z.postalCode,
  6.             z.city,
  7.             z.street,
  8.             z.BusinessID,
  9.             z.BusinessType,
  10.         z.latitude, z.longitude,
  11.         p.radius,
  12.         p.distance_unit
  13.                  * DEGREES(ACOS(COS(RADIANS(p.latpoint))
  14.                  * COS(RADIANS(z.latitude))
  15.                  * COS(RADIANS(p.longpoint - z.longitude))
  16.                  + SIN(RADIANS(p.latpoint))
  17.                  * SIN(RADIANS(z.latitude)))) AS distance
  18.         FROM Businesses AS z
  19.         JOIN (  
  20.         SELECT  56.11164290  AS latpoint, -3.15779220 AS longpoint,
  21.                 30.0 AS radius, 111.045 AS distance_unit
  22.         ) AS p ON 1=1
  23.         INNER JOIN BusinessTypes AS b
  24.         ON z.BusinessType = b.BusinessType
  25.         WHERE z.latitude
  26.         BETWEEN p.latpoint  - (p.radius / p.distance_unit)
  27.         AND p.latpoint  + (p.radius / p.distance_unit)
  28.         AND z.longitude
  29.         BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
  30.         AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
  31.         ) AS d
  32.         WHERE distance <= radius AND b.Description = 'Coffee'
  33.         ORDER BY distance
RAW Paste Data