You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@sedona.apache.org by "JimShady (via GitHub)" <gi...@apache.org> on 2024/07/08 16:08:16 UTC

[I] st_union - understanding difference with PostGIS [sedona]

JimShady opened a new issue, #1514:
URL: https://github.com/apache/sedona/issues/1514

   I am trying to move a process which I currently run in PostGIS, to within the cloud and use Apache Sedona instead. In my PostgreSQL + PostGIS database this runs:
   
   ```
   WITH a_table AS (
   SELECT st_boundary(geom) geom
   FROM   emea_eu_seu.albania_prefectures_2021
   UNION ALL
   SELECT st_boundary(geom) geom
   FROM   emea_eu_seu.albania_municipalities_2021
   UNION ALL
   SELECT st_boundary(geom) geom
   FROM   emea_eu_seu.albania_2_digit_postcode_areas_2021
   ),
   b_table AS (
   SELECT   geom
   FROM     a_table
   GROUP BY geom
   )
   SELECT   ST_Union(geom) geom
   FROM     b_table
   ```
   
   The result is one row:
   
   ![image](https://github.com/apache/sedona/assets/2901470/8e467cf8-1025-4cda-8599-cc4ea6d0c75b)
   
   I have tried to run the same process using Sedona (code below) but get an error:
   
   ```
   WITH a_table AS (
   SELECT st_boundary(st_geomfromwkb(geom_wkb)) geom
   FROM   hive_metastore.gfk_bronze.albania_prefectures_2021
   UNION ALL
   SELECT st_boundary(st_geomfromwkb(geom_wkb)) geom
   FROM   hive_metastore.gfk_bronze.albania_municipalities_2021
   UNION ALL
   SELECT st_boundary(st_geomfromwkb(geom_wkb)) geom
   FROM   hive_metastore.gfk_bronze.albania_2_digit_postcode_areas_2021
   ),
   b_table AS (
   SELECT   geom
   FROM     a_table
   GROUP BY geom
   )
   SELECT   ST_Union(geom) geom
   FROM     b_table
   
   ```
   
   ```
   AnalysisException: [DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] Cannot resolve "st_union(geom)" due to data type mismatch: Parameter 1 requires the "ARRAY<BINARY>" type, however "geom" has the type "BINARY".; line 16 pos 9;
   'WithCTE
   ```
   
   I am using Sedona 1.6.0 by the way. I read "Variant 2 : As of version 1.6.0, this function accepts an array of Geometry objects and returns the geometric union of all geometries in the input array." so was expecting a similar result to that which PostGIS returns. Am I misunderstanding something?
   
   Thanks!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@sedona.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] st_union - understanding difference with PostGIS [sedona]

Posted by "JimShady (via GitHub)" <gi...@apache.org>.
JimShady commented on issue #1514:
URL: https://github.com/apache/sedona/issues/1514#issuecomment-2218260164

   Any ideas on how to work around this? The same query finishes in 3 seconds on my fairly modest PostgreSQL server!
   
   ```
   %sql
   
   WITH a_table AS (
   SELECT st_boundary(st_geomfromwkb(geom_wkb)) geom
   FROM   hive_metastore.gfk_bronze.albania_prefectures_2021
   UNION ALL
   SELECT st_boundary(st_geomfromwkb(geom_wkb)) geom
   FROM   hive_metastore.gfk_bronze.albania_municipalities_2021
   UNION ALL
   SELECT st_boundary(st_geomfromwkb(geom_wkb)) geom
   FROM   hive_metastore.gfk_bronze.albania_2_digit_postcode_areas_2021
   ),
   b_table AS (
   SELECT   geom
   FROM     a_table
   GROUP BY geom
   )
   SELECT   ST_Union_Aggr(geom) geom
   FROM     b_table
   ```
   
   ```
   java.lang.Exception: Results too large
   	at com.databricks.backend.daemon.driver.OutputAggregator$.maybeApplyOutputAggregation(OutputAggregator.scala:458)
   	at com.databricks.backend.daemon.driver.OutputAggregator$.withOutputAggregation0(OutputAggregator.scala:279)
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@sedona.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] st_union - understanding difference with PostGIS [sedona]

Posted by "JimShady (via GitHub)" <gi...@apache.org>.
JimShady commented on issue #1514:
URL: https://github.com/apache/sedona/issues/1514#issuecomment-2217712834

   Isn't the idea that from 1.6.0 onwards, the behavior is the same?
   
   ![image](https://github.com/apache/sedona/assets/2901470/d4cd2719-db2f-46a7-bfb5-22547c185c24)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@sedona.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] st_union - understanding difference with PostGIS [sedona]

Posted by "furqaankhan (via GitHub)" <gi...@apache.org>.
furqaankhan commented on issue #1514:
URL: https://github.com/apache/sedona/issues/1514#issuecomment-2217704860

   I think you would want to use ST_Union_Aggr. As the one parameter option of ST_Union expected an Array of geometries. It can be confusing as PostGIS has ST_Union and ST_Union_Aggr as one function.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@sedona.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] st_union - understanding difference with PostGIS [sedona]

Posted by "furqaankhan (via GitHub)" <gi...@apache.org>.
furqaankhan commented on issue #1514:
URL: https://github.com/apache/sedona/issues/1514#issuecomment-2217731873

   The ST_Union accepts an array of geometries ( [geom1, geom2] ) in each row. What you want to achieve from my understanding is to do ST_Union of all the rows in the specified column, for that we have ST_Union_Aggr. In PostGIS when you are invoking ST_Union after group by, you are using [the fourth variant not the third](https://postgis.net/docs/ST_Union.html#:~:text=geometry%20ST_Union(geometry%20set%20g1field)%3B).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@sedona.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] st_union - understanding difference with PostGIS [sedona]

Posted by "JimShady (via GitHub)" <gi...@apache.org>.
JimShady commented on issue #1514:
URL: https://github.com/apache/sedona/issues/1514#issuecomment-2228196748

   This was because the browser could not display the results. Writing to file it was fine.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@sedona.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] st_union - understanding difference with PostGIS [sedona]

Posted by "JimShady (via GitHub)" <gi...@apache.org>.
JimShady closed issue #1514: st_union - understanding difference with PostGIS
URL: https://github.com/apache/sedona/issues/1514


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@sedona.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] st_union - understanding difference with PostGIS [sedona]

Posted by "JimShady (via GitHub)" <gi...@apache.org>.
JimShady commented on issue #1514:
URL: https://github.com/apache/sedona/issues/1514#issuecomment-2218161870

   Ok, I'll give that a go and report back. :-)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@sedona.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org