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.



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);

enter image description here

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

Jim Jones
Jim Jones
May 12, 2019 13:17 PM

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