1

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?

  • Any other details needed? Francesco? – Evan Carroll May 16 '17 at 3:11
2

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)
|improve this answer|||||
  • Why to use CROSS JOIN LATERAL instead of a simple LEFT JOIN? Also, does ST_Dump takes a table as argument and produces by default a geom column? – Francesco May 11 '17 at 23:20
  • @Francesco CROSS JOIN LATERAL is the PostgreSQL equiv of CROSS APPLY and the like, ST_Dump() is a SRF and multipoint comes from the past part of the query. – Evan Carroll May 12 '17 at 3:04

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.