ST_Intersects returns "incomplete" results

Question

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,

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?

THANKS!!

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...

SELECT
  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.