CODEWITHSUNDEEP
postgresqlpostgis
Frank Jimenez
Frank Jimenez

Reputation: 369

Results to two decimals using ST_Area

I have performed ST_Area on a shapefile but the resulting numbers are VERY long. Need to reduce them to two decimals. This is the code so far:

SELECT mtn_name, ST_Area(geom) / 1000000 AS km2 FROM mountain ORDER BY 2 DESC;

This is what I get:

             mtn_name                                  KM2
        character varying                        double precision
1   Monte del Pueblo de Jerez del Marquesado    6.9435657067528e-9
2   Monte de La Peza                            6.113288075418532e-9

I tried ROUND() but it brings KM to 0.00

Upvotes: 0

Views: 739

Answers (1)

S-Man
S-Man

Reputation: 23756

Since it is not simply possible to round a decimal value (Decimal Precision problem) you will not get a double value which is exactly 6.94e-9. It would be something like 6.9400000001e-9 after rounding.

You can do:

demos:db<>fiddle

  1. If the exponent is always the same (in your example it is always e-9) you can round with a fixed value. With double values, this results in the problem described above.

    SELECT
    round(area * 10e8 * 100) / 100 / 10e8
FROM area_result

  2. To avoid these precision problems, you can use numeric type

    SELECT
    round(area * 10e8 * 100)::numeric / 100 / 10e8
FROM area_result

If you have different exponents, you have to calculate the multiplicator first. According to this solution you can do:

  1. For double output

    SELECT
    round(area / mul * 100) * mul / 100
FROM (
    SELECT
        area,
        pow(10, floor(log10(area))) as mul
    FROM area_result
) s

  2. For numeric output

    SELECT
    round((area / mul) * 100)::numeric * mul / 100
FROM (
    SELECT
        area,
        pow(10, floor(log10(area)))::numeric as mul
    FROM area_result
) s

However, your exponential result is just a view of the values. This can vary from database tool to database tool. Internally they are not stored as the view. So, if you fetch these values, you will, in fact, get a value like 0.00000000694 and not 6.94e-9, which is just a textual representation.

  1. If you want to ensure to get exactly this textual representation, you can use number formatting to_char() for this, which, of course, returns a type text, not a number anymore:

    SELECT
    to_char(area, '9.99EEEE')
FROM area_result

Upvotes: 2

Related Questions