Why I'm getting wrong values when using ST_Area

Question

I Have created a table with the following commands:

  CREATE TABLE example (
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),
    AGE INT,
    SEX CHAR(6),
    INCOME FLOAT,
    LOCATION geometry)

And inserted values with the following command:

INSERT INTO example (FIRST_NAME,LAST_NAME, AGE,SEX,INCOME,LOCATION)
            VALUES ("b", "b", 30, "M", 500,  POLYGON((10 10, 10 20, 30 20, 30 30, 10 10)))

When I try to display the content with the area of the polygon, I'm getting wrong size:

SELECT FIRST_NAME, ST_Area(LOCATION) AS Area FROM example

I'm getting 0 (and I expect to get 200 (10*20))

What am I doing wrong, and how can I fix it ?

Answer 1

Could it be that your polygon is not valid? It is crossing itself:

POLYGON((10 10, 10 20, 30 20, 30 30, 10 10))

The ST_Area of such polygon will inevitably return zero:

SELECT 
 ST_Area('POLYGON((10 10, 20 10,20 30,30 30, 10 10))')

 st_area 
---------
       0
(1 Zeile)

On a first glance it seems you set the latitude value of the fourth coordinate pair wrong. It should be 30 10, not 30 30. Doing so you will get the 200 output you wished:

POLYGON((10 10, 10 20, 30 20, 30 10, 10 10))

SELECT 
 ST_Area('POLYGON((10 10, 10 20, 30 20, 30 10, 10 10))')

 st_area 
---------
     200
(1 Zeile)