SQL STDistance where both locations are in same record

Question

I'm new to geographic SQL queries. I have two tables; [station_information] which describes bicycle rental stations with a geography type spatial column, and [bike_rides] which has information on a trips on rental bikes.

[station_information] has station_id as a primary key, and [bike_rides] has to_station_id and a from_station_id column referencing the starting and ending stations of the bike trips. I want to create a distance column in [bike_rides] with the distance between the from_station_id and to_station_id for every record.

How do I go about doing this? I know I have to join the tables and use STDistance, but I don't know to us it. Every example of STDistance I've looked up creates variables for the start and end points and uses STGeomFromText to create the point, or uses the spatial columns from two different tables. Any help would be appreciated.

Answer 1

I'm envisioning your schema something like this:

create table dbo.station_information (
   station_id int not null
      constraint PK_station_information primary key clustered (station_id),
   location geography not null
);

create table  dbo.bike_rides (
    bike_ride_id int not NULL
        constraint PK_bike_rides primary key clustered (bike_ride_id),
    from_station_id INT
        constraint [FK_bike_rides_origin]
        foreign key (from_station_id)
        references dbo.station_information (station_id),
    to_station_id INT
        constraint [FK_bike_rides_destination]
        foreign key (to_station_id)
        references dbo.station_information (station_id)
);

If so, the following query should get you over the hump conceptually:

select br.bike_ride_id, 
   origin.[location].STDistance(destination.[location])
from dbo.bike_rides as br
join dbo.station_information as origin
    on br.from_station_id = origin.station_id
join dbo.station_information as destination
    on br.to_station_id = destination.station_id;

The joins in the query are just normal "join back to the place that has the details I want". The only weird thing (if you can call it weird) is that you have two columns that reference the same table so you have to join back to that table twice (once for each context in which you want to get the details).

Once those joins are performed, you have both the origin and destination geography columns available to you and so can do whatever geospatial calculations you want on them.