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'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?

THANKS!!

share|improve this question
3  
Can you be sure that both datasets are in the same projection? –  Stev_k Aug 31 '12 at 15:51
    
I've also tried ST_CoveredBy and ST_Within without success... –  fgcartographix Aug 31 '12 at 17:14
    
Even WHERE ST_Distance(citla.stops.the_geom, amt_trains.stops.the_geom)=0; gives me the same results... frustrating! –  fgcartographix Aug 31 '12 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. –  fgcartographix Aug 31 '12 at 18:00
add comment

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
    
(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) –  fgcartographix Aug 31 '12 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! –  fgcartographix Aug 31 '12 at 18:06
add comment

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.