PostGIS select sites within buffer

Question

I have a WMS query (GeoServer) that returns info from my layer into a popup in Leaflet.JS.However I was wondering how would do this outside of Leaflet, i.e. on a standard HTML/PHP page.

I assume I would use the ST_buffer function in PostGIS when I do my query, order by distance and then limit by 1. For example I have a table with the_geom and lets say house name, How would I do a simple buffer that selects the nearest house to that given point which happens to be from the same table using the same geom column. I think if I can just get that working I should be able to extend the query myself to provide the info I need.

Answer 1

Not buffer, distance. Fill in your query house id, and search radius:

SELECT 
  a.house_id, 
  ST_Distance(a.geom, b.geom) AS distance,
  a.house_name
FROM houses a, houses b
WHERE
  ST_DWithin(a.geom, b.geom, %radius) AND
  b.id = %house_id
ORDER BY distance ASC;