Created
May 27, 2023 16:47
-
-
Save gongfan99/9b7e9fa26305893abdc3d6f24c26c58a to your computer and use it in GitHub Desktop.
Find all hotels within 500 meters from my location with Node.js
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import { createPool } from 'mysql2/promise' | |
| import 'dotenv/config' | |
| const pool = createPool({ | |
| host: process.env.DB_HOST, | |
| user: process.env.DB_USER, | |
| password: process.env.DB_PASSWORD, | |
| database: process.env.DB_DATABASE, | |
| waitForConnections: true, | |
| connectionLimit: 5, | |
| idleTimeout: 10000, // idle connections timeout, in milliseconds, the default value 60000 | |
| queueLimit: 10 // 0 means no limit for queue length | |
| }); | |
| /*********************************************************** | |
| * To find all hotels within 500 meters from myLocation | |
| * It uses a rectangular boundary to first limit the possible hotels | |
| * All distances are in meters; all angles are in degrees | |
| * The hotels table is created as below | |
| CREATE TABLE hotels | |
| ( location POINT NOT NULL, | |
| SPATIAL INDEX(location) | |
| ) ENGINE=INNODB; | |
| ************************************************************/ | |
| async function main() { | |
| try { | |
| const myLocation = {lon: -80, lat: 40}; | |
| const d = 500; | |
| const earthRadius = 6371000; | |
| const deltaLat = (180 / Math.PI) * d / earthRadius; | |
| const deltaLon = 2 * Math.asin(Math.sin(0.5 * d / earthRadius) / Math.cos(myLocation.lat * Math.PI / 180)) * (180 / Math.PI); | |
| const westBorder = myLocation.lon - deltaLon; | |
| const eastBorder = myLocation.lon + deltaLon; | |
| const northBorder = myLocation.lat + deltaLat; | |
| const southBorder = myLocation.lat - deltaLat; | |
| const query = ` | |
| SELECT location, ST_Distance_Sphere(location, Point(${myLocation.lon}, ${myLocation.lat})) distance FROM hotels | |
| WHERE ST_Within(location, Polygon(LineString( | |
| Point(${westBorder}, ${northBorder}), | |
| Point(${westBorder}, ${southBorder}), | |
| Point(${eastBorder}, ${southBorder}), | |
| Point(${eastBorder}, ${northBorder}), | |
| Point(${westBorder}, ${northBorder}) | |
| ))) | |
| HAVING distance < ${d} | |
| ORDER BY distance; | |
| `; | |
| let [rows] = await pool.query(query); | |
| console.log(rows); // example output: [ { location: { x: -80.0001, y: 40.0001 }, distance: 14.007102060596733 } ] | |
| } catch(e) { | |
| console.log(e) | |
| } finally { | |
| await pool.end(); | |
| } | |
| } | |
| main() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment