0

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 ?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.