尝试根据其他列将几何对象添加到MySQL列会导致“不影响任何行"? [英] Attempt to add geometry object to MySQL column based off other columns results in 'no rows affected'?

问题描述

这实际上是对先前问题的后续操作问题&自写答案可以潜在地解决我的问题，但是我在理解该页面上显示的其中一条SQL语句时遇到了一些麻烦.

This is actually a follow up to a prior question I asked here about how to improve SELECT query performance on a simple read-only database. Long story short, I have then found this question & self-written-answer which can potentially solve my problem, but I'm having some trouble understanding one of the SQL statements presented on that page.

您可以在我的原始问题中找到更多详细信息，但总的来说，我的表如下所示:

You can find additional details in my original question, but in summary, my table, which looks like this:

`squares` (
   `square_id` int(7) unsigned NOT NULL,
   `ref_coord_lat` double(8,6) NOT NULL,
   `ref_coord_long` double(9,6) NOT NULL,
   PRIMARY KEY (`square_id`),
   KEY `ref_coord_lat` (`ref_coord_lat`),
   KEY `ref_coord_long` (`ref_coord_long`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

对于每个选择查询，我喜欢的时间太慢，无法达到1.40-1.45秒.我发现的Q + A建议将数据库从InnoDB切换到MyISAM以支持MySQL的空间扩展.现在，我完成了此操作，并创建了一个NOT NULL，GEOMETRY POINT列，我打算分别在这些列中分别填充ref_coord_lat和ref_coord_long的纬度和经度坐标.唯一的问题是我无法实现此目的的查询.而且我不知道为什么.

was performing too slowly for my liking at 1.40-1.45 seconds per select query. The Q+A which I discovered recommended switching database from InnoDB to MyISAM to support MySQL's spatial extensions. I have now done this, and created a NOT NULL, GEOMETRY POINT column which I intend to populate with the latitude and longitude coordinates from ref_coord_lat and ref_coord_long respectively. The only thing is my query to achieve this does not work at all. And I have no idea why.

这是答案用来填充几何列的查询:

Here's the query that the answer used to populate a geometry column:

GeomFromText(CONCAT('LINESTRING(', start_ip, ' -1, ', end_ip, ' 1)'))

这是我为我的目的而进行的改编的尝试:

And here's my attempt at an adaptation for my purposes:

UPDATE `squares` SET `coordinate` = PointFromText(CONCAT('POINT(', ref_coord_lat, ref_coord_long, ')'))

除非这行不通.

phpMyAdmin并没有提醒我任何SQL语法错误，实际上，我得到的只是一个绿色的完成"框，通知我"0行受到影响(查询花费了0.00007秒)".

I'm not alerted to any SQL syntax errors by phpMyAdmin, in fact, all I get is a green 'completed' box which informs me that '0 rows were affected (Query took 0.00007 sec)'.

有什么主意为什么我的查询无法正常运行?

Any ideas why my query is not functioning correctly?

推荐答案

我可以看到的一件事是您的坐标之间没有空格.试试这个:

One thing I can see is that your coordinates have no spaces between them. Try this:

UPDATE `squares` SET `coordinate` = PointFromText(CONCAT('POINT(', ref_coord_lat, ' ', ref_coord_long, ')'));

这篇关于尝试根据其他列将几何对象添加到MySQL列会导致“不影响任何行"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！