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```

``` -- DROP 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`

``` -- DROP 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

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 