AnsweredAssumed Answered

Geometry vs Geography

Question asked by jedwards5 on Nov 24, 2016
Latest reply on Nov 30, 2016 by bixb0012
Hi,
Currently I'm doing a conversion from MSSQL to Spark/SparkSQL.  I’m using the esri-geometry-api and spatial-sdk-hadoop packages from esri which are mostly fine.  Except the data in sql server is of geography data type, with the smaller polygon conversions all was well but with the bigger ones where the earths curvature has some impact I am getting differing results.  The MS SQL sample code below examines the differences between the two types where the Geography ones is the one being using in the source system I am porting the functionality from, the MS-SQL-Geometry example matches the same as I am getting from the ESRI hadoop kit also.
If anyone could assist in helping find equivalent functionality in either the ESRI libraries (or another libraries) it would be much appreciated.
MS SQL - Geography - Correctly identifies the point as belonging to the RegionA - This is what I want to end up with.
DECLARE @polyA geography
DECLARE @polyB geography
DECLARE @point geography
SET @point = geography::STGeomFromText('POINT(-1.5667444464495464 55.07067670927046)',4326);
SET @polyA = geography::STGeomFromText('POLYGON ((-8 48.83305556, -2 50, -0.25 50, 1.466666667 50.66666667, 1.466666667 51, 2 51.11638889, 2 51.5, 5 55, -5.5 55, -5.5 52.33305556, -8 51, -8 48.83305556))',4326)
SET @ployB = geography::STGeomFromText('POLYGON ((-10 54.5663888888889, -9 54.75, -8.25 55.3330555555556, -7.33305555555556 55.4166666666667, -6.91666666666667 55.3330555555556, -8.16666666666667 54.4166666666667, -5.5 53.9166666666667, -5.5 55, 5 55, 5 57, 0 60, 0 61, -10 61, -10 54.5663888888889))',4326)
SELECT @polyA.STContains(@point) as inRegionA, @polyB.STContains(@point) as inRegionB;

Results
inRegionA      inRegionB
-------------- ----------------
1              0
MS SQL - Geometry - Incorrectly identifies the point as belonging to the RegionB - This matches the ESRI Hadoop library results
DECLARE @polyA2 geometry
DECLARE @polyB2 geometry
DECLARE @point2 geometry
SET @point2 = geometry::STGeomFromText('POINT(-1.5667444464495464 55.07067670927046)',4326);
SET @polyA2 = geometry::STGeomFromText('POLYGON ((-8 48.83305556, -2 50, -0.25 50, 1.466666667 50.66666667, 1.466666667 51, 2 51.11638889, 2 51.5, 5 55, -5.5 55, -5.5 52.33305556, -8 51, -8 48.83305556))',4326)
SET @polyB2 = geometry::STGeomFromText('POLYGON ((-10 54.5663888888889, -9 54.75, -8.25 55.3330555555556, -7.33305555555556 55.4166666666667, -6.91666666666667 55.3330555555556, -8.16666666666667 54.4166666666667, -5.5 53.9166666666667, -5.5 55, 5 55, 5 57, 0 60, 0 61, -10 61, -10 54.5663888888889))',4326)
SELECT @polyA2.STContains(@point2) as inRegionA, @polyB2.STContains(@point2) as inRegionB;

Results
inRegionA      inRegionB
-------------- ----------------
0              1

Geography vs Geometry Plotted Information
Plots
Hive/SparkSQL - ESRI Geometry - Correctly identifies the point as belonging to the RegionB because it's using geometry but I want to use Geography types - is there a way?
add jar esri-geometry-api.jar;
add jar spatial-sdk-hadoop.jar;
create function ST_GeomFromText as 'com.esri.hadoop.hive.ST_GeomFromText';
create function ST_Contains as 'com.esri.hadoop.hive.ST_Contains';
SELECT
ST_Contains(

ST_GeomFromText('POLYGON ((-8 48.83305556, -2 50, -0.25 50, 1.466666667 50.66666667, 1.466666667 51, 2 51.11638889, 2 51.5, 5 55, -5.5 55, -5.5 52.33305556, -8 51, -8 48.83305556))', 4326),
ST_GeomFromText('POINT(-1.5667444464495464 55.07067670927046)',4326)
) AS inRegionA, 
ST_Contains(
ST_GeomFromText('POLYGON ((-10 54.5663888888889, -9 54.75, -8.25 55.3330555555556, -7.33305555555556 55.4166666666667, -6.91666666666667 55.3330555555556, -8.16666666666667 54.4166666666667, -5.5 53.9166666666667, -5.5 55, 5 55, 5 57, 0 60, 0 61, -10 61, -10 54.5663888888889))', 4326),
ST_GeomFromText('POINT(-1.5667444464495464 55.07067670927046)',4326)
) AS inRegionB;
Results
inRegionA       inRegionB
--------------  ----------------
false           true

Outcomes