ST_Area does not exist Heroku Postgresql Postgis-CodePudding

I have a Postgres extended with Postgis version 2.5 database in Heroku.

I want to use the function:

ST_Area( a_polygon )

Specifically I want a generated column in my table: alter table buildings add building_area float generated always as ( st_area( base_polygon ) ) stored;

Where base_polygon is of type polygon.

However, I am getting this error:

ERROR: function st_area(polygon) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Aren't these commands supposed to be available after I run CREATE EXTENSION postgis?

Or, is there something else I have to do?

CodePudding user response：

It seems your polygon column data type is postgre base built in polygon.

ST_Area expects postgis geometry type as a parameter.

As in this example from docs https://postgis.net/docs/ST_Area.html

select ST_Area(geom) sqft,
ST_Area(ST_Transform(geom, 26986)) As sqm
from (
     select
         'SRID=2249;POLYGON((743238 2967416,743238 2967450,
         743265 2967450,743265.625 2967416,743238 2967416))' :: geometry 
      geom
 ) subquery;

Check if this example works, it means that ST_Area function exists.

You can add a column with postgis geometry type. https://postgis.net/docs/AddGeometryColumn.html

SELECT AddGeometryColumn ('my_schema','my_spatial_table','geom',4326,'POLYGON',2);

Then convert your polygons into postgis format, by postgis functions.

For example https://postgis.net/docs/ST_MakePolygon.html

SELECT ST_MakePolygon( ST_GeomFromText('LINESTRING(75 29,77 29,77 29, 75 29)'));