Storing Circles and run efficient query using PostGIS

Question

In my database I have two tables where:

• Table Pin contains a column of type point representing lat and long.
• Table Fence contains (1) a column of type double representing a radius (2) and a foreign key reference to a Pin ID.

Given a lat, long and radius I would like to find any Pin+Fence that intersects (ST_Intersects) with the given circle.

Since I can't represent/save a circle in PostGIS as is and some answers on Stackoverflow refer to using ST_Buffer. Can I store the output of ST_Buffer or ST_Envelope in a new column in the Fence table and maybe use a trigger to update it (when Pin row get's updated)?

Answer 1

You don't have to create another column:

SELECT *
FROM pins INNER JOIN fence ON pins.id=fence.point
WHERE ST_Intersects(ST_Buffer(pins.geom,fence.radius),ST_Buffer(ST_GeomFromText('POINT(12 12)'), 10))

and you can store the output. First create a new geometry column:

ALTER TABLE fence ADD COLUMN geom geometry

Then update the column:

UPDATE fence
SET geom=ST_Buffer(pins.geom,radius)
FROM pins
WHERE pins.id=fence.point