Geek asks and answers

How can I optimize this subquery as a join?

I noticed that running this subquery

SELECT ST_Area (ST_Union (ST_Transform (ST_Intersection ((SELECT poly1.the_geom of poly1 WHERE poly1.polygon_type = 'P'), poly2.the_geom), 3857)))

AS area_of_P FROM poly1, poly2

significantly slower than starting this connection

SELECT ST_AREA (ST_Union (ST_Transform (ST_Intersection (poly1.the_geom, poly2.the_geom), 3857)))

AS area_of_poly

FROM poly2

LEFT JOIN poly1 on st_intersects (poly1.the_geom, poly2.the_geom)

WHERE poly2.polygon_type = 'P'

However, I need to expand this second merged version to return more columns, each of which calculates the area of ​​a given type of polygon, i.e.

SELECT ST_Area (ST_Union (ST_Transform (ST_Intersection ((SELECT poly1.the_geom poly1 WHERE poly1.polygon_type = 'P'), poly2.the_geom), 3857))) AS area_of_P,

ST_Area (ST_Union (ST_Transform (ST_Intersection ((SELECT poly1.the_geom poly1 WHERE poly1.polygon_type = 'S'), poly2.the_geom), 3857))) AS area_of_S

FROM poly1, poly2

+5
1

.

SELECT ST_AREA(ST_Union(ST_Transform(ST_Intersection(poly1.the_geom,poly2.the_geom),3857)))

AS area_of_poly

FROM poly2

LEFT JOIN poly1 on st_intersects(poly1.the_geom,poly2.the_geom)

WHERE poly2.polygon_type IN ( 'P', 'S' )

Edit:

SELECT ST_AREA(ST_Union(ST_Transform(ST_Intersection(ps.the_geom,poly2.the_geom),3857))) AS area_of_P,
       ST_AREA(ST_Union(ST_Transform(ST_Intersection(ss.the_geom,poly2.the_geom),3857))) AS area_of_S
FROM poly2
JOIN poly1 ps ON poly2.polygon_type = 'P' AND st_intersects(ps.the_geom,poly2.the_geom)
JOIN poly1 ss ON poly2.polygon_type = 'S' AND st_intersects(ss.the_geom,poly2.the_geom)
+6

More articles:


All Articles