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'')'
END
EXEC(@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 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
What 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 b
where b.Shape.STIntersects(@b.STCentroid()) = 1') AS AD
But getting below error
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '+'.
Throwing an error here in the above query
POLYGON ' + @input