2

Ok so I have the following two columns in my database table with the following long/lat.

longitude - "-2.2426305000000184,0.7077123000000256"
latitude - "53.4807593,51.5459269"

I have this query so far:

function getResults($lat, $long) {

$query=$this->db->query("SELECT *, (3959 * acos(cos(radians('".$lat."')) * cos(radians(latitude)) * cos( radians(longitude) - radians('".$long."')) + sin(radians('".$lat."')) * 
sin(radians(latitude)))) 
AS distance 
FROM ads HAVING distance < 15 ORDER BY distance LIMIT 0 , 10");
       return $query->result();
  }

The problem is that my longitude and latitude columns are arrays.

The user enters the longitude and latitude in a search bar and it should return results. I'm finding it difficult to query this since the longitude and latitude columns are arrays.

xadjnxadjcx
  • 43
  • 4
  • use where in clause of mysql. reffer this link - https://stackoverflow.com/questions/9800997/mysql-where-in-query-order-by-match – Suniti Yadav Sep 30 '17 at 17:52
  • @sunitiyadav Thank you. I have seen many ways how this can be achieved however embedded it into my query is quite tricky due to its format. – xadjnxadjcx Sep 30 '17 at 18:01
  • You can split by comma using https://stackoverflow.com/questions/1096679/can-mysql-split-a-column – Mazzy Sep 30 '17 at 18:13
  • Possible duplicate of [Select all geospatial points inside a bounding box](https://stackoverflow.com/questions/21208697/select-all-geospatial-points-inside-a-bounding-box) – hd1 Sep 30 '17 at 18:15
  • I'm confused. are they stored as ARRAY (didn't know mysql did that) or your php input is array, or you are storing an array as a serialized text? – Jacques Amar Sep 30 '17 at 19:30
  • MySQL columns that look like this `"-2.2426305000000184,0.7077123000000256"` are NOT "arrays" they are "comma separated strings" (which is a poor way to store data in any RDBMS) so you need to break-up those strings into individual values before they be of use to you. So look for "split by comma" to help you solve this dilemma. Personally I would recommend re-thinking how you store your lat/long data so splitting can be avoided. – Paul Maxwell Sep 30 '17 at 23:57
  • @JacquesAmar Hi, they are stored as comma separated strings. So the user enters many long/lat which are stored in the db as individuals using commas to separate them. – xadjnxadjcx Oct 01 '17 at 11:43
  • @Used_By_Already Thank you for the clarification. It's the first time I'm using this. I will see how it goes and do further research. – xadjnxadjcx Oct 01 '17 at 11:44
  • 1
    Please **please** read about database normalization. You will have *no end of programming and performance problems* if you store your latitude and longitude values as text strings, never mind comma-separated text strings.When you're in a hole, stop digging. Store them as `FLOAT` columns. Once you have normalized your data, this article may help you. https://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/ – O. Jones Oct 01 '17 at 14:02

0 Answers0