SQL Server Geography Functions Intersect

Question

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_xyz_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 [xyz_SDE].[xyz_CARTO].[CANTONES] WHERE canton_sap=@codCantonOr; SELECT @cantonCortaParcial = canton_sap, @geometryCantonCortaParcial=[Shape] FROM xyz_sde.ence_carto.corta_parcial WHERE canton_sap = @clamonSap; SELECT @idMunicipio= municipio_id FROM [xyz_SDE].[xyz_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 [xyz_SDE].[xyz_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 [xyz_SDE].[xyz_CARTO].[CATASTRO_PARCELA] WITH(INDEX(s188_idx)) WHERE @geometryCantonCortaParcial.stintersects(shape)=1 AND LEFT(refcat, 2)=LEFT(@idMunicipio, 2) END end‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

bixb0012 · Answer

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.