Sum of points attributes inside intersection of two polygons

Question

I have a three different tables:

1. Polygons1

Name1	Geom
Name...	POLYGON..
Cell 3	Cell 4

2. Polygons2

Name2	Geom
Name...	POLYGON..
Cell 3	Cell 4

3. Points

ID	Num	Geom
ID...	54	POINT...
Cell 3	33

I want to find where are polygons interesected and inside that area of intersection to sum points attribute - Num.

I tried this query:

SELECT sum(Num) as total, Polygons1.Name1
from Points, 
     Polygons1, 
     Polygons2 
where ST_intersects(Polygons1.geom , Polygons2.geom)
GROUP BY Polygons1.Name1

This query returns some really big sum numbers that are not correct. Please help.

Answer 1

1. Unless you double-quoted relation names back when you created them, they are actually lowercase. Make sure you know how you name things. Some tools auto-quote, some tools don't.
2. You specified how to pair up polygons. You didn't specify how to match points to those pairs of polygons - in this situation postgres assumes you want to link all points to each pair of polygons. You're likely seeing a repeated total sum of all Points.Num listed next to each Polygons1.Name1 that found a matching geom in Polygons2. See this demo.
3. You used implicit joins. It might be easier to join your tables explicitly.

SELECT sum(p.Num) as total, 
       Polygons1.Name1
from      Polygons1 as plg1 
     join Polygons2 as plg2
     on ST_Intersects(plg1.geom, plg2.geom)
     join Points    as p
     on ST_Within(p.geom, ST_Intersection(plg1.geom, plg2.geom) )
GROUP BY Polygons1.Name1;

Make sure you're clear on what you mean by inside and intersection:

• If the polygons overlap sharing an area the shape of a smaller polygon and your point is in there, it's clear it's inside their intersection.
• If the polygons only share a side, their intersection is a line. Is a point on that line inside it, according to your criteria?
• If two squares only touch their corners, sharing a point, their intersection will be that point. If you have a point in that exact location, do you consider your point to be inside that other point, their intersection?