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,
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.
What you're missing is
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
geography is always the best choice in PostgreSQL/PostGIS.