## Problem You have a `locations` table storing points of interest with latitude and longitude. Write queries to: (1) find all locations within 10 km of a given point, (2) return the 5 nearest locations to a given point, and (3) for each user in a `users` table, find the nearest location of each category. ```sql -- Schema locations(location_id, name, category, lat FLOAT, lon FLOAT) users(user_id, home_lat FLOAT, home_lon FLOAT) ``` **Distance approximation (flat-earth for small areas):** ```sql -- distance_km ~= sqrt(power((lat2-lat1)*111, 2) + power((lon2-lon1)*111*cos(radians(lat1)), 2)) ``` **Example:** ```sql -- Q1: Locations within 10km of (43.65, -79.38) SELECT name, category, sqrt(power((lat-43.65)*111,2) + power((lon+79.38)*111*cos(radians(43.65)),2)) AS dist_km FROM locations HAVING dist_km < 10 ORDER BY dist_km; ``` ## Follow-ups 1. What is wrong with using a distance function in WHERE vs. HAVING? Does it affect index usage? 2. How do PostGIS extensions change this query — what index type does it use? 3. For Q3 (nearest per user per category), write the SQL using LATERAL JOIN or ROW_NUMBER(). 4. At 50M locations globally, how do you avoid a full table scan for every proximity query?

InterviewDB Question

Location Query: SQL Queries to Find Nearby Locations and Compute Distance-Based Rankings

phone

Question Details

Problem You have a locations table storing points of interest with latitude and longitude. Write queries to: (1) find all locations within 10 km of a given point, (2) return the 5 nearest locations to a given point, and (3) for each user in a users table, find the nearest location of each category. Distance approximation (flat-earth for small areas): Example: Follow-ups What is wrong with using a distance function in WHERE vs. HAVING? Does it affect index usage? How do PostGIS extensions change…

Full Details

🔒

Unlock all Axon questions

Full insider details, leaked discussions, and candidate experiences.

Get full access — $100 a year, unlimited access

About This Question

This is a reported interview question from a axon interview during the phone round.

It covers the following topics: Onsite, Sql, Phone, Coding .

Topics

Onsite Sql Phone Coding

Location Query: SQL Queries to Find Nearby Locations and Compute Distance-Based Rankings

Question Details

Full Details

About This Question

Topics

More Axon Interview Questions