How to create a function for finding the largest of 2 polygons by area in PostGIS?

I tried my level in creating the function but it shows error while running.

I have enclosed the code here :

create function largest_poly (geom1, geom2)
returns geometry as \$largest_poly\$
begin
from plpygis import Geometry
ST_Area(geom1 ST_Polygon),
ST_Area(geom2 ST_Polygon)
poly largest_poly=Null
for
(poly ST_Area: geom){
{If (largest_poly==Null)|| ST_Area.geom() > largest_poly.ST_Area())
largest_poly=ST_Area
}
return largest_poly;
}
end;
\$largest_poly\$ language plpygis;
• Why a function, when it can be done with straight SQL – John Powell May 16 at 14:31
• yes, Mr. J. Monticolo gave the answer and it was working perfectly with sql language. – Karthik May 20 at 13:17

From the examples in the plpygis documentation, it gives :

Some functions that analyze or manipulate geometries are possible in SQL but are easier to model in a procedural language. The following example will use Shapely to find the largest component polygon of a multipolygon.

CREATE OR REPLACE FUNCTION largest_poly(geom geometry)
RETURNS geometry
AS \$\$
from plpygis import Geometry
polygons = Geometry(geom)
if polygons.type == 'Polygon':
return polygons
elif polygons.type == 'MultiPolygon':
largest = max(polygons.shapely, key=lambda polygon: polygon.area)
return Geometry.from_shapely(largest)
else:
return None
\$\$ LANGUAGE plpythonu;

A pure PL/pgSQL function will have significantly better performance:

CREATE OR REPLACE FUNCTION largest_poly_fast(polygons geometry)
RETURNS geometry
AS \$\$
WITH geoms AS (
SELECT (ST_Dump(polygons)).geom AS geom
)
SELECT geom
FROM geoms
ORDER BY ST_Area(geom) DESC LIMIT 1;
\$\$ LANGUAGE sql;

From the second code, we can modify it a little bit :

CREATE OR REPLACE FUNCTION largest_poly(geom1 geometry, geom2 geometry)
RETURNS geometry
AS \$\$
WITH geoms AS (
SELECT geom1 AS geom
UNION ALL SELECT geom2
)
SELECT geom
FROM geoms
ORDER BY ST_Area(geom) DESC LIMIT 1;
\$\$ LANGUAGE sql;
• Thank you for your response sir. Then there is no use of case statement in finding this answer? – Karthik May 16 at 13:24
• There's no reason to put a Python function in your answer. It looks like you're recommending using it. – jpmc26 May 16 at 13:43
• Yes, Thank you so much. – Karthik May 16 at 14:02
• In the question, the given code call a python module and I paste the Python documentation example for show that is not mandatory and less powerful to use Python here. But, the code gives the Python working syntax. I just don't know if using Python in the question was a choice or a lack of solutions. – J. Monticolo May 16 at 14:44
• I just want the solution from plpgsql which was given by you. I am wondering that how the subquery gives the answer and it seems to be correct. But while querying what will the solution for the first and second codes whether I need to give name of the polygon or Id. May I have an example. This is my example of querying " select largest_poly(geo_gem_01, geo_gem_05);" where geo_gem_01 and geo_gem_05 are polygons. – Karthik May 16 at 15:48