Spatial Join to count points in polygons in lat/lon in PostGIS?

Question

I have a table, called pets, with lats and lng coordinates and variables and a table that was created by uploading a shapefile, called city, with columns left, right, top, bottom, lat, lng, and geom.

    lat    lng   cats  dogs
   -59.4   42.1   2     0
   -58.1   44.3   1     1
   -59.7   43.6   0     3 
   -59.8   42.0   2     1

I'm wondering how I would construct a query that will sum up the number of cats and dogs within each boundary. Usually I would do a spatial join in QGIS but my dataset is too large and it crashes before successfully joining the two.

I am pretty familiar with QGIS and Postgres, but very new to PostGIS. I've constructed a query but unsuccessful in getting the answer I needed:

select sum(pets.dogs)
from pets, city
WHERE ST_Within(pets.dogs, city.geom);

How do I do this in PostGIS?

Answer 1

Taking as hypothesis that:

• city.geom is in EPSG:4326
• lat, lng in pets table can also be taken as EPSG:4326
• That you want to sum the number of cats and dogs that are in each geometry of the table city
• That the geoms in table city do not overlap or if overlaps you don't care to count two times the pets
• city has a pk called gid
• You are not interested in the points outside the boundaries of the cities

The query that must be built should take the following into account:

Convert lat,lng coordinates into postgis geometries in 4326

SELECT ST_SetSRID(ST_MakePoint(lng, lat), 4326) FROM pets;

The you must build a query that sums the cats and dogs on each geom:

SELECT gid, sum(cats), sum(dogs) FROM city c, pets_with_geom p WHERE ST_Within(p.geom, c.geom) GROUP BY c.gid

To build the full query we use a CTE

WITH pets_with_geom AS(
  SELECT cats, dogs, ST_SetSRID(ST_MakePoint(lng, lat), 4326) as geom FROM pets
)
SELECT gid, sum(cats), sum(dogs) FROM city c, pets_with_geom p WHERE ST_Within(p.geom, c.geom) GROUP BY c.gid

Using this test data:

CREATE TABLE pets (
  gid SERIAL PRIMARY KEY,
  lat double precision,
  lng double precision,
  dogs int,
  cats int
);

INSERT INTO pets (lat, lng, dogs, cats) VALUES (0.647, -0.977, 1, 1);
INSERT INTO pets (lat, lng, dogs, cats) VALUES (0.673, -0.245, 4, 4);
INSERT INTO pets (lat, lng, dogs, cats) VALUES (0.520, -0.789, 2, 2);

CREATE TABLE city (
  gid SERIAL PRIMARY KEY,
  geom geometry(MULTIPOLYGON, 4326)
);

INSERT INTO city (gid, geom) VALUES (1, '0106000020E610000001000000010300000001000000050000008ACE1C2D1BE2F0BF64DF23728270E73F431C149D395AE6BF64DF23728270E73F1120C30FD548E6BFFE9B075EE536DD3F715074E668D9F0BF6294A978AE59DD3F8ACE1C2D1BE2F0BF64DF23728270E73F');
INSERT INTO city (gid, geom) VALUES (2, '0106000020E61000000100000001030000000100000005000000F05CB33A8C32B5BFC2C45A4F42EAE73F40B81BFB1E91B3BFA6C93BBE2E66DC3F189726EFF8BAD8BFB2EF113941B8DB3FB49E84D42F98D8BF2CD067A714B6E73FF05CB33A8C32B5BFC2C45A4F42EAE73F');
INSERT INTO city (gid, geom) VALUES (3, '0106000020E61000000100000001030000000100000005000000DC2985EDAD40DF3FF4C0ABDCA6FBE73F1A7EA846B208E03F42D199A36543DC3FD0B2210E8ACECC3F16E8B3530ADBDB3F200E8ACE1C2DCB3F8AB59E84D42FE83FDC2985EDAD40DF3FF4C0ABDCA6FBE73F');

You can get this results:

┌─────┬─────┬─────┐
│ gid │ sum │ sum │
├─────┼─────┼─────┤
│   2 │   4 │   4 │
│   1 │   3 │   3 │
└─────┴─────┴─────┘
(2 rows)