汇总.sql 1.2 KB

123456789101112131415161718192021222324252627282930
  1. WITH intersected_data AS (
  2. SELECT
  3. ST_Intersection(
  4. ST_GeomFromText('POLYGON ((109.51142919411961 18.322906808030652, 109.51382391646248 18.323160890707612, 109.51812407725552 18.318549085673954, 109.52096478636398 18.31677099219241, 109.52639647097506 18.31534849921383, 109.52681717214205 18.307529099172545, 109.51255360145637 18.30725787066725, 109.50859860338323 18.311988017573178, 109.51142919411961 18.322906808030652))', 4326),
  5. dk.geom
  6. ) AS intersection_geom,
  7. dk.lbmc
  8. FROM
  9. "TB_GHDK" AS dk
  10. WHERE
  11. ST_Intersects(
  12. ST_GeomFromText('POLYGON ((109.51142919411961 18.322906808030652, 109.51382391646248 18.323160890707612, 109.51812407725552 18.318549085673954, 109.52096478636398 18.31677099219241, 109.52639647097506 18.31534849921383, 109.52681717214205 18.307529099172545, 109.51255360145637 18.30725787066725, 109.50859860338323 18.311988017573178, 109.51142919411961 18.322906808030652))', 4326),
  13. dk.geom
  14. )
  15. )
  16. SELECT
  17. lbmc,
  18. merged_geom,
  19. ST_Area(merged_geom::geography) AS area
  20. FROM (
  21. SELECT
  22. lbmc,
  23. ST_Union(intersection_geom) AS merged_geom
  24. FROM
  25. intersected_data
  26. GROUP BY
  27. lbmc
  28. ) AS unioned_data
  29. ORDER BY
  30. lbmc;