Support Questions

Find answers, ask questions, and share your expertise

ST_Transform is not supported in ESRI Spatial Framework for Hadoop. What are the alternatives?

avatar

ST_Transform is not supported in ESRI Spatial Framework for Hadoop. What are the alternatives?. St_transform is used to transform the one spatial coordinate system to other coordinate system.

1 ACCEPTED SOLUTION

avatar
Super Guru

@Mahipal Ramidi

Actually, ST_Transform is supported, but it has a slightly different behavior when used in HIve. in traditional implementations like Netezza, Oracle or SQL Server, ST_Transform converts two-dimensional ST_Geometry data into the spatial reference specified by the spatial reference ID (SRID). SRID parameter is not supported in Hive. As such, I suggest you to pre-process the data a denormalize your table structure to account for your SRID. This is a good approach if you have a limited number of SRIDs to support. If number is high, then you may need to write a custom UDF and use in Hive. That is if you need to implement ST_Transform with SRID in SQL. There are other options if the geometry subject of the conversion is a small amount and is a matter of how is reflected in the UI. You may consider implementing a JavaScript function or a REST web service. Overall, it is a matter of good design.

Check this reference for functions in Hive with different behavior and not only: https://github.com/Esri/spatial-framework-for-hadoop/wiki/ST_Geometry-in-Hive-versus-SQL

View solution in original post

3 REPLIES 3

avatar
Super Guru

@Mahipal Ramidi

Actually, ST_Transform is supported, but it has a slightly different behavior when used in HIve. in traditional implementations like Netezza, Oracle or SQL Server, ST_Transform converts two-dimensional ST_Geometry data into the spatial reference specified by the spatial reference ID (SRID). SRID parameter is not supported in Hive. As such, I suggest you to pre-process the data a denormalize your table structure to account for your SRID. This is a good approach if you have a limited number of SRIDs to support. If number is high, then you may need to write a custom UDF and use in Hive. That is if you need to implement ST_Transform with SRID in SQL. There are other options if the geometry subject of the conversion is a small amount and is a matter of how is reflected in the UI. You may consider implementing a JavaScript function or a REST web service. Overall, it is a matter of good design.

Check this reference for functions in Hive with different behavior and not only: https://github.com/Esri/spatial-framework-for-hadoop/wiki/ST_Geometry-in-Hive-versus-SQL

avatar
New Contributor

@Constantin Stanca Hi constantin ,i have a question about ST_Transform,i have a coordinate in a laea spatial coordinate system and i want to convert them to another coordinate system but i don't know how i can add ST_Transform class to use this function.Thank you

avatar
Super Guru

@nizar saddiki

In other databases (other than Hive), ST_Transform converts two-dimensional ST_Geometry data into the spatial reference specified by the spatial reference ID (SRID). SRID parameter is not supported in Hive. As such, you need to pre-process the data in other system before uploading to Hive. Usually, that leads to denormalization. You would add a new column for each SRID. However, if they are way too many, is probably better to write your own ST_Transform service or function. I wish I could give better news.

Check this article: https://community.hortonworks.com/articles/44319/geo-spatial-queries-with-hive-using-esri-geometry.h.... Also: https://community.hortonworks.com/articles/44319/geo-spatial-queries-with-hive-using-esri-geometry.h...

It will show you how to add the jar and create the function, as well as how to use it. Second article includes some limitations.