MySQL POINT字段的PHP数据类型

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).

我创建了一个MySQL表,其中一个字段的类型为POINT,用于存储lat,lon坐标(例如: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.

我的理解是POINT应该有示例的格式(我也尝试过lat,lon)。我认为问题是空间将变量转换为STRING。

Am I missing something, here?

我错过了什么吗,在这里?

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

这是我的PHP代码连接到数据库并插入记录。

// 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=AIzaSyDAQPnstTsQBb6_Ok8WLXWNWoxmaQZxLG4');
$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.

或者,我可以只有单独的lat和lon字段,但我想练习msyql的内置几何功能。

Edit...

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

按照这个答案的建议,我用POINT()包围了$ latlon。但是,它没有改变结果。

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

1 个解决方案

#1

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:

然后使用PointFromText包装你的:latlon:

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

#1