0

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
3

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

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.