How to make query in postgis to get to know if square shape is intersected with circle or not postgress

How to make query in postgress with postgis to know if square shape is intersected with circle or not postgress

Solution

The function you're looking for is ST_Intersects. This function isn't constraint to any geometrical shape, so it is irrelevant if it is a rectangle, a circle or a hexagon. What matters is the area they cover. See this example:

Data Sample

 -- bbox
SRID=4326;POLYGON((-4.43 54.31,-4.39 54.31,-4.39 54.29,-4.43 54.29,-4.43 54.31))
 -- buffer
SRID=4326;POLYGON((-4.4245 54.2887,-4.424692147195968 54.286749096779836,-4.425261204674887 54.284873165676345,-4.426185303876974 54.2831442976698,-4.427428932188135 54.281628932188134,-4.428944297669804 54.280385303876976,-4.430673165676349 54.27946120467489,-4.432549096779838 54.27889214719597,-4.4345 54.2787,-4.436450903220162 54.27889214719597,-4.438326834323651 54.27946120467489,-4.440055702330196 54.280385303876976,-4.441571067811865 54.281628932188134,-4.442814696123025 54.2831442976698,-4.443738795325113 54.284873165676345,-4.444307852804032 54.286749096779836,-4.4445 54.2887,-4.444307852804032 54.29065090322016,-4.443738795325113 54.29252683432365,-4.442814696123025 54.2942557023302,-4.441571067811865 54.29577106781186,-4.440055702330196 54.29701469612302,-4.438326834323651 54.29793879532511,-4.436450903220162 54.29850785280403,-4.4345 54.2987,-4.432549096779838 54.29850785280403,-4.430673165676349 54.29793879532511,-4.428944297669804 54.29701469612302,-4.427428932188135 54.29577106781186,-4.426185303876974 54.2942557023302,-4.425261204674887 54.29252683432365,-4.424692147195968 54.29065090322016,-4.4245 54.2887))

The following CTE will check if the given geometries intersect (ST_Intersects) and will return a geometry with the overlapping area (ST_Intersection):

WITH bbox (geom) AS (
  VALUES ('SRID=4326;POLYGON((-4.43 54.31,-4.39 54.31,-4.39 54.29,-4.43 54.29,-4.43 54.31))'::GEOMETRY)
), buffer (geom) AS (
  VALUES ('SRID=4326;POLYGON((-4.4245 54.2887,-4.424692147195968 54.286749096779836,-4.425261204674887 54.284873165676345,-4.426185303876974 54.2831442976698,-4.427428932188135 54.281628932188134,-4.428944297669804 54.280385303876976,-4.430673165676349 54.27946120467489,-4.432549096779838 54.27889214719597,-4.4345 54.2787,-4.436450903220162 54.27889214719597,-4.438326834323651 54.27946120467489,-4.440055702330196 54.280385303876976,-4.441571067811865 54.281628932188134,-4.442814696123025 54.2831442976698,-4.443738795325113 54.284873165676345,-4.444307852804032 54.286749096779836,-4.4445 54.2887,-4.444307852804032 54.29065090322016,-4.443738795325113 54.29252683432365,-4.442814696123025 54.2942557023302,-4.441571067811865 54.29577106781186,-4.440055702330196 54.29701469612302,-4.438326834323651 54.29793879532511,-4.436450903220162 54.29850785280403,-4.4345 54.2987,-4.432549096779838 54.29850785280403,-4.430673165676349 54.29793879532511,-4.428944297669804 54.29701469612302,-4.427428932188135 54.29577106781186,-4.426185303876974 54.2942557023302,-4.425261204674887 54.29252683432365,-4.424692147195968 54.29065090322016,-4.4245 54.2887))'::GEOMETRY)
)
SELECT ST_Intersection(bbox.geom,buffer.geom) FROM bbox,buffer
WHERE ST_Intersects(bbox.geom,buffer.geom);

                                                                                                                                                          st_intersection                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 0103000020E610000001000000090000006EDC4EB1D1B211C085EB51B81E254B40B81E85EB51B811C085EB51B81E254B40B81E85EB51B811C0CA336B1117264B40A330892C3DB711C0CF23DB9304264B40EF7214EEAFB511C06FAD8BD3DB254B40ECBF98EB69B411C0B915BD2BAA254B40455859AC77B311C0AE4FF18471254B407E4C8C7FE2B211C015F17D0C34254B406EDC4EB1D1B211C085EB51B81E254B40
(1 Zeile)