Posted in IT

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.

Advertisements

Author:

Siapa itu hilwa? Oh, itu pertanyaan yang sangat bagus! Hilwa itu adalah nickname dari seseorang yang bernama Wirda Hilwa, Tapi Wirda Hilwa itu masih punya nickname yang lain, yaitu wirda, dan iwa. Dari beberapa nickname itu Wirda Hilwa paling suka dipanggil Hilwa, karena arti dari hilwa itu adalah... hmm..., ga jadi deh, rahasia :P . [Sekarang serius mode:on ] Singkatnya aja, hilwa itu adalah seseorang yang hobi baca blog2 orang utk diambil manfaat & pelajaran dari blog tsb. Lalu terinspirasi pula utk membuat blog sendiri, utk berbagi yang bisa dibagi kpd orang lain. Semoga blog ini dapat bermanfaat buat yang baca. Amiin.

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