MySql如何将varchar（纬度，经度）转换为十进制字段？

In mysql I have a varchar containing latitude and longitude provided by Google maps. I need to be able to query based on a bounding box value, but have no need for the geo features now available. I'm trying to populate 2 new Decimal fields with the Decimal values found in the varchar. Here is the query that I'm trying to use, but the result are all rounded values in the new fields.

在mysql中,我有一个包含谷歌地图提供的纬度和经度的varchar。我需要能够基于边界框值进行查询,但不需要现在可用的地理特征。我正在尝试使用varchar中找到的Decimal值填充2个新的Decimal字段。这是我正在尝试使用的查询,但结果是新字段中的所有舍入值。

Sample data:

'45.390746926938185, -122.75535710155964',
'45.416444621636415, -122.63058006763458'

Create Table:

CREATE TABLE IF NOT EXISTS `cameras` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(75) NOT NULL,
  `position` varchar(75) NOT NULL,
  `latitude` decimal(17,15) default NULL,
  `longitude` decimal(18,15) default NULL,
  `address` varchar(75) NOT NULL,<br />
  `date` varchar(11) NOT NULL,<br />
  `status` int(1) NOT NULL default '1',
  `duplicate_report` int(11) NOT NULL default '0',
  `missing_report` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1050 ;

SQL:

UPDATE cameras
SET latitude = CAST( (substring(position,1,locate(',',position))) AS DECIMAL(17,15) ),
    longitude = CAST( (substring(position,locate(',',position)+1)) AS DECIMAL(18,15) )

SQL Alternate attempt:

SQL替代尝试:

UPDATE cameras
SET latitude = CONVERT( (substring(position,1,locate(',',position))), DECIMAL(17,15) ),
    longitude = CONVERT( (substring(position,locate(', ',position)+1)), DECIMAL(18,15) )

The resulting field values are for both scenarios:

生成的字段值适用于以下两种情况:

45.000000000000000 and -122.000000000000000
AND
45.000000000000000 and -122.000000000000000

Can anyone see what I'm doing wrong? Thanks.

谁能看到我做错了什么?谢谢。

1 个解决方案

#1

Both the CAST and CONVERT forms seem to be correct.

CAST和CONVERT形式似乎都是正确的。

SELECT CAST((SUBSTRING(t.position,1,LOCATE(',',t.position))) AS DECIMAL(17,15)) AS lat_
     , CONVERT(SUBSTRING(t.position,LOCATE(', ',t.position)+1),DECIMAL(18,15)) AS long_
  FROM (SELECT '45.390746926938185, -122.75535710155964' AS `position`) t

              lat_                   long_
------------------  ----------------------
45.390746926938185    -122.755357101559640

I think position is a reserved word, but I don't think that matters in this case. But it wouldn't hurt to assign a table alias and qualify all column references

我认为立场是一个保留的词,但我不认为在这种情况下这很重要。但是分配表别名并限定所有列引用并不会有什么坏处

UPDATE cameras c
   SET c.latitude = CAST((SUBSTRING(c.position,1,LOCATE(',',c.position))) AS DECIMAL(17,15))
     , c.longitude = CAST((SUBSTRING(c.position,LOCATE(',',c.position)+1)) AS DECIMAL(18,15))

But I suspect that won't resolve the problem.

但我怀疑这不会解决问题。

One thing to check is for a before or after update trigger defined on the table, which is rounding/modifying the values assigned to the latitude and longitude columns?

要检查的一件事是在表上定义的更新触发器之前或之后,是否舍入/修改分配给纬度和经度列的值?

I suggest you try running just a query.

我建议你尝试只运行一个查询。

SELECT CAST((SUBSTRING(c.position,1,LOCATE(',',c.position))) AS DECIMAL(17,15)) AS lat_
     , CAST((SUBSTRING(c.position,LOCATE(',',c.position)+1)) AS DECIMAL(18,15)) AS lon_
  FROM cameras c

and verify that produces the decimal values you expect.

并验证是否产生您期望的十进制值。

A dot character should be recognized as a decimal point. Does the position column contain some other special characters, like a space or something?

点字符应该被识别为小数点。 position列是否包含一些其他特殊字符,如空格或其他内容?

From what you posted, it looks like the CAST and CONVERT working on the integer portion up to the decimal point. (There shouldn't be an implicit convert to signed integer in there, so it's not clear why the characters following the decimal point aren't being included.)

从您发布的内容来看,CAST和CONVERT看起来像整数部分一样工作到小数点。 (那里不应该隐式转换为有符号整数,因此不清楚为什么不包括小数点后面的字符。)

If you can figure out what character(s) are being used to represent the decimal point, then you could use a MySQL REPLACE() function to replace those with a simple dot character.

如果你可以找出用于表示小数点的字符,那么你可以使用MySQL REPLACE()函数来替换那些带有简单点字符的字符。

#1