CODEWITHSUNDEEP
postgresqlpostgis
civarchive
civarchive

Reputation: 67

Points in Polygon, multiple polygons

I have this query working where I populated the prem_council column in my dst db by matching points with polygons in the src. But, I could only do it manually by stepping through each district 1-15 manually. 

    UPDATE poursafe.ca_licenses dst
SET prem_council = '1'
FROM ca_la_la_areas_council_dist_2012 src
WHERE EXISTS (
        SELECT *
        FROM public.ca_la_la_areas_council_dist_2012 src
        WHERE ST_Within(dst.geom, src.geom )
        AND district = '1'
        );

I can't figure out how and wonder if you can tell me how I could automate the process so I don't have to do each district manually? The reason I ask is I have several other geographic tables and need to do a similar process. Thanks!

Upvotes: 0

Views: 332

Answers (1)

Barış Serkan AKIN
Barış Serkan AKIN

Reputation: 581

it is blackbox for me without table defination and geometry type of table but if you try to make relation between two tables(ca_licenses and ca_la_la_areas_council_dist_2012), you can simply set district's values to prem_council ;

update poursafe.ca_licenses dst
    set prem_council = district 
from ca_la_la_areas_council_dist_2012 src
where st_within(dst.geom,src.geom) and dst.geom&&src.geom --to accelerate query

Query doesn't solve your problem , pls add table's defination and update conditions to your question.

Upvotes: 1

Related Questions