Sql地理空间链接服务器查询

本文介绍了Sql地理空间链接服务器查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我对TSQL链接服务器查询有一个问题。链接服务器是GIS强制执行的，因此我们将坐标传递给该服务器，该服务器从链接服务器返回数据。请找到以下工作查询。 DECLARE @input varchar（max），@ sql varchar（max）; SET @input = N'（（ - 119.470830216356 46.2642458295079，-119.470722927989 46.2642050348762，-119.470076515615 46.2647075484513，-119.470240130371 46.2647075484512，-119.470830216356 46.2642458295079））' BEGIN SELECT @sql ='select * from openquery（[LinkedServerName]，''DECLARE @b geometry; SET @b = geometry :: STGeomFromText（'''''POLYGON'+ @input +'''''，4326）; SET @b = @ b.MakeValid（）; 来自[数据库]的SELECT *。[表] AS b 其中b.Shape.STIntersects（@ b.STCentroid（））= 1''）' END EXEC（@sql） 但问题是有时我们必须将超过8000个字符传递给输入参数@input，因为它是varchar（max）和EXEC命令都有8000个字符的限制。因此，我们试图摆脱动态SQL，以便我们可以使用2个输入变量传递输入（我们已经实现将输入拆分为C＃代码中每个8000个字符的子集，并将它们作为2个不同的输入发送到SQL查询） 。我们在实际服务器（链接服务器）中尝试了以下查询，该工作正常。 DECLARE @b geometry SET @input = N'（（ - 119.470830216356 46.2642458295079，-119.470722927989 46.2642050348762，' SET @ input2 = N'-119.470076515615 46.2647075484513，-119.470240130371 46.2647075484512，-119.470830216356 46.2642458295079））' SELECT @b = geometry :: STGeomFromText（'POLYGON'+ @input + @ input2 +''，4326） SELECT @b = @ b.MakeValid（） SELECT * FROM [Database] 。[TableName] AS b WHERE b.Shape.STIntersects（@ b.STCentroid（））= 1 我尝试过： DECLARE @input varchar（max），@ input2 varchar（max）; SET @input = N'（（ - 119.470830216356 46.2642458295079，-119.470722927989 46.2642050348762，' SET @ input2 = N'-119.470076515615 46.2647075484513，-119.470240130371 46.2647075484512，-119.470830216356 46.2642458295079））' SELECT * FROM OPENQUERY（[LinkedServerName]，'DECLARE @b geometry; SELECT @b = geometry :: STGeomFromText（''''POLYGON'+ @input + @ input2 +'''，4326）; SELECT @b = @ b.MakeValid（）; SELECT * FROM [DatabaseName]。[TableName] AS b 其中b.Shape.STIntersects （@ b.STCentroid（））= 1'）AS AD 但是低于误差 Msg 102，等级15，状态1，行11 '+'附近的语法不正确。 在上面的查询中输入错误 POLYGON'+ @input 解决方案 您的之前和之后单引号已更改。你需要简化你的字符串处理，以便更好地处理正在发生的事情。 SET @b = geometry :: STGeomFromText（ '''''POLYGON'+ @input + ''''， 4326）; SELECT @b = geometry :: STGeomFromText（''''POLYGON'+ @input + @ input2 +'''， 4326）; I have one question on the TSQL Linked Server Query. Linked Server is GIS enforced so we pass the coordinates to that server which it returns the data from the Linked Server. Please find the below-working query.DECLARE @input varchar(max), @sql varchar(max);SET @input = N'((-119.470830216356 46.2642458295079,-119.470722927989 46.2642050348762,-119.470076515615 46.2647075484513,-119.470240130371 46.2647075484512,-119.470830216356 46.2642458295079))'BEGIN SELECT @sql = 'select * from openquery([LinkedServerName],''DECLARE @b geometry; SET @b = geometry::STGeomFromText(''''POLYGON '+ @input + ' '''', 4326); SET @b = @b.MakeValid(); SELECT * from [Database].[Table] AS b where b.Shape.STIntersects(@b.STCentroid()) = 1'')'ENDEXEC(@sql)But the issue is sometimes we have to pass more than 8000 characters to the input parameter @input since it is varchar(max) and EXEC command both have an 8000 character limitation. So we are trying to get rid of Dynamic SQL so that we can pass the input using 2 input variables (We have implemented splitting the input into subsets each of 8000 characters in our C# code and sending them as 2 different inputs to the SQL Query). We have tried the below query in the Actual Server (Linked Server) which is working fine.DECLARE @b geometrySET @input = N'((-119.470830216356 46.2642458295079,-119.470722927989 46.2642050348762,'SET @input2 = N'-119.470076515615 46.2647075484513,-119.470240130371 46.2647075484512,-119.470830216356 46.2642458295079))'SELECT @b = geometry::STGeomFromText('POLYGON ' + @input + @input2 + '', 4326)SELECT @b = @b.MakeValid()SELECT * FROM [Database].[TableName] AS bWHERE b.Shape.STIntersects(@b.STCentroid()) = 1What I have tried:DECLARE @input varchar(max), @input2 varchar(max);SET @input = N'((-119.470830216356 46.2642458295079,-119.470722927989 46.2642050348762,'SET @input2 = N'-119.470076515615 46.2647075484513,-119.470240130371 46.2647075484512,-119.470830216356 46.2642458295079))'SELECT * FROM OPENQUERY([LinkedServerName],'DECLARE @b geometry;SELECT @b = geometry::STGeomFromText(''''POLYGON ' + @input + @input2 + '' ', 4326);SELECT @b = @b.MakeValid();SELECT * FROM [DatabaseName].[TableName] AS bwhere b.Shape.STIntersects(@b.STCentroid()) = 1') AS ADBut getting below errorMsg 102, Level 15, State 1, Line 11Incorrect syntax near '+'.Throwing an error here in the above queryPOLYGON ' + @input 解决方案 Your "before and after" single quotes "changed". You need to "simplify" your string handling so you get a better handle on what's happening.SET @b = geometry::STGeomFromText(''''POLYGON '+ @input + ' '''', 4326);SELECT @b = geometry::STGeomFromText(''''POLYGON ' + @input + @input2 + '' ', 4326); 这篇关于Sql地理空间链接服务器查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！