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

问题描述

在mysql中，我有一个varchar，它包含Google地图提供的经度和纬度。
我需要能够根据边界框值进行查询，但不需要现在可用的地理特征。我试图用varchar中的Decimal值填充2个新的Decimal字段。这里是我试图使用的查询，但结果是新字段中的所有取整值。

示例数据：

  '45 .390746926938185，-122.75535710155964'，
 '45 .416444621636415，-122.63058006763458'
  

创建表：

  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）默认NULL，
`longitude` decimal（18,15）默认NULL，
`address` varchar（75）NOT NULL，< br /> 
`date` varchar（11）NOT NULL，< br /> 
` status` int（1）NOT NULL默认值'1'，
`duplicate_report` int（11）NOT NULL默认值'0'，
`missing_report` int（11）NOT NULL默认'0'，
 PRIMARY KEY（`id`），
 KEY`user_id`（`user_id`），
```` status `（`status`）
）ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO_INCREM ENT = 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备用尝试：

  UPDATE cameras 
 SET latitude = CONVERT（（substring（position，1，定位（'，'，位置））），DECIMAL（17,15）），
 longitude = CONVERT（（substring（position，locate（'，'，position）+1）），DECIMAL（18,15 ））
  

结果字段值适用于两种情况：

  45.000000000000000和-122.000000000000000 
 AND 
 45.000000000000000和-122.000000000000000 
  

任何人都可以看到我做错了什么吗？
Thanks。

解决方案

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 
  

我认为头寸是一个保留字，但我认为这不重要。但是，分配一个表别名并限定所有列引用并不会造成伤害。

  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））
  

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

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

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

<$ p $ （（17,15））AS lat_
，CAST（（（1），（1）， SUBSTRING（c.position，LOCATE（'，'，c.position）+1））AS DECIMAL（18,15））AS lon_
FROM camera c

，并验证它是否会生成您期望的十进制值。

点字符应该被识别为小数点。位置列是否包含一些其他特殊字符，如空格或其他？

从您发布的内容看，它看起来像是CAST和CONVERT，小数点。 （在那里不应该有一个隐式转换为有符号整数，所以不清楚为什么小数点后面的字符没有被包含在内。）

---

如果您可以确定用什么字符表示小数点，那么您可以使用MySQL REPLACE（）函数用一个简单的点字符替换它们。

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.

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:

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.

解决方案

Both the CAST and CONVERT forms seem to be correct.

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?

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

---

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如何将varchar（纬度，经度）转换为十进制字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！