Search code examples
postgisintersectionbounding-box

How do I query for geometries in a rectangle bigger than what I request?

I have this PostGIS query: 

SELECT ST_AsGeoJSON(geom) AS geom_geojson 
FROM tracts AS tbl 
WHERE ST_MakeEnvelope(-86.84422306060793,36.14537465258748,-86.76182559967043,36.17846331773539) && ST_Transform(tbl.geom,4326);

This returns geometries in a rectangle made by those four points, but I'd like to get back geometries within a rectangle that's one kilometer bigger than that. What PostGIS query must I write?

Solution

  • Just use ST_DWithin on the geography type. If you have an index on tbl.geom::geography it'll use it. Or, you can store the tbl.geom as geography, and then you only have to have an index on the column.

    SELECT ST_AsGeoJSON(geom) AS geom_geojson 
FROM tracts AS tbl 
WHERE ST_DWithin(
  tbl.geom::geography,
  ST_MakeEnvelope(
    -86.84422306060793,
    36.14537465258748,
    -86.76182559967043,
    36.17846331773539
  ),
  1000
);