2

I have a query where I am calculating acreage of two intersecting tables and what percentage that new acreage is compared to the old acreage.here is the error

ERROR:  division by zero
********** Error **********

ERROR: division by zero
SQL state: 22012

query

select p.objectid,acreage old_acreage,p.shape,sum(st_area(st_intersection(p.shape,lulc.shape))/43560) as new_acreage,
    sum(st_area(st_intersection(p.shape,lulc.shape))/43560)/acreage as agr_use_percentage

from base_layers.modiv_parcels_2015 p join base_layers.lulc_huc_hl_clip lulc on st_intersects(p.shape,lulc.shape) 

where lu12 in(2100,2200,2300,2400,2140,2150) group by p.objectid,acreage,p.shape

what can be done to circumvent this error?

7

You need to guard against dividing by zero, as the exception suggests. You are dividing by the field acreage, so if that is ever 0 then the query will fail. Add this to your where clause:

AND acreage > 0
  • okay great that worked..very strange it threw this error here when I have been using that parcel layer for many other calculations.. – ziggy Aug 23 '17 at 16:17
3

Another option to avoid the 'Div by 0' errors would be to wrap the expression with a case statement.

select 
    p.objectid,
    acreage old_acreage,
    p.shape,
    sum(st_area(st_intersection(p.shape,lulc.shape))/43560) as new_acreage,
    case 
        when acreage = 0 then -1 -- some value to indicate div by 0 result
        else sum(st_area(st_intersection(p.shape,lulc.shape))/43560)/acreage
    end as agr_use_percentage

from base_layers.modiv_parcels_2015 p 
    join base_layers.lulc_huc_hl_clip lulc on st_intersects(p.shape,lulc.shape) 

where lu12 in(2100,2200,2300,2400,2140,2150) group by p.objectid,acreage,p.shape;

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.