none
SQL Geo-Spatial Linked Server Query

    질문

  • 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


    Msg 102, Level 15, State 1, Line 11
    Incorrect syntax near '+'.


    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

    In the above query, an issue has been highlighted in the below-underlined.

    SELECT @b = geometry::STGeomFromText(''''POLYGON ' + @input + @input2 + '' ', 4326);

    Any Help is really appreciated. 
    2018년 7월 13일 금요일 오후 3:30

모든 응답

  • You have unbalanced single quotes on the end. 

    Change:

    ...''''POLYGON ' + @input + @input2 + '' '...
    

    To

    ''''POLYGON ' + @input + @input2 + ''' '

    2018년 7월 13일 금요일 오후 4:54
    중재자
  • Thanks for the reply, I tried that way you suggested but unfortunately it is not working either. If I change like that the starting quotes in ending with the below once and rest of the query it is not treating in the quoted queries. 

    @input2 + ''' '
    2018년 7월 13일 금요일 오후 5:55
  • Try this:

    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

    You need to double up all the internal single quotes.

    2018년 7월 13일 금요일 오후 6:11
    중재자
  • Thanks, I tried that way now I am getting below error. 

    OLE DB provider "SQLNCLI11" for linked server "GISserver" returned "Deferred prepare could not be completed.".
    Msg 8180, Level 16, State 1, Line 1
    Statement(s) could not be prepared.
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near 'POLYGON'.



    2018년 7월 13일 금요일 오후 6:26
  • Ah, I was just fixing your quote issue.  Now that I read what you are trying to do, you can't do what you are trying to do.

    You can't append strings inside the OPENQUERY function call.  You have to resolve it outside the call, and you can't pass a variable to OPENQUERY, so you are stuck.

    In addition, OPENQUERY only take 8K chars for a query.  So you this anyway. https://docs.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?view=sql-server-2017

    You need to change your program to not send >8000 chars in @input1+@input2 minus the other chars in the query.  Or not use openquery.

    2018년 7월 13일 금요일 오후 6:41
    중재자
  • Yah you are right. Then instead of using Linked Query, we might need to create a Stored Procedure in the Actual GIS Server and pass parameters to the Stored Procedure to get the data. Anyway, Thank You very much for your help. Really appreciate that. 
    2018년 7월 13일 금요일 오후 7:05
  • Yah you are right. Then instead of using Linked Query, we might need to create a Stored Procedure in the Actual GIS Server and pass parameters to the Stored Procedure to get the data. Anyway, Thank You very much for your help. Really appreciate that. 

    Since you have found out the reason of your problem, please kindly help close the thread by marking useful reply as answer.

    Thanks for your cooperation.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 7월 16일 월요일 오전 6:28