AnsweredAssumed Answered

SQL Server Geography Functions Intersect

Question asked by ARROCAL on Feb 6, 2020
Latest reply on Feb 6, 2020 by bixb0012

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

Outcomes