CODEWITHSUNDEEP
postgresqlgeometrypostgis
user3445104
user3445104

Reputation: 29

How can I find the area from polygon values in postgres?

I want to find the area from the polygon including latitude and longitude data

select ST_Area(l.polygon) from  dwh_il_fo.v1_dim_location l
where location_id  = '4136'
limit 10

The polygon value in this case is, 

MULTIPOLYGON (((103.867936362042 1.34178614086727, 103.867778465463 1.34071624615614, 103.867304775725 1.34017252899258, 103.866497748765 1.33999713633343, 103.865234576132 1.34047069648432, 103.865234576132 1.3411547276517, 103.86567317774 1.3419439941457, 103.865918794641 1.34124242394138, 103.866778453795 1.34103195284086, 103.867936362042 1.34178614086727)))

But the result return to 0.000002908012571383107 but I wanted to get the geometry value.I suppose I don't know the way the query consider the value as real world latitude and longitude data.

Upvotes: 1

Views: 3477

Answers (1)

JGH
JGH

Reputation: 17836

ST_AREA on a geometry returns the area in the coordinate system unit. In you case, it is in degrees... and this unit is meaningless with respect to area (1 degree of latitude is not the same length - in meters - as 1 degree of longitude).

To get an area in m2 (or km2, or miles2 etc), you would need to either transform your geometry to a coordinate system whose unit is in meters, or you would need to use the geography datatype.

WITH a as (SELECT ST_GEOMFROMTEXT('MULTIPOLYGON(((103.867936362042 1.34178614086727, 103.867778465463 1.34071624615614, 103.867304775725 1.34017252899258, 103.866497748765 1.33999713633343, 103.865234576132 1.34047069648432, 103.865234576132 1.3411547276517, 103.86567317774 1.3419439941457, 103.865918794641 1.34124242394138, 103.866778453795 1.34103195284086, 103.867936362042 1.34178614086727)))') geom)
SELECT st_area(geom::geography) m2 from a;
        m2
------------------
 35785.3981593922
(1 row)

The original query becomes

select ST_Area(l.polygon::geography) 
from  dwh_il_fo.v1_dim_location l
where location_id  = '4136'
limit 10;

Upvotes: 2

Related Questions