I'm trying to assign weights for the grids in postcode based on intersection with urban layer for each postcode (sum of weights must be 1). To get the count of intersection between grid and urban layer I have created a temporary table (vrg.urban_nonurban_count_new ) which gives me postcodeid,urbancells count, non urban cells count.
Below is my query:
SELECT ST_Union(x.geom), x.vrgid, x.weights FROM (SELECT postcodes.id as postcodeid, fishnet.gid as vrgid, fishnet.geom as geom, CASE WHEN st_intersects(centroids.geom, urban.geom) and counts.nonurbancells!=0 THEN 0.95::numeric/counts.urbancells WHEN st_intersects(centroids.geom, urban.geom) and counts.nonurbancells = 0 THEN 1.00::numeric/counts.urbancells WHEN st_disjoint(centroids.geom, urban.geom) and counts.nonurbancells!=0 THEN 0.05::numeric/counts.nonurbancells END AS Weights FROM vrg.urban_nonurban_count_new as counts LEFT OUTER JOIN vrg.gfk_2016_id_5_digit_pcd_areas2013_projected as postcodes ON postcodes.id=counts.postid LEFT OUTER JOIN vrg.rdsid_86_quadgrid_centroids as centroids ON st_contains (postcodes.geom,centroids.geom) LEFT OUTER JOIN vrg.rdsid86_quadgrid AS fishnet ON fishnet.gid=centroids.gid LEFT OUTER JOIN vrg.rdsid86_katrisk_poly_projected as urban ON st_intersects(urban.geom,fishnet.geom) WHERE postcodes.id='42395') as x GROUP BY x.vrgid, x.weights
Here I'm supposed to get 0.0125 for 4 cells and 0.95 for 1 cell. But, 2 weights have been assigned to single cell i.e 0.95 and 0.0125 for vrgid 7192. I'm not able to figure out the problem.