SQL Server Geography Functions Intersect

AlbertoLópez — Sun, 12 Dec 2021 10:05:58 GMT

Hi;

I have 2 layers (tables) in SQL Server/ArcGIS Enterprise Geodatabase and I have to intersect those layers and retrieve the areas of the intersections.

I am using a store procedure (SP) with geometrics functions in SQL Server.

The layers in SQL Server are in Geographic Coordinate System GCS_ETRS_1989. But the results of the intersection and the areas returned by the SP are not correct. The result I get doing the intersect in ArcMap does not give the same results.

The result in the SP is:

The result in ArcMap is:

I do not know if the problem is due to the Coordinate System.

This is the code. Any ideas?. Thanks.

USE [ENCE_SDE]goSET ansi_nulls ONgoSET quoted_identifier ONgoALTER PROCEDURE [ENCE_CARTO].[sp_XXX_getCatastroParcelaByCanton] 
  @codCantonOr NVARCHAR (40) 
AS 
  BEGIN 
    DECLARE @geometryCantonOr GEOMETRY; 
    DECLARE @idMunicipio NVARCHAR(5); 
    DECLARE @clamonSap   NVARCHAR(40); 
    DECLARE @geogrpah AS GEOGRAPHY; 
    DECLARE @cantonCortaParcial NVARCHAR(40); 
    DECLARE @geometryCantonCortaParcial GEOMETRY; 
    EXECUTE AS 
      caller 
    SET nocount ON; 
    SELECT @geometryCantonOr =[Shape], 
           @clamonSap=clamon_sap 
    FROM   [XXX_SDE].[XXX_CARTO].[CANTONES] 
    WHERE  canton_sap=@codCantonOr; 
     
    SELECT @cantonCortaParcial = canton_sap, 
           @geometryCantonCortaParcial=[Shape] 
    FROM   xxx_sde.ence_carto.corta_parcial 
    WHERE  canton_sap = @clamonSap; 
     
    SELECT @idMunicipio= municipio_id 
    FROM   [XXX_SDE].[XXX_CARTO].[MONTES] 
    WHERE  ut_monte_sap =@clamonSap; 
     
    IF (@cantonCortaParcial IS NULL) 
    BEGIN 
      SELECT objectid, 
             refcat, 
             (geography::STGeomFromWKB( 
             ( 
                    SELECT shape.Stasbinary()), 4326)).MakeValid().starea() AS suptotal, 
             (geography::stgeomfromwkb( 
             ( 
                    SELECT (@geometryCantonOr.STIntersection(shape)).STAsBinary()), 4326)).starea() AS supinter,
             LEFT(refcat, 5)                                                                        AS municipio
      FROM   [XXX_SDE].[XXX_CARTO].[CATASTRO_PARCELA] WITH(INDEX(s188_idx)) 
      WHERE  @geometryCantonOr.stintersects(shape)=1 
      AND    LEFT(refcat, 2)=LEFT(@idMunicipio, 2) 
    END 
    ELSE 
    BEGIN 
      SELECT objectid, 
             refcat, 
             (geography::STGeomFromWKB( 
             ( 
                    SELECT shape.Stasbinary()), 4326)).MakeValid().starea() AS suptotal, 
             (geography::stgeomfromwkb( 
             ( 
                    SELECT (@geometryCantonOr.STIntersection(shape)).STAsBinary()), 4326)).starea() AS supinter,
             LEFT(refcat, 5)                                                                        AS municipio
      FROM   [XXX_SDE].[XXX_CARTO].[CATASTRO_PARCELA] WITH(INDEX(s188_idx)) 
      WHERE  @geometryCantonCortaParcial.stintersects(shape)=1 
      AND    LEFT(refcat, 2)=LEFT(@idMunicipio, 2) 
    END 
    end‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: SQL Server Geography Functions Intersect

JoshuaBixby — Thu, 06 Feb 2020 16:36:56 GMT

The first thing that comes to mind when talking enterprise geodatabases and different results between SQL outside of ArcGIS clients and results from within ArcGIS clients are versioned data. If the data is versioned, make sure you are running your SQL outside of ArcGIS on the same versioned data as what is being used within ArcGIS.

topic Re: SQL Server Geography Functions Intersect in Geodatabase Questions

SQL Server Geography Functions Intersect

Re: SQL Server Geography Functions Intersect