Optimizing PostGIS query

Question

I've a query that has to be run against 1 million records. The query finds the intersection between two polygons and the area of the result of the intersection. Here's the query:

SELECT 
    ST_Area(
        ST_Collect(
            ST_Intersection(poly1.the_geog::geometry, poly2.the_geog::geometry)
        )::geography
    ),
    ST_AsGeoJSON(
       ST_Multi(
           ST_Collect(
               ST_Intersection(poly1.the_geog::geometry, poly2.the_geog::geometry)
           )
       )
    ) 
FROM poly1, poly2 
WHERE poly1.id = 1 AND
   ST_Intersects(poly1.the_geog::geometry,poly2.the_geog::geometry)

I'm trying to figure out if I can use the result of ST_Intersection query which is common for ST_AsGeoJSON and for calculating the area using ST_Area. Is it possible to do it ?

Answer 1

Use nested SELECT statement. In the inner SELECT run the ST_Intersection, then pass the result to the outer SELECT to ST_Area and ST_AsGeoJSON. Be sure to have indices built on your table as well.