Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 0 down vote accepted
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 );
share|improve this answer
    
SELECT ST_Astext(geom) FROM "locations" WHERE ST_Contains( ST_Buffer( St_GeomFromText( '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)', 4326 ), 100 ), geom );to get all points along route with 100 meters buffer but it returns all my points from the table –  George Hown Jul 21 '13 at 9:28
    
The above query must returns ONLY 2 points. –  George Hown Jul 21 '13 at 9:34
    
Updated the answer, I wasn't right about SRID 4326 measurement is in meters. –  Peter Schoep Jul 21 '13 at 12:21
    
It works! Thanks you a lot! You saved me! –  George Hown Jul 21 '13 at 15:21
1  
I know..it`s out of the thread scope but how can I extract the polygon with the buffer area? (if I want to draw in as an openlayer in googlemapas). –  George Hown Jul 21 '13 at 15:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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