2

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?

1
  • Is the city.geom in WGS84?
    – underdark
    Jul 25, 2015 at 9:08

1 Answer 1

3

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)

enter image description here

8
  • Thanks! This is exactly what I figured out but for some reason, the error I keep getting is that No function matches the given name and argument types. You might need to add explicit type casts. where the ST_MakePoint function is....thoughts?
    – Minh
    Jul 25, 2015 at 15:25
  • Can you post the CREATE TABLE sentence of pets. ST_MakePoint expects the parameters to be in double precision, you can cast it with:ST_MakePoint(lng::double precision, lat::double precision) Jul 25, 2015 at 16:55
  • I tested this query out select ST_setSRID(ST_MakePoint(lng::double precision, lat::double precision),4326) FROM pets; and got the following error ERROR: invalid input syntax for type double precision:
    – Minh
    Jul 25, 2015 at 19:02
  • Paste your CREATE TABLE pets sentence and a couple of INSERT INTO pets. It seems that your columns lat and lng doesn't have a correct format. Jul 25, 2015 at 19:09
  • CREATE TABLE pets ( lat double precision, lng double precision, dogs int, cats int);
    – Minh
    Jul 25, 2015 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.