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
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)';