Skip to content

Instantly share code, notes, and snippets.

@djouallah
Created March 7, 2022 01:46
Show Gist options
  • Save djouallah/504e001f54727185bd2190b4259535c2 to your computer and use it in GitHub Desktop.
Save djouallah/504e001f54727185bd2190b4259535c2 to your computer and use it in GitHub Desktop.
select `WKT`,
`C1`,
`C2`
from
(
select `WKT`,
sum(`area`) as `C1`,
min(`WKT`) as `C2`
from
(
WITH
xxx AS (
SELECT
*
FROM
UNNEST( ['POINT(138.6780384 -31.0947747)','POINT(138.4236902 -30.5530431)','POINT(138.4236351 -30.5558298)','POINT(138.4214329 -31.8875526)','POINT(138.0427624 -32.3463532)','POINT(138.0422809 -32.3464209)','POINT(138.0414668 -32.3468268)','POINT(137.7437191 -32.4616685)'] ) AS element),
yyy AS (
SELECT
ST_CONVEXHULL( ST_UNION_AGG(ST_GEOGFROMTEXT(element))) AS geo
FROM
xxx)
SELECT
ST_ASTEXT(geo) AS WKT,
st_area(geo) AS area
FROM
yyy
) as `_`
group by `WKT`
) as `ITBL`
where not `C1` is null or not `C2` is null
LIMIT 1000001 OFFSET 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment