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
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.