遵义市住房和城乡建设局官方网站6,临武县网站建设,rp如何做网站,ui设计 国外网站1.工作要求
计算某两个经纬度距离 2.遇到问题
从数据类型 varchar 转换为 numeric 时出错。
3.解决问题
项目版本较老#xff0c;使用SQLServer 2012 计算距离需执行视图#xff0c;如下#xff1a;
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER view
vi_ord…1.工作要求
计算某两个经纬度距离 2.遇到问题
从数据类型 varchar 转换为 numeric 时出错。
3.解决问题
项目版本较老使用SQLServer 2012 计算距离需执行视图如下
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER view
vi_ordensilingtranslist
as
SELECT a.csendlongitude,csendlatitud,ilongitude,ilatitude,cmclat,cmclng,a.cgroupcontractname ,a.censilingcode ,a.ccorpname ,a.ccustomername ,a.dfhtime ,a.cmassifname ,a.cjhname ,a.izttime ,a.cinvname ,a.imweight ,a.ipweight ,a.iweight ,a.istarchvalue ,a.iqualityper ,b.iklfracture,a.ccarcode,a.carcode
,CASE WHEN a.cmassifname无地块 OR ISNULL(a.csendlongitude,) OR ISNULL(c.ilatitude,) THEN 无地块或无发货坐标无法计算 ELSE CAST( ROUND(dbo.fnGetDistance(a.csendlongitude,a.csendlatitud,c.ilatitude,c.ilongitude)/1000,2,0) AS VARCHAR(250)) END AS cfhdis ,CASE WHEN a.cmassifname无地块 OR ISNULL(d.cmclat,)OR ISNULL(c.ilongitude,) OR ISNULL(a.cmassifname,) THEN 无地块或牧场未设置坐标无法计算 ELSE CAST( ROUND(dbo.fnGetDistance(c.ilongitude,c.ilatitude,d.cmclat,d.cmclng)/1000,2,0) AS VARCHAR(250)) END AS cdkmcdis,CASE WHEN ISNULL(a.csendlongitude,)OR ISNULL(d.cmclat,) THEN 无发货位置或无牧场坐标无法计算 ELSE CAST( ROUND(dbo.fnGetDistance(a.csendlongitude,a.csendlatitud,d.cmclat,d.cmclng)/1000,2,0) AS VARCHAR(250)) END AS cfhmcdis
FROM dbo.Op_EnsilingTrans a WITH(NOLOCK) LEFT JOIN dbo.op_sensorytest b WITH(NOLOCK) ON a.censilingcodeb.clzcode
LEFT JOIN dbo.op_massif c WITH(NOLOCK) ON a.cmassifcodec.cmassifcode
LEFT JOIN dbo.Sys_AccountSet d WITH(NOLOCK) ON a.ccorpcoded.cztcode
WHERE ISNULL(a.iweight,0)0GO根据经纬度计算公里数函数如下
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[fnGetDistance](sendlog VARCHAR(50), sendlat VARCHAR(50), lat VARCHAR(50), log VARCHAR(50))
RETURNS FLOAT
AS
BEGINDECLARE a GEOGRAPHY;DECLARE b GEOGRAPHY;DECLARE distance REALDECLARE atxt VARCHAR(50);DECLARE btxt VARCHAR(50);SET atxt POINT(sendlog sendlat);SET btxtPOINT(log lat);SET a geography::STGeomFromText(atxt, 4326);SET b geography::STGeomFromText(btxt, 4326);-- 计算距离并将结果存入输出参数SET distance ROUND(a.STDistance(b), 1); -- 将距离转换为公里并进行四舍五入RETURN distance
END
GO执行视图报错
消息 8114级别 16状态 5第 1 行
从数据类型 varchar 转换为 numeric 时出错。错误原因isnull的使用问题 错误点 在视图内ISNULL(a.csendlongitude,) OR ISNULL(c.ilatitude,) 这里a表即Op_EnsilingTrans表经纬度字段类型为nvarchar而c表即op_massif表经纬度字段为decimal, 错就错在decimal, 下面是正确写法
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER view
vi_ordensilingtranslist
as
SELECT a.csendlongitude,csendlatitud,ilongitude,ilatitude,cmclat,cmclng,a.cgroupcontractname ,a.censilingcode ,a.ccorpname ,a.ccustomername ,a.dfhtime ,a.cmassifname ,a.cjhname ,a.izttime ,a.cinvname ,a.imweight ,a.ipweight ,a.iweight ,a.istarchvalue ,a.iqualityper ,b.iklfracture,a.ccarcode,a.carcode
,CASE WHEN a.cmassifname无地块 OR ISNULL(a.csendlongitude,) OR ISNULL(c.ilatitude,0) 0 THEN 无地块或无发货坐标无法计算 ELSE CAST( ROUND(dbo.fnGetDistance(a.csendlongitude,a.csendlatitud,c.ilatitude,c.ilongitude)/1000,2,0) AS VARCHAR(250)) END AS cfhdis ,CASE WHEN a.cmassifname无地块 OR ISNULL(d.cmclat,)OR ISNULL(c.ilongitude,0) 0 OR ISNULL(a.cmassifname,) THEN 无地块或牧场未设置坐标无法计算 ELSE CAST( ROUND(dbo.fnGetDistance(c.ilongitude,c.ilatitude,d.cmclat,d.cmclng)/1000,2,0) AS VARCHAR(250)) END AS cdkmcdis,CASE WHEN ISNULL(a.csendlongitude,)OR ISNULL(d.cmclat,) THEN 无发货位置或无牧场坐标无法计算 ELSE CAST( ROUND(dbo.fnGetDistance(a.csendlongitude,a.csendlatitud,d.cmclat,d.cmclng)/1000,2,0) AS VARCHAR(250)) END AS cfhmcdis
FROM dbo.Op_EnsilingTrans a WITH(NOLOCK) LEFT JOIN dbo.op_sensorytest b WITH(NOLOCK) ON a.censilingcodeb.clzcode
LEFT JOIN dbo.op_massif c WITH(NOLOCK) ON a.cmassifcodec.cmassifcode
LEFT JOIN dbo.Sys_AccountSet d WITH(NOLOCK) ON a.ccorpcoded.cztcode
WHERE ISNULL(a.iweight,0)0GO4.结论
建表时注意字段类型统一
