Hi guys need some serious help
I have a PostgreSQL database full of bus routes and bus stops. I am drawing them out using perl and manipulating the data. What I need to be able to do is to compare the variables from one query to the variables in another. Allow me to explain further.
Firstly all unique routes are selected within a specific distance of my origin point.
[CODE=perl]
$originsql = qq{SELECT a.stop_referenc e, b.service_id, distance(PointF romText('POINT( $origin)', 27700),east_nor th), c.route_number FROM bus_stops a, service b, routes c WHERE distance(PointF romText('POINT( $origin)', 27700),east_nor th) < 200 AND a.stop_referenc e = b.stop_referenc e AND b.service_id = c.service_id ORDER BY b.service_id, distance(PointF romText('POINT( $origin)', 27700),east_nor th)};
$sth = $dbh->prepare( $originsql );
$sth->execute();
$sth->bind_columns ( undef, \$origin_stops, \$origin_servic e, \$origin_distan ce, \$origin_route_ number);
while ($sth->fetch()) {
if ($orig_service != $last_service_i d) {
print "The bus stop located nearest to your Origin area is: ".$origin_stops ."<br />\n";
print "And the bus stop is ".$origin_dista nce." metres away from your location <br />\n";
print "This stop serves route number ".$origin_route _number." <p><p/>\n";
$last_orig_serv ice_id = $origin_service ;
}
}
[/CODE]
The same logic is applied to the destination point with a similar query:
[CODE=perl]
$destsql = qq{SELECT a.stop_referenc e, b.service_id, distance(PointF romText('POINT( $dest)', 27700),east_nor th), c.route_number FROM bus_stops a, service b, routes c WHERE distance(PointF romText('POINT( $dest)', 27700),east_nor th) < 200 AND a.stop_referenc e = b.stop_referenc e AND b.service_id = c.service_id ORDER BY b.service_id, distance(PointF romText('POINT( $dest)', 27700),east_nor th)};
$sth = $dbh->prepare( $destsql );
$sth->execute();
$sth->bind_columns ( undef, \$destination_s tops, \$destination_s ervice, \$destination_d istance, \$destination_r oute_number);
while ($sth->fetch()) {
if ($dest_service != $last_service_i d) {
print "The bus stop located nearest to your Origin area is: ".$destination_ stops."<br />\n";
print "And the bus stop is ".$destination_ distance." metres away from your location <br />\n";
print "This stop serves route number ".$destination_ route_number." <p><p/>\n";
$last_dest_serv ice_id = $destination_se rvice;
}
}
[/CODE]
Now what I need to be able to do is the compare the "$origin_servic e" to the "$destination_s ervice". A simple if($origin_serv ice == $destination_se rvice) clause wont work as it seems to only compare the last two routes from each query whereas I need it to loop through all of the results for each and find matching pairs.
One idea I had was to enter each origin and destination service numbers into two arrays and then compare them both? Or would I need some sort of double foreach statement to loop through both the origin and destination numbers finding mathcing pairs?
I have tried many options but cant seem to figure it out! Your help will be much appreciated. Thanks
I have a PostgreSQL database full of bus routes and bus stops. I am drawing them out using perl and manipulating the data. What I need to be able to do is to compare the variables from one query to the variables in another. Allow me to explain further.
Firstly all unique routes are selected within a specific distance of my origin point.
[CODE=perl]
$originsql = qq{SELECT a.stop_referenc e, b.service_id, distance(PointF romText('POINT( $origin)', 27700),east_nor th), c.route_number FROM bus_stops a, service b, routes c WHERE distance(PointF romText('POINT( $origin)', 27700),east_nor th) < 200 AND a.stop_referenc e = b.stop_referenc e AND b.service_id = c.service_id ORDER BY b.service_id, distance(PointF romText('POINT( $origin)', 27700),east_nor th)};
$sth = $dbh->prepare( $originsql );
$sth->execute();
$sth->bind_columns ( undef, \$origin_stops, \$origin_servic e, \$origin_distan ce, \$origin_route_ number);
while ($sth->fetch()) {
if ($orig_service != $last_service_i d) {
print "The bus stop located nearest to your Origin area is: ".$origin_stops ."<br />\n";
print "And the bus stop is ".$origin_dista nce." metres away from your location <br />\n";
print "This stop serves route number ".$origin_route _number." <p><p/>\n";
$last_orig_serv ice_id = $origin_service ;
}
}
[/CODE]
The same logic is applied to the destination point with a similar query:
[CODE=perl]
$destsql = qq{SELECT a.stop_referenc e, b.service_id, distance(PointF romText('POINT( $dest)', 27700),east_nor th), c.route_number FROM bus_stops a, service b, routes c WHERE distance(PointF romText('POINT( $dest)', 27700),east_nor th) < 200 AND a.stop_referenc e = b.stop_referenc e AND b.service_id = c.service_id ORDER BY b.service_id, distance(PointF romText('POINT( $dest)', 27700),east_nor th)};
$sth = $dbh->prepare( $destsql );
$sth->execute();
$sth->bind_columns ( undef, \$destination_s tops, \$destination_s ervice, \$destination_d istance, \$destination_r oute_number);
while ($sth->fetch()) {
if ($dest_service != $last_service_i d) {
print "The bus stop located nearest to your Origin area is: ".$destination_ stops."<br />\n";
print "And the bus stop is ".$destination_ distance." metres away from your location <br />\n";
print "This stop serves route number ".$destination_ route_number." <p><p/>\n";
$last_dest_serv ice_id = $destination_se rvice;
}
}
[/CODE]
Now what I need to be able to do is the compare the "$origin_servic e" to the "$destination_s ervice". A simple if($origin_serv ice == $destination_se rvice) clause wont work as it seems to only compare the last two routes from each query whereas I need it to loop through all of the results for each and find matching pairs.
One idea I had was to enter each origin and destination service numbers into two arrays and then compare them both? Or would I need some sort of double foreach statement to loop through both the origin and destination numbers finding mathcing pairs?
I have tried many options but cant seem to figure it out! Your help will be much appreciated. Thanks