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 have a trips table and a bus_stops table. The trips table has a from_stop_id and a to_stop_id. The bus_stops table has lat and lng information for each bus stop.

How can I order the trips in descending order of popularity, where popularity is defined as the number of other trips in the database that start and end within a particular distance of the given trip? For the moment, I am happy to work with 'as the crow flies' distance between two points.

Neither of the tables is spatial but I am using Postgres and adding PostGIS extension is also possible, so if that's the best way forward then I'd love to hear it.

thanks a million

share|improve this question
    
Before attempting an answer, can you clarify two questions: Is either of the tables spatial? i.e. do they have a 'the_geom' column in PostGIS? And second, to calculate popularity as number of other trips that start/end within a certain distance, is this a straight line distance "as the crow files" or distance along some street network? –  Micha Jul 12 '12 at 18:28

1 Answer 1

Here's an approach. First get PostGIS installed. There's plenty of explanations how to do that such as postgis.net installation:

Now make your bus_stops table spatial:

-- Assuming the Lng/Lat are WGS84 coordinates 
SELECT AddGeometryColumn('bus_stops','geom',4326,'POINT',2); 

UPDATE bus_stops SET geom=ST_MakePoint(lng, lat);

-- Here's a query that returns the distances for each trip 
-- between the "from' bus_stop and the "from" stop of all the other trips 

SELECT ST_Distance_Sphere(bs1.geom, bs2.geom), t.t_id  
FROM trips AS t, bus_stops AS bs1, bus_stops AS bs2  
WHERE ( t.from_bs_id=bs1.bs_id AND t.from_bs_id <> bs2.bs_id );  

-- And similarly, the distances between the "to" bus stops 

SELECT ST_Distance_Sphere(bs1.geom, bs2.geom), t.t_id  
FROM trips AS t, bus_stops AS bs1, bus_stops AS bs2  
WHERE (t.to_bs_id=bs1.bs_id AND t.to_bs_id<>bs2.bs_id);

-- To count how many trips have "from" stops within a certain minimum:
-- (The numbers are from some fictitious tables)
-- (I choose 3000 m. as a sample. Set to whatever is needed...)  

SELECT t.t_id AS TripID, count(t.t_id) As Cnt_Close 
FROM trips AS t, bus_stops AS bs1, bus_stops AS bs2 
WHERE (t.to_bs_id=bs1.bs_id AND 
    t.to_bs_id<>bs2.bs_id) AND 
    ST_Distance_Sphere(bs1.geom, bs2.geom) < 3000 
GROUP BY TripID ORDER BY Cnt_Close; 
tripid | cnt_close 
            --------+-----------
                  8 |         1
                 11 |         1
                  9 |         1
                 13 |         2
                  7 |         2
                 12 |         2
                 10 |         2
                  5 |         2
                  4 |         2
                  6 |         3
                  1 |         3
                 14 |         3
                  2 |         5
                  3 |         5
            (14 rows)

So we're getting close... Next we merge into the query both the distances of the "from" stops and the distances to the "to" stops:

SELECT t.t_id AS TripID, count(t.t_id) As Cnt_Close 
FROM trips AS t, bus_stops AS bs1, bus_stops AS bs2, bus_stops AS bs3, bus_stops AS bs4 
WHERE (t.to_bs_id=bs1.bs_id AND t.to_bs_id<>bs2.bs_id) AND 
    (t.from_bs_id=bs3.bs_id AND t.from_bs_id<>bs4.bs_id) AND 
    ST_Distance_Sphere(bs1.geom, bs2.geom) < 2000 AND 
    ST_Distance_Sphere(bs3.geom, bs4.geom) < 2000 
GROUP BY TripID ORDER BY Cnt_Close;
     tripid | cnt_close 
    --------+-----------
          5 |         2
          8 |         2
          9 |         2
          1 |         2
         11 |         2
          2 |         2
         13 |         3
          3 |         3
          7 |         4
          6 |         4
         10 |         4
         14 |         6
    (12 rows)

Now we're ready to setup the popularity. First add a column to hold the value:

ALTER TABLE trips ADD COLUMN popularity INTEGER;
-- Now the update using the above subquery

UPDATE trips SET popularity=(
SELECT count(t.t_id) 
FROM trips AS t, bus_stops AS bs1, bus_stops AS bs2, bus_stops AS bs3, bus_stops AS bs4 
WHERE (t.to_bs_id=bs1.bs_id AND t.to_bs_id<>bs2.bs_id) AND 
    (t.from_bs_id=bs3.bs_id AND t.from_bs_id<>bs4.bs_id) AND 
    ST_Distance_Sphere(bs1.geom, bs2.geom) < 2000 AND 
    ST_Distance_Sphere(bs3.geom, bs4.geom) < 2000 AND 
    t.t_id=trips.t_id
);
UPDATE 14

Whew...

share|improve this answer
    
wow! thanks so much! I'm going to try it out. Will report back with my results. thanks –  svs Jul 15 '12 at 6:00

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.