Area calculation from given lat, lng with SQL

area-calculation-playground.sql

drop table my_polygon;
create table my_polygon (
    id integer primary key autoincrement,
    polygon_set char(50),
    vertex_order integer not null,
    lat real not null,
    lng real not null
);
insert into my_polygon (polygon_set, vertex_order, lat, lng)
values ('set_1', 1, 1, 8),
    ('set_1', 2, 7, 9),
    ('set_1', 3, 5, 1),
    ('set_1', 4, 4, 4),
    ('set_1', 5, 2, 6),
    ('set_1', 6, 1, 6),
    ('set_2', 1, 18, 26),
    ('set_2', 2, 28, 22),
    ('set_2', 3, 30, 16),
    ('set_2', 4, 26, 12),
    ('set_2', 5, 28, 6),
    ('set_2', 6, 16, 14);
with move_first_to_last as (
    select m.polygon_set,
        mvo.max_vertex_order + 1 as vertex_order,
        lat,
        lng
    from my_polygon m
        join (
            select polygon_set,
                max(vertex_order) as max_vertex_order
            from my_polygon
            where polygon_set in ('set_1', 'set_2')
            group by polygon_set
        ) mvo on m.polygon_set = mvo.polygon_set
    where m.vertex_order = 1
        and m.polygon_set in ('set_1', 'set_2')
),
my_polygon_shifted as (
    select polygon_set,
        vertex_order - 1 as vertex_order,
        lat,
        lng
    from (
            select polygon_set,
                vertex_order,
                lat,
                lng
            from move_first_to_last
            union
            select polygon_set,
                vertex_order,
                lat,
                lng
            from my_polygon
            where vertex_order != 1
                and polygon_set in ('set_1', 'set_2')
        )
)
select m.polygon_set,
    sum(m.lng * s.lat - m.lat * s.lng) / 2 as area
from (
        select polygon_set,
            vertex_order,
            lat,
            lng
        from my_polygon
        where polygon_set in ('set_1', 'set_2')
    ) m
    join my_polygon_shifted s on m.polygon_set = s.polygon_set
    and m.vertex_order = s.vertex_order
group by m.polygon_set;

source.md

https://www.mathopenref.com/coordpolygonarea2.html