I've created a PostGIS database of bus stops and train stations (from the gtfs file of two transit authority -- CITLA is the bus and AMT_Trains is... well, the train).

I've created a 200 meters buffer around each train station and tried to select each bus stop that intersects those buffers :

CREATE TABLE citla.stop_stations AS
    SELECT citla.stops.stop_id AS cit_stop_id, amt_trains.stops.stop_id AS amt_stop_id, citla.stops.the_geom AS the_geom
    FROM amt_trains.stops, citla.stops
    WHERE ST_Intersects(amt_trains.stops.the_geom, citla.stops.the_geom); 

In this screenshot, screenshot

we see that the buffers (the grey circles) are correct (I'm telling you, they are at the right place... I've just removed the train stations symbol for clarity).

The red dots are bus stops. The bigger blue dots are intersecting point (results from a spatial selection in QGIS). The single yellow dot is the PostGIS query result. It's just a sample, but only 6 of the supposed bus stops are selected.

I tried with

WHERE ST_Contains(amt_trains.stops.the_geom, citla.stops.the_geom);

but I have the exact same results.

I'm new to PostGIS and I'm stuck. Any ideas?


  • 3
    Can you be sure that both datasets are in the same projection?
    – Stev_k
    Aug 31, 2012 at 15:51
  • I've also tried ST_CoveredBy and ST_Within without success...
    – fgcarto
    Aug 31, 2012 at 17:14
  • Even WHERE ST_Distance(citla.stops.the_geom, amt_trains.stops.the_geom)=0; gives me the same results... frustrating!
    – fgcarto
    Aug 31, 2012 at 17:24
  • My buffers weren't implicitly declared as SRID 32188 (MTM8), but they were created from projected points.... When I look into the SQL Pane of my table, it's not written the_geom(Polygon, 32188), but when I open it in QGIS, the projection's right... I've try a ST_SetSRID(the_geom, 32188), but it didn't change anything.
    – fgcarto
    Aug 31, 2012 at 18:00

1 Answer 1


Are the geometries in your amt_trains.stops table buffered already? My guess is no. Instead of a buffer, I'd suggest something like ST_DWithin...

  bs.stop_id AS cit_stop_id,
  ts.stops.stop_id AS amt_stop_id,
  bs.stops.the_geom AS the_geom
FROM amt_trains.stops AS ts, citla.stops AS bs
WHERE ST_DWithin(ts.the_geom, bs.the_geom, 200)

I'm assuming that both amt_trains.stops and citla.stops are in the same projection (same SRID) and the projection's units are in meters.

  • (1/2) Woot!!! You were right. Well, you pointed me into the right direction. I already had created my buffers so I couldn't use a ST_Contains or ST_Intersects with my stops which weren't in my buffer table. I changed to « WHERE ST_Contains(amt_trains.train_buffers.the_geom, citla.stops.the_geom); » and it partly worked because I had now like 200 results because it was intersecting with the "layer" of each train stations so I had each intersecting stops intersecting with all the train stations (which is also false)
    – fgcarto
    Aug 31, 2012 at 17:58
  • (2/2) But I saw that you uses ST_DWithin so I got rid of my buffers and just do a search by distance and it worked!!! Well, that's just the first step of my project, but it was frustrating ;) THANKS EVERYBOBY!
    – fgcarto
    Aug 31, 2012 at 18:06

Your Answer

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

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