Skip to main content
Geographic Information Systems

Return to Answer

added 18 characters in body
Source Link
Evan Carroll
Evan Carroll
  • 7.1k
  • 2
  • 33
  • 62
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)'),
  AS t(multipoint)
CROSS JOIN ST_GeomFromText('POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10))')
  )
) AS gt(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)

SELECT ST_AsText(polygeom), ST_AsText(dump.geom), ST_Contains(polygeom, dump.geom)
FROM ST_GeomFromText('MULTIPOINT(0 0,5 5,100 100,-50 -50)')
  AS t(multipoint)
CROSS JOIN ST_GeomFromText('POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10))')
  AS g(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)

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)
added 825 characters in body
Source Link
Evan Carroll
Evan Carroll
  • 7.1k
  • 2
  • 33
  • 62
SELECT ST_AsText(polygeom), ST_AsText(dump.geom), ST_Contains(polygeom, dump.geom)
FROM ST_GeomFromText('MULTIPOINT(0 0,5 5,100 100,-50 -50)')
  AS t(multipoint)
CROSS JOIN ST_GeomFromText('POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10))')
  AS g(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)

SELECT ST_AsText(polygeom), ST_AsText(dump.geom), ST_Contains(polygeom, dump.geom)
FROM ST_GeomFromText('MULTIPOINT(0 0,5 5,100 100,-50 -50)')
  AS t(multipoint)
CROSS JOIN ST_GeomFromText('POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10))')
  AS g(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)
Source Link
Evan Carroll
Evan Carroll
  • 7.1k
  • 2
  • 33
  • 62

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.