Member Avatar for praba_web

Hi there,
How to get near by locations (around 5km) using latitude and longitude?

even i got sql query :
my table structure:
craete table ad_list(ad_list_id int(11) NOT NULL, lat decimal(10,6) NOT NULL, lng decimal(10,6) NOT NULL, PRIMARY KEY(ad_list_id));

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( $lat ) )
  * cos( radians( $lng ) - radians(-122) ) + sin( radians(37) ) 
  * sin( radians( $lat ) ) ) ) AS distance
FROM markers HAVING distance < 5 ORDER BY distance LIMIT 0 , 10;

Please give me a solution for this....

Edited by praba_web because: mistook
  • 3 Contributors
  • 2 Replies
  • 460 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

Short, practical pattern that works well on real datasets: first use a small bounding box (fast, index-friendly) to eliminate most rows, then apply an exact geodesic check only to the reduced set. That follows 's suggestion to think mathematically, while giving you the option mentioned to swap in DB spatial functions if your server supports them.

Compute the box in degrees from the center point (uses Earth's radius R = 6371 km). This gives tight min/max lat/lng to feed into an indexed WHERE clause:

# radius_km = 5
R = 6371.0
deltaLat = (radius_km / R) * (180 / PI)
deltaLng = deltaLat / cos(centerLat * PI/180)

minLat = centerLat - deltaLat
maxLat = centerLat + deltaLat
minLng = centerLng - deltaLng
maxLng = centerLng + deltaLng

Use that box in the query to leverage indexes, then run a precise distance test (Haversine or a geodesic library) on the remaining rows, or use your DB's spatial distance function if available. Example WHERE-clause pattern:

WHERE lat BETWEEN :minLat AND :maxLat
  AND lng BETWEEN :minLng AND :maxLng

Notes and tips: make sure lat/lng columns are indexed (composite index on (lat,lng) or separate indexes helps); avoid running trig across the whole table; be careful around the +/-180° meridian and the poles; choose units consistently (R=6371 km or ≈3959 miles). For high query volumes, convert to a POINT/geometry column with a spatial index and use the DB's optimized spatial predicates. If sub-meter accuracy or ellipsoidal correctness matters, prefer a geodesic library over simple spherical formulas.

Recommended Answers

All 2 Replies

Member Avatar for rubberman

Do you have to use SQL? First, solve the problem mathematically using an arbitrary reference point (latitude and longitude). Compute what the L&L (latitude and longitude) would be for a distance of 5km from the reference point in any direction. Then, you will understand how to derive that from database entries. Note that you may want to compute this in an abstract manner, rather than just 5km east, west, north, or south of the reference location. That seems to be what you are trying to do, but SQL is NOT a good language for such abstractions unless you fully understand the permutations involved first.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.