CODEWITHSUNDEEP
sqlpostgresqlgeospatialpostgisspatial-query
Aravinth
Aravinth

Reputation: 91

Number of population in an existing buffer in postgresql

I have a spatial table named Operation with points with 100m buffer for all the points, I also have another table with polygon shapefiles, which contains population

ew is the population in each polygon Population table

dbfiddle

Is it possible to add the population as a column into the Operation table by counting the population inside each buffer? As I am new to PostgreSQL I can't get any idea how to proceed with this.

ALTER TABLE public."Operation" ADD COLUMN population int;

UPDATE public."Operation" o 
SET population = (SELECT pop_per_sq count(*) FROM public."Restaurants" s
           WHERE ST_Contains(o.buffer,s.geom));

The result should be like a new column in the operation table with the population count.

Upvotes: 1

Views: 189

Answers (1)

Jim Jones
Jim Jones

Reputation: 19653

You were really close. The correct syntax for the aggregate function count() is count(column) not column count(*). But what you need is rather sum(), which follows the same syntax. In case there is no overlap between buffer and polygons the result will be null. Also, if you do not wish to have null values in your table, use coalesce to replace them values with 0. This should fix your query:

UPDATE public."Operation" o 
SET population = coalesce((SELECT sum(ew) FROM public."Population" s
                           WHERE ST_Contains(o.buffer,s.geom)),0);

Demo: db<>fiddle

Note: If you also want to count the polygons that partially overlap with the buffer, take a look at ST_Intersects. And keep in mind that if two (or more) buffers overlap and if their corresponding population geometries are inside both buffers, they will be calculated twice.

btw +1 for the nice fiddle, it is much easier to understand a question like this!

Upvotes: 1

Related Questions