Postgis Point in Polygon

I am starting to learn postgis since I need to do certain calculations at my current job and the first thing I need is to calculate some things with points within a polygon. Rather than doing it directly on the real data I will develop my skills using a dummy data set. The reason? Simple I can control how much data I have and can know the answer to compare my calculations.

So the first thing I am after is knowing the number of points that are within each polygon. I went ahead and made a table for the polygons
name:

box

columns:

id – integer

name – varchar

geom – geometry(polygon,4326)


-- Table: box

CREATE TABLE box
(
id serial NOT NULL,
name character varying NOT NULL,
geom geometry(Polygon,4326),
CONSTRAINT box_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE box
OWNER TO postgres;


I also made a point table

name:

obstacle

columns:

id  – integer

elevation – integer

geom – geometry(point,4326)

-- Table: obstacle

CREATE TABLE obstacle
(
id serial NOT NULL,
elevation integer NOT NULL,
geom geometry(Point,4326),
CONSTRAINT obstacle_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE obstacle
OWNER TO postgres;

This is what I see once I add it to QGIS and style it a bit

point-in-polygon

From simple inspection I get that for each box I get the following count

• box_1 = 4
• box_2 = 4
• box_3 = 4
• box_4 = 3

Easy enough right? Well Imagine doing this for tens or hundreds of polygons and points…..this is where SQL comes to the rescue

select box.name,count(obstacle.id) as total_obstacles
from box left join obstacle
on st_contains(box.geom,obstacle.geom)
group by box.name
order by box.name asc


If you need more info on Left Join, but basically it returns the row of table 1 (box) with matches from table 2 (obstacle)

And here is the result in pgadmin