Latest Contributions by JohnEdwards2

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