PHP PDO Insert Longditude / latituded in Mysql and check if exists before insert

Question

I am trying to insert geolocations correctly into my mysql db so that I may run a query to get all items within a set radius.

my db is set up like so: --id (integer pk) --latlon (point spatial index)

and my function which checks if the geolocation exists and if not inserts looks like this:

function addGeoLocation($long,$lat,$deal_id,$db)
{
    if($long != '' && $long > 0){

        $latlon = 'POINT('.$lat .' ' .$long.')';

        $locs = $db->row("SELECT id FROM geolocations WHERE latlon = :latlon",array("latlon"=>$latlon));
        if(!$locs)
        {
             $sql = $db->query("INSERT INTO geolocations set latlon = PointFromText(:latlon)",array("latlon"=>$latlon));
            $location_id = $db->lastInsertId();
        }
        else
        {
            $location_id = $locs['id']; 
        }

        $locs_pivot = $db->row("SELECT id FROM dailydeals_geolocations WHERE dailydeal_id = :deal_id AND geolocation_id = :location_id",array("deal_id"=>$deal_id,"location_id"=>$location_id));

        if(!$locs_pivot)
        {
            $sql = $db->query("INSERT INTO dailydeals_geolocations (geolocation_id,dailydeal_id) VALUES(:location_id,:deal_id)",array("location_id"=>$location_id,"deal_id"=>$deal_id));

        }
    }
}

No matter what I try I just cannot get it to insert the geo I just keep getting this error:

Unhandled Exception. <br />SQLSTATE[22003]: Numeric value out of range: 
1416 Cannot get geometry object from data you send to the GEOMETRY field

Any help would be greatly received

Many thanks in advance :)

Mohammad Awwaad · Answer 1 · 2017-12-27T05:09:45.897

0

This error of MySQL means that you are inserting a number value that is larger than the maximum allowed value for that column type.

I think you need to check again what you are doing at "PointFromText" or you can change your column type to be larger enough to hold the given value.

edited Dec 27 '17 at 05:09

answered Dec 30 '14 at 14:41

Mohammad Awwaad

431
3
12

hi thanks for your reply I have changed PointFromText() to GeomFromText() that should format the value to fit the field but it still throws the same error so I don't think that's the case. – Adrian Brown Dec 30 '14 at 15:22
Could you tell me what is the type of latlon column ? – Mohammad Awwaad Jan 01 '15 at 12:23
Also take a look on the following issue: http://stackoverflow.com/questions/16647641/saving-spatial-data-in-cakephp – Mohammad Awwaad Jan 01 '15 at 12:31

score 0 · Answer 2 · answered Dec 30 '14 at 15:48

0

my code actually works this issue is I have a latlon field also in the locations table which I manually populate with the lat lon values because this field is null on import it throws the error :o)

answered Dec 30 '14 at 15:48

Adrian Brown

79
1
8

PHP PDO Insert Longditude / latituded in Mysql and check if exists before insert

2 Answers2