时间戳

首页 > 解决方案 > 将 varchar 值 '(175.622313 -40.3554891)' 转换为数据类型 int 时转换失败

sql - 将 varchar 值 '(175.622313 -40.3554891)' 转换为数据类型 int 时转换失败

问题描述

Righto，一直在寻找年龄，但无济于事。我正在处理空间数据。

use[joeldb3]
go

create proc UDP_CREATE_TABLES
as

create table points
(
    id int identity(1,1) primary key,
    point geography not null,
    label varchar(30) not null
)


create table polygons
(
    id int identity(1,1) primary key,
    polygon geography not null,
    label varchar(30)
)


create table linestrings
(
    id int identity(1,1) primary key,
    linestring geography not null,
    label varchar(30),
)
go


create proc UDP_INSERT_WKT (@shapetype varchar(10), @WKT varchar(max), @desc varchar(30))
as

if (@shapetype = 'POINT')
insert into points (point, label) values(geography::STPointFromText(@shapetype, @WKT), @desc)

else if (@shapetype = 'LINESTRING')
insert into linestrings (linestring, label) values(geography::STLineFromText(@shapetype, @WKT), @desc)

else if (@shapetype = 'POLYGON')
insert into polygons (polygon, label) values(geography::STPolyFromText(@shapetype, @WKT), @desc)


go

这是我创建创建表并填充它们的过程的查询，我将把执行查询放在下面。

use [joeldb3]
go

if object_id('points', 'U') is not null
drop table points
go

if object_id('polygons', 'U') is not null
drop table polygons
go

if object_id('linestrings', 'U') is not null
drop table linestrings
go

--Task 1
exec UDP_CREATE_TABLES
go

--Task 2
exec UDP_INSERT_WKT 'POINT', '(175.622313 -40.3554891)', 'Point 1'
exec UDP_INSERT_WKT 'POINT', '(175.62430859999998 -40.350403500000006)', 'Point 2'
exec UDP_INSERT_WKT 'POINT', '(175.62664750000002 -40.3535268)', 'Point 3'
exec UDP_INSERT_WKT 'POINT', '(175.6153822 -40.35440979999999)', 'Point 4'
exec UDP_INSERT_WKT 'POINT', '(175.6129789 -40.3568135)', 'Point 5'
exec UDP_INSERT_WKT 'POINT', '(175.6119919 -40.3573041)', 'Point 6'

exec UDP_INSERT_WKT 'LINESTRING', '(175.62753659999998 -40.3543383,175.6272912 -40.3544916,175.62430859999998 -40.350403500000006,175.6122065 -40.355848800000004,175.6120348 -40.3556362,175.61166999999998 -40.35588150000001,175.6115393 -40.355753199999995)', 'Route 1'
exec UDP_INSERT_WKT 'LINESTRING', '(175.62753659999998 -40.3543383,175.623064 -40.35648650000002,175.622313 -40.3554891,175.6136656 -40.3595278,175.6119919 -40.3573041,175.6129789 -40.3568135,175.6122065 -40.355848800000004,175.6120348 -40.3556362,175.61166999999998 -40.35588150000001,175.6114273 -40.355780800000005)', 'Route 2'

exec UDP_INSERT_WKT 'POLYGON', '((175.62753659999998 -40.3543383,175.6276339 -40.3544671,175.6277847 -40.354401200000005,175.6276855 -40.354271899999986,175.62753659999998 -40.3543383))', 'Home'
exec UDP_INSERT_WKT 'POLYGON', '((175.6113522 -40.355529900000015,175.611314 -40.3555458,175.61132470000004 -40.3555596,175.6113072 -40.3555662,175.61129790000004 -40.355576400000004,175.6113039 -40.355590199999995,175.6112811 -40.355602,175.6112643 -40.3556148,175.6112663 -40.3556342,175.6112865 -40.35564289999999,175.6113079 -40.3556424,175.6113394 -40.35568680000001,175.61130660000003 -40.355702099999995,175.61137769999996 -40.355801299999996,175.6114273 -40.355780800000005,175.61146890000003 -40.3557854,175.611493 -40.35578029999999,175.6115191 -40.3557696,175.6115393 -40.355753199999995,175.6115594 -40.3557251,175.6116311 -40.355693499999994,175.6115896 -40.3556398,175.61155600000004 -40.35565359999999,175.6115212 -40.35560660000001,175.6114803 -40.355624,175.61144539999998 -40.355577000000004,175.61147149999996 -40.355561599999994,175.6114729 -40.355542199999995,175.6114481 -40.355529900000015,175.6114125 -40.35554119999999,175.6114011 -40.3555494,175.6113944 -40.35553959999999,175.611375 -40.35553500000001,175.6113609 -40.3555407,175.6113522 -40.355529900000015))', 'iSite'
exec UDP_INSERT_WKT 'POLYGON', '((175.6129789 -40.35112300000001,175.6039453 -40.3551293,175.6050611 -40.3566827,175.60523269999996 -40.35660099999999,175.60645580000002 -40.3582688,175.60750719999996 -40.3590046,175.60935259999997 -40.3612936,175.61789269999997 -40.357483900000005,175.6129789 -40.35112300000001))', 'Ring Road'

我尝试使用 CAST()，并确保数据类型相同等，但无法使其正常工作。任何人都有一个不涉及以完全不同的方式执行此操作的解决方案？这都是为了一个任务，我不能做不同的事情。

标签： sqlsql-server

解决方案

UDP_INSERT_WKT存储过程正在传递函数无法理解的值。尝试这个...

create proc UDP_INSERT_WKT (@shapetype varchar(10), @WKT varchar(max), @desc varchar(30))
as

if (@shapetype = 'POINT')
    insert into points (point, label) values(geography::STPointFromText(@shapetype + @WKT), @desc)

else if (@shapetype = 'LINESTRING')
    insert into linestrings (linestring, label) values(geography::STLineFromText(@shapetype + @WKT), @desc)

else if (@shapetype = 'POLYGON')
    insert into polygons (polygon, label) values(geography::STPolyFromText(@shapetype + @WKT), @desc)

推荐阅读