Calculates the distance based on the latitude longitude in mysql and postgre

Here’s the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371. This is based on HAVERSINE FORMULA

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 < 25 ORDER BY distance LIMIT 0 , 20;

POSTGRE PLSQL QUERY

I made a sample using postgre. for searching location in radius 2 km:

SELECT * FROM (SELECT no, ( 6371 * ACOS( COS( RADIANS(-6.2340772) ) * COS( RADIANS( latitude ) ) * COS( RADIANS( longitude ) – RADIANS(106.7211914) ) + SIN( RADIANS(-6.2340772) ) * SIN( RADIANS( lattitude ) ) ) ) AS distance

FROM t_mtr_tower_provider )tp WHERE tp.distance < 2

ps:

1.fyuuh….akhirnya bisa nyempatin posting di blog ini lagi😛 .

2. 3 jam ngubek2 google baru ketemu query utk postgre ini, ternyata best keyword nya HAVERSINE FORMULA ^_^ . Semoga bisa membantu untuk yang lagi nyari2 query untuk menghitung jarak berdasarkan latitude dan longitude di postgre.

One thought on “Calculates the distance based on the latitude longitude in mysql and postgre

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s