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.

For example in the screenshot below: enter image description here There are 5 grids in this postcode(postcode containing centroids). When I execute the above query, I get the below result

enter image description here

Below is my query:

   ST_Union(x.geom), x.vrgid, x.weights
           postcodes.id as postcodeid, 
           fishnet.gid as vrgid,
           fishnet.geom as geom, 
           CASE WHEN st_intersects(centroids.geom, urban.geom) and 
            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 
            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.

  • I'm not really sure what you are trying to achieve, but your query looks like you may need to make the JOINs LEFT OUTER JOINs. I think a little diagram/map or something demonstrating the results you are trying to achieve from the data you have will help you to get an answer. – MickyT May 10 '18 at 20:52
  • Is there any chance you could format the query a bit. It would be much easier to read if indented properly, with keywords, SELECT, FROM, etc in caps. This is a really nice SQL style guide. I forced myself to start writing my own SQL like this and have found it really helps when going back to long queries after a period of time. – John Powell May 11 '18 at 10:47
  • hm, I have the feeling it might be easier to build a (new) query based on your needs; could you maybe just describe your goal a bit more, independent of your query? and some questions: the darker cells are urban areas I take it? would it be of help to find the actual percentage of total cell area to the postcode area to weigh over each quadgrid cell? you base your calculations on intersection between the centroids and the urban cells...that seems rather random? do those centroids have an id reference to their cells or do you need to relate both by containment? – ThingumaBob May 12 '18 at 7:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.