I've got

CREATE TABLE points1 (
    point1id varchar PK, 
    geom1 geom, 
    date1 date);

CREATE TABLE points2 (
    point2id varchar PK, 
    geom2 geom, 
    date2 date);

Both with indexes. I need to count points (also zeros) within a UTM grid and this seems to work fine for one table

SELECT utm.utmid, count(points1.geom1)  
FROM utm LEFT JOIN points1 
ON st_contains(utm.geom,points1.geom1) 
GROUP BY utm.utmid;

But I need to obtain only one table with something like

utmid / Date / # points1 / # points2

  • 1
    The ST_Contains operator should fail to match on points which are on the UTM boundary, and ST_Intersects will return both boundary grid cells. – Vince Jul 26 '19 at 12:55

You can have many joins, so this should just work:

  count(points1.geom1) AS points1,
  count(points2.geom1) AS points2 
FROM utm
LEFT JOIN points1 
  ON ST_Contains(utm.geom, points1.geom1) 
LEFT JOIN points2 
  ON ST_Contains(utm.geom, points2.geom1) 
GROUP BY utmid;

I highly recommend not naming fields in your tables after the table name. Always just use "id", "geom" and "date". This makes things much cleaner and easier to maintain in the long run.

  • Actually, they are not thus named. It was just for being clearer in the question. Anyway, what about the dates? How can they be included? – Jesús Pinilla Jul 26 '19 at 10:18
  • 1
    The JOINs join the columns, so you can add them to the SELECT now. Since you GROUP you have to either aggregate or group the dates. But that is another question. – bugmenot123 Jul 26 '19 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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