Is there a more efficient way to return the record of maximum spatial intersection in SQL?

Question

I have two tables in SQL. The first are project polygons. The second is any boundary I want to apply to the project polygons based on maximum spatial intersection.

For example, if project 1 is overlapping 2 states, I want to return the state name that project 1 has the most area in.

I've done so with the query below. PrjID is the projects table and STATE is the state table. I want to have statename returned based on maximum intersection between each PrjID record and STATE record.

SELECT *
FROM
(SELECT a.PROJECT_DELIVERY_ID, b.statename, a.Shape.STIntersection(b.Shape).STArea() AS Area
    FROM PrjID a
        INNER JOIN STATE b
            ON a.Shape.STIntersects(b.Shape) = 1) as c
INNER JOIN
(SELECT a.PROJECT_DELIVERY_ID, MAX(a.Shape.STIntersection(b.Shape).STArea()) AS Area
    FROM PrjID a
        INNER JOIN STATE b
            ON a.Shape.STIntersects(b.Shape) = 1
    GROUP BY a.PROJECT_DELIVERY_ID) as d
ON c.PROJECT_DELIVERY_ID = d.PROJECT_DELIVERY_ID
    AND c.Area = d.Area

I feel like I'm running the same query twice and joining them. Is there a more efficient way to do this?

Thanks

Answer 1

if I got it correct, you want to get max area row. you can use ROW_NUMBER().

SELECT * FROM (
    SELECT a.PROJECT_DELIVERY_ID, b.statename, a.Shape.STIntersection(b.Shape).STArea() AS Area, 
        ROW_NUMBER() OVER(PARTITION BY a.PROJECT_DELIVERY_ID ORDER BY a.Shape.STIntersection(b.Shape).STArea() DESC) RN
        FROM PrjID a
            INNER JOIN STATE b
                ON a.Shape.STIntersects(b.Shape) = 1 ) AS T
WHERE RN = 1