0

The table that i want to perform this query has 3 columns: id, the_geom and seen_count.

The query i've been using to get points inside a bounding box is as follows:

SELECT id, ST_AsGeoJSON(the_geom) FROM table WHERE ST_Contains(ST_MakeEnvelope({0}, {1}, {2}, {3}, 4326), table.the_geom)

What i want to do is: selecting points, which are transformed to circles using their seen_count as radius, intersect with bounding box. Is it possible? If so, would it be an expensive query? Thanks in advance.

2
  • 1
    Wrap the table.geom in ST_Buffer() using the seen_count column as buffer distance, like so: SELECT id, ST_AsGeoJSON(the_geom) FROM table WHERE ST_Contains(ST_MakeEnvelope({0}, {1}, {2}, {3}, 4326), ST_Buffer(table.the_geom, table.seen_count)). I guess you realize that ST_Contains will return the ids only of points where the buffered circle it totally within the bounding box.
    – Micha
    Commented Jun 23, 2013 at 11:41
  • Thank you for showing me a starting point. I found the answer!
    – onatm
    Commented Jun 23, 2013 at 19:48

1 Answer 1

0

Thanks to Micha, i found the answer:

SELECT id, ST_AsGeoJSON(the_geom) FROM table WHERE ST_INTERSECTS(ST_MakeEnvelope({0}, {1}, {3}, {4}, 4326), 
ST_Buffer(the_geom::geography, 1000*(seen_count+1))::geometry);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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