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:

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?

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