1

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)?

  • 1
    Please ask only one question per Question. – Vince Aug 30 '16 at 3:28
  • @Vince I edited the answer. – Jimmy Aug 30 '16 at 3:39
  • You seem to still be asking two questions. – PolyGeo Aug 30 '16 at 4:00
  • @PolyGeo So you believe I should move the second part of my question (which is just a follow-up to the first part) to its own Question and then either repeat the same explanation of my use case or reference this question?. Is that better than what I did here? – Jimmy Aug 30 '16 at 4:09
  • 1
    @PolyGeo makes sense, I removed that part. Thanks. – Jimmy Aug 30 '16 at 4:14
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 
  • Thanks for the answer, but would I need the other column to store the bounding box and be able to use Spatial index, assuming I have thousands of locations (like it's mentioned here revenant.ca/www/postgis/workshop/indexing.html)? – Jimmy Aug 30 '16 at 17:27
  • 1
    @Jimmy Updated answer to include how to create a new column and then update the column based on info from the joined table. – kttii Aug 30 '16 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.