Count multipoints within polygon

Question

It seems I can't count the number of individual points contained in multipoints geometries with

CREATE TABLE count_table
AS 
  SELECT poly_table.id,
    count(multipoint_table.geom) AS count 
  FROM poly_table
  LEFT JOIN multipoint_table
    ON ST_Contains(poly_table.geom, multipoint_table.geom) 
  GROUP BY poly_table.id
;

but I will only count the number of multipoint geometries.

Should I ST_dump?

Answer 1

I can't count the number of individual points contained in multipoints geometries

SELECT ST_AsText(x), ST_NumGeometries(x)
FROM ST_GeomFromText('MULTIPOINT(0 0,1 1)')
  AS t(x);
      st_astext      | st_numgeometries 
---------------------+------------------
 MULTIPOINT(0 0,1 1) |                2
(1 row)

---

Count multipoints within polygon

Polygons aren't constructed with MULTIPOINTS. They're constructed with Multiple-POINT()

SELECT ST_AsText(x), ST_NPoints(x)
FROM ST_GeomFromText('POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10))')
 AS t(x);
                   st_astext                    | st_npoints 
------------------------------------------------+------------
 POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10)) |          5
(1 row)

If you mean counting how many points of a multipoint are in a polygon, you need to decompose it first. If a polygon has one point of a multipoint the whole multipoint is contained.

SELECT ST_AsText(polygeom), ST_AsText(dump.geom), ST_Contains(polygeom, dump.geom)
FROM ( VALUES
  (
    ST_GeomFromText('MULTIPOINT(0 0,5 5,100 100,-50 -50)'),
    ST_GeomFromText('POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10))')
  )
) AS t(multipoint, polygeom)
CROSS JOIN LATERAL ST_Dump(multipoint)
  AS dump;
                   st_astext                    |   st_astext    | st_contains 
------------------------------------------------+----------------+-------------
 POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10)) | POINT(0 0)     | t
 POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10)) | POINT(5 5)     | t
 POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10)) | POINT(100 100) | f
 POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10)) | POINT(-50 -50) | f
(4 rows)