How to make a generic query to get a point from a Postgis geometry type-CodePudding

I have a geometry field in one of the tables in my PostgreSQL database with the Postgis extension, this field is valued with the following possibilities, POINT, LINESTRING and POLYGON. I wonder if it is possible to set up a query so that regardless of the point format I can retrieve a POINT contained in geometry to be used in a WHERE clause, so that I can retrieve a tuple that has a point close to the searched one.

DDL:

CREATE TABLE public.contribution (
    id serial4 NOT NULL,
    occurrence timestamp(6) NULL,
    risk_damage bool NOT NULL DEFAULT false,
    victims bool NOT NULL DEFAULT false,
    published varchar(1) NOT NULL DEFAULT 'P'::character varying,
    "desc" varchar(500) NULL,
    "local" geometry NOT NULL,
    id_category int4 NOT NULL,
    id_collaborator int4 NULL,
    id_manager int4 NULL,
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT contribution_pkey PRIMARY KEY (id)
);

"local" column supports POINT, POLYGON and LINESTRING, I would like to make a query that given a coordinate's X and Y value returns tuples that have the coordinate in question, for example:

Select * from contribution where 10.0000 < local.x < 12000 and 20.0000 < local.y < 22.0000

In this situation I need to know if any point of the "local" geometry is in the above range and this is what I need.

CodePudding user response：

Instead of using a range on X and Y, turn it into a polygon and check if this box intersects with the stored geometries:

Select * 
from contribution 
where st_intersects(local,
  ST_SetSRID(
   ST_MakeBox2D(
    ST_Point(10, 20),
    ST_Point(12 ,22)),4326);

And if you were interested in a distance of 1 around 11;21, you could use st_dwithin instead:

Select * 
from contribution 
where st_dwithin(local,
  ST_SetSRID(
   ST_Point(12, 22),
  ,4326),
  1);