how to select a specific region from within a multipoint plane in postgis?

Question

The following is some test data with x y and z values ( supposed to be representing image pixel positions in the physical world, but this is a small example to work with ).

-- create a table wtih geom as the geometry type column    
CREATE TABLE spatial_table (geom geometry);
     -- insert 3d points
    INSERT INTO spatial_table VALUES ('MULTIPOINT(1 0 800, 2 3 152, 1 2 300, 1 5 234234, 5 3 123123)')

Now, I am able to extract all of the points from this using the following query:

      SELECT * from st_x(st_geomfromewkt(SELECT ST_AsText( (ST_Dump(geom)).geom)
FROM spatial_table))

But lets say I want a specific region, where I know the x and y bounds for the region. How does one do this?

Answer 1

Make use of st_makeenvelope(xmin, ymin, xmax, ymax, srid)

For the following example:

SQL-query:

select p.id
from (values (st_geomfromtext('Point (34.5430 48.3706)'),'C'),
(st_geomfromtext('Point (34.0136 48.4661)'),'B'),
(st_geomfromtext('Point (33.6983 48.2173)'),'A'),
(st_geomfromtext('Point (34.0628 48.1450)'),'G'),
(st_geomfromtext('Point (33.7562 47.8586)'),'F'),
(st_geomfromtext('Point (34.0686 47.6388)'),'E'),
(st_geomfromtext('Point (34.4562 47.9599)'),'D'),
(st_geomfromtext('Point (34.1235 47.8528)'),'H')) p(geom, id)
where st_within(p.geom, st_makeenvelope(33.9327, 47.7892, 34.6645, 48.2434))

Returns G, D, H