Buffer along a route

Question

I'm using Googlemaps service to plot some marker on the map. All markers are stored in a postgresql table, named "locations" and that has a location column (data type to geometry).
When the user clicks on the markers a route is created between them.

My question is: how can I generate a buffer (with a limit of 10km along route) and select all my locations inside the buffer. I`m really new in postgis.

P.S. This link inspired me but without success
Thanks!

Answer 1

SELECT ST_Astext(geom) FROM "locations" WHERE ST_Contains( ST_Buffer( St_GeomFromText( 'LINESTRING( 6 52, 6.5 53, 7 54 )', 4326 ), 10000 ), geom )

• ST_Buffer( geom, radius ) creates a 10km buffer. Im using a linestring as example. Second argument is in meters when using SRID 4326. Calculations are in the Spatial Reference System of this Geometry.
• ST_Contains( geomA, geomB ) Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.

Don't hesitate to ask me if you need more help!

edit:
SRID 4326 is NOT measuring in meters (see here). Besides that, it's better (faster) to use ST_DWithin. There is a note in the ST_Buffer documentation:

People often make the mistake of using this function to try to do radius searches. Creating a buffer to to a radius search is slow and pointless. Use ST_DWithin instead.

If you would like to measure in meters its better to use geography instead of geometry:

SELECT ST_Astext(geom) FROM "locations" WHERE ST_DWithin( ST_GeographyFromText( 'LINESTRING(48.85523 2.36179,48.85556 2.36205,48.85591 2.36226,48.857 2.36288,48.85806 2.36362,48.85834 2.36382,48.85818 2.36434,48.85817 2.36444,48.85817 2.36448,48.85882 2.3645,48.86 2.36454,48.86144 2.3646,48.86178 2.3646,48.86184 2.36458,48.86187 2.36455,48.86193 2.36446,48.86199 2.36455,48.86205 2.3646,48.8621 2.36464,48.86277 2.36466,48.86367 2.36469,48.86459 2.36583,48.8647 2.36577)' ), geom::geography, 10000 );

It transforms the points to geographic points and the input is a geographic linestring. ST_GeographyFromText() description:

Returns a geography object from the well-known text representation. SRID 4326 is assumed.

Good luck!

edit2:
If you want to extract the polygon with the buffer area u can use this query:

SELECT ST_Astext(geom) as "point", ST_AsText( ST_Buffer( "linestring", 10000 ) ) as "buffer" FROM "locations", ST_GeographyFromText( 'LINESTRING(48.85523
2.36179,48.85556 2.36205,48.85591 2.36226,48.857 2.36288,48.85806 2.36362,48.85834 2.36382,48.85818 2.36434,48.85817 2.36444,48.85817 2.36448,48.85882 2.3645,48.86 2.36454,48.86144 2.3646,48.86178 2.3646,48.86184 2.36458,48.86187 2.36455,48.86193 2.36446,48.86199 2.36455,48.86205 2.3646,48.8621 2.36464,48.86277 2.36466,48.86367 2.36469,48.86459 2.36583,48.8647 2.36577)' ) as "linestring" WHERE ST_DWithin("linestring", geom::geography, 10000 );