PHP Data Type For MySQL POINT Field

Question

I created a MySQL table where a field is of type POINT and is used to store lat, lon coordinates (ex: 36.6294654 -93.1725982).

I got an error, with a form submission, and I'm assuming it is due to a mismatched data type.

SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field

My understanding is that POINT should have the example's format (I also tried lat, lon). The problem, I think, is that the space converts the variable into STRING.

Am I missing something, here?

Here is my PHP code to connect to the database and insert the record.

// use google's geocoding service to transform an address into lat/lon coordinates (https://developers.google.com/maps/documentation/geocoding/)
$json = file_get_contents('https://maps.googleapis.com/maps/api/geocode/json?address=' . urlencode($street) . ',+' . urlencode($city) . ',+' . urlencode($state) . ',+' . urlencode($zip) . '&key=YOUR_API_KEY');
$json = json_decode($json, true);
$latlon = $json['results'][0]['geometry']['location']['lat'] . ' ' . $json['results'][0]['geometry']['location']['lng'];
try {
    $dbh = new PDO('mysql:host=someserver;port=someport;dbname=somedatabase', 'someusername', 'somepassword', array(PDO::ATTR_PERSISTENT => true));
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = 'INSERT INTO Venues (Name, Street, City, State, Zip, Country, TimeZone, LatLon, Phone, Email, Website, Host, Notes) VALUES (:name, :street, :city, :state, :zip, :country, :timezone, :latlon, :phone, :email, :website, :host, :notes)';
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(":name" => $name, ":street" => $street, ":city" => $city, ":state" => $state, ":zip" => $zip, ":country" => $country, ":timezone" => $timezone, ":latlon" => $latlon, ":phone" => $phone, ":email" => $email, ":website" => $website, ":host" => $host, ":notes" => $notes));
    if ($stmt->rowCount() == 1) {
        echo '<p>"' . $name . '" creation succeeded.</p>';
    } else {
        echo '<p>"' . $name . '" creation failed.</p>';
    }
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $dbh = null;
}
catch(PDOException $e) {
    echo $e->getMessage();
}

Alternatively, I could just have separate lat and lon fields, but I was wanting to practice with msyql's built-in geometry functionality.

Edit...

Following the advise of this answer, I surrounded $latlon with POINT(). It didn't change the results, though.

Edit 2...

Here is the table structure, just in case something doesn't look correct.

VenueKey    int(11)
Name        varchar(255)
Street      varchar(255)
City        varchar(255)
State       varchar(2)
Zip         varchar(10)
Country     varchar(2)
TimeZone    varchar(20)
LatLon      point
Phone       varchar(20)
Email       varchar(255)
Website     varchar(255)
Host        varchar(255)
Notes       text

@Ronnie I tried POINT(lat, lon), POINT(lat lon), and POINT(lat,lon). They all resulted in the error. — doubleJ
– doubleJ, Commented Jan 14, 2015 at 22:41
maybe give this answer a shot: stackoverflow.com/questions/17893691/… sounds like you need to be using PointFromText — Ronnie
– Ronnie, Commented Jan 14, 2015 at 23:04
@Ronnie That link had the answer (same as Prisoner's answer), but it wasn't clearly defined that PointFromText() HAD to be in the query. I assumed that its inclusion in the variable was the same thing. It wasn't. — doubleJ
– doubleJ, Commented Jan 15, 2015 at 16:05

Prisoner · Accepted Answer · 2015-01-15 15:44:11Z

3

This should fix your issue:

$latlng = 'POINT(' . $json['results'][0]['geometry']['location']['lat']. " " . $json['results'][0]['geometry']['location']['lng']. ')';

and then wrap your :latlon with PointFromText:

$sql = 'INSERT INTO Venues (Name, Street, City, State, Zip, Country, TimeZone, LatLon, Phone, Email, Website, Host, Notes) VALUES (:name, :street, :city, :state, :zip, :country, :timezone, PointFromText(:latlon), :phone, :email, :website, :host, :notes)';

answered Jan 15, 2015 at 15:44

Prisoner

27.7k11 gold badges78 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

doubleJ Over a year ago

That worked!!! What, exactly, is the difference between putting PointFromText() in the variable versus the query?

Ronnie Over a year ago

Did you try it with PointFromText() in the var? I don't see why it wouldn't work. On a side note, Prisoner looks like that guy from vsauce on youtube

doubleJ Over a year ago

@Ronnie Yes, I did try PointFromText() in the variable. The link that you gave, above, referenced that parameterizing queries is not (simple) string replacement. It's possible that it being in the query is interpreted, differently, by MySQL.

Collectives™ on Stack Overflow

PHP Data Type For MySQL POINT Field

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related