# Calculating number of points in each polygon

by Eugene   Last Updated May 13, 2019 08:26 AM

I have 2 tables,

points: which has two columns latitude(float) and longitude(float).

polygon: which has two columns polygon_id(int) and geom(the shape of the polygon)

I want to print out number of points that are contained in each polygon_id.

I could figure out how to get the polygon_id that contain one point but couldn't progress further.

``````SELECT polygon.id
FROM polygon
WHERE st_contains(geom, ST_GeomFromText('POINT(120.32155 -33.843322)', 4326)) = true
``````

I wanted to use latitude and longitude from the points table, however,

``````ST_GeomFromText('POINT(points.latitude points.longitude)
``````

Did not work.

The expected results are having polygon_id and the points that the polygon_id contains for each row. Since there are 100 polygons, I'm expecting 100 rows.

Tags :

#### Answers 1

What you're missing is `ST_MakePoint`.

Considering your table structure and the following samples, which consist of a polygon and two overlapping points ..

``````CREATE TABLE polygon (id int, geom geometry);
INSERT INTO polygon VALUES (1,'POLYGON((120.20 -33.75,120.43 -33.75,120.43 -33.88,120.20 -33.88,120.20 -33.75))'::geometry);

CREATE TABLE points (lon numeric, lat numeric);
INSERT INTO points VALUES (120.32,-33.84);
INSERT INTO points VALUES (120.39, -33.85);
`````` .. execute the following query:

``````SELECT polygon.id,COUNT(points.lon)
FROM polygon,points
WHERE ST_Contains(geom, ST_MakePoint(lon,lat))
GROUP BY polygon.id

id | count
----+-------
1 |     2
(1 row)
``````

I would recommend you to not store latitude and longitude in different columns. Storing points as `geometry` or `geography` is always the best choice in PostgreSQL/PostGIS.

## Related Questions

Updated October 29, 2018 14:26 PM

Updated June 28, 2017 11:26 AM

Updated July 04, 2017 14:26 PM

Updated August 28, 2018 00:26 AM

Updated May 20, 2015 03:11 AM