0

[table name:franchise

I have two inputs in php latitude and longitude with this value i need to fetch nearest frachise from franchise table.

please see image for table info.

Ex: 25.271631753161955-Latitude

55.31605750322342-Longitude

I want to select nearest franchise using given latitude and longitude.]1

Shakif
  • 1
  • 4
  • You want to take in a lat/long input, and query the table to return the row with the lat/long that is "closest" to the input? Because this is a case where you are going to need more than SQL. You're going to have to pull some data from the table, then perform calculations to see which one is closest, and return that. – Bango Mar 19 '17 at 05:31
  • You can craft your SQL so that it only returns relevant results, but like I said, you will need to programmatically choose which result(s) are best fit to be the answer with some simple algebra (distance fmla) – Bango Mar 19 '17 at 05:33
  • em bit new to this ......... i can pull the data using php.i can take all each values using foreach loop – Shakif Mar 19 '17 at 05:35
  • 1
    Look into using the Haversine formula, which should be fine if this is just an assignment. If you are doing this for an actual app, then geospatial partitioning might come into play. – Tim Biegeleisen Mar 19 '17 at 05:37
  • For now, best would be pull all values, then foreach through them like you said. each loop run, calculate distance between input and that respective place, and if its less than the one before, replace that value with the one before it.. – Bango Mar 19 '17 at 05:37
  • @Bango I might disagree. If he has 100K records, your approach is to fetch every record, when many could be eliminated on the database. It is wasteful to do the distance filter in his PHP code when it could be done on the database. – Tim Biegeleisen Mar 19 '17 at 06:22
  • hi friend any other clue...... i'm very much needed – Shakif Mar 19 '17 at 06:44
  • SELECT * , (3956 * 2 * ASIN(SQRT( POWER(SIN(( 25.271631753161955 - latitude) * pi()/180 / 2), 2) +COS( 25.271631753161955 * pi()/180) * COS(latitude * pi()/180) * POWER(SIN(( 55.31605750322342 - longitude) * pi()/180 / 2), 2) ))) as distance from franchise having distance <= 10 order by distance – Shakif Mar 19 '17 at 08:22
  • it worked for me to get nearest location – Shakif Mar 19 '17 at 08:23

0 Answers0