diff options
| -rw-r--r-- | db/schema.sql | 28 | ||||
| -rw-r--r-- | db/schema_0033-speed-up-nearby.sql | 47 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Nearby.pm | 12 |
3 files changed, 58 insertions, 29 deletions
diff --git a/db/schema.sql b/db/schema.sql index 5bac02bce..ef996fe8c 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -226,6 +226,7 @@ create table problem ( create index problem_state_latitude_longitude_idx on problem(state, latitude, longitude); create index problem_user_id_idx on problem ( user_id ); create index problem_external_body_idx on problem(lower(external_body)); +create index problem_radians_latitude_longitude_idx on problem(radians(latitude), radians(longitude)); create table questionnaire ( id serial not null primary key, @@ -242,17 +243,6 @@ create table questionnaire ( create index questionnaire_problem_id_idx on questionnaire using btree (problem_id); --- angle_between A1 A2 --- Given two angles A1 and A2 on a circle expressed in radians, return the --- smallest angle between them. -create function angle_between(double precision, double precision) - returns double precision as ' -select case - when abs($1 - $2) > pi() then 2 * pi() - abs($1 - $2) - else abs($1 - $2) - end; -' language sql immutable; - -- R_e -- Radius of the earth, in km. This is something like 6372.8 km: -- http://en.wikipedia.org/wiki/Earth_radius @@ -274,11 +264,11 @@ create function problem_find_nearby(double precision, double precision, double p -- table or results set in memory. That means we can't check the values of -- the parameters, sadly. -- Through sheer laziness, just use great-circle distance; that'll be off - -- by ~0.1%: - -- http://www.ga.gov.au/nmd/geodesy/datums/distance.jsp + -- by ~0.1%. -- We index locations on lat/lon so that we can select the locations which lie -- within a wedge of side about 2 * DISTANCE. That cuts down substantially -- on the amount of work we have to do. + -- http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates ' -- trunc due to inaccuracies in floating point arithmetic select problem.id, @@ -292,9 +282,13 @@ create function problem_find_nearby(double precision, double precision, double p longitude is not null and latitude is not null and radians(latitude) > radians($1) - ($3 / R_e()) and radians(latitude) < radians($1) + ($3 / R_e()) - and (abs(radians($1)) + ($3 / R_e()) > pi() / 2 -- case where search pt is near pole - or angle_between(radians(longitude), radians($2)) - < $3 / (R_e() * cos(radians($1 + $3 / R_e())))) + and ( + abs(radians($1)) + ($3 / R_e()) > pi() / 2 -- case where search pt is near pole + or ( + radians(longitude) > radians($2) - asin(sin($3 / R_e())/cos(radians($1))) + and radians(longitude) < radians($2) + asin(sin($3 / R_e())/cos(radians($1))) + ) + ) -- ugly -- unable to use attribute name "distance" here, sadly and R_e() * acos(trunc( (sin(radians($1)) * sin(radians(latitude)) @@ -302,7 +296,7 @@ create function problem_find_nearby(double precision, double precision, double p * cos(radians($2 - longitude)))::numeric, 14) ) < $3 order by distance desc -' language sql; -- should be "stable" rather than volatile per default? +' language sql stable; -- Comments/q&a on problems. diff --git a/db/schema_0033-speed-up-nearby.sql b/db/schema_0033-speed-up-nearby.sql new file mode 100644 index 000000000..845f3c22e --- /dev/null +++ b/db/schema_0033-speed-up-nearby.sql @@ -0,0 +1,47 @@ +begin; + +create index problem_radians_latitude_longitude_idx on problem(radians(latitude), radians(longitude)); + +drop function angle_between(double precision, double precision); + +create or replace function problem_find_nearby(double precision, double precision, double precision) + returns setof problem_nearby_match as + -- Write as SQL function so that we don't have to construct a temporary + -- table or results set in memory. That means we can't check the values of + -- the parameters, sadly. + -- Through sheer laziness, just use great-circle distance; that'll be off + -- by ~0.1%. + -- We index locations on lat/lon so that we can select the locations which lie + -- within a wedge of side about 2 * DISTANCE. That cuts down substantially + -- on the amount of work we have to do. + -- http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates +' + -- trunc due to inaccuracies in floating point arithmetic + select problem.id, + R_e() * acos(trunc( + (sin(radians($1)) * sin(radians(latitude)) + + cos(radians($1)) * cos(radians(latitude)) + * cos(radians($2 - longitude)))::numeric, 14) + ) as distance + from problem + where + longitude is not null and latitude is not null + and radians(latitude) > radians($1) - ($3 / R_e()) + and radians(latitude) < radians($1) + ($3 / R_e()) + and ( + abs(radians($1)) + ($3 / R_e()) > pi() / 2 -- case where search pt is near pole + or ( + radians(longitude) > radians($2) - asin(sin($3 / R_e())/cos(radians($1))) + and radians(longitude) < radians($2) + asin(sin($3 / R_e())/cos(radians($1))) + ) + ) + -- ugly -- unable to use attribute name "distance" here, sadly + and R_e() * acos(trunc( + (sin(radians($1)) * sin(radians(latitude)) + + cos(radians($1)) * cos(radians(latitude)) + * cos(radians($2 - longitude)))::numeric, 14) + ) < $3 + order by distance desc +' language sql stable; + +commit; diff --git a/perllib/FixMyStreet/DB/ResultSet/Nearby.pm b/perllib/FixMyStreet/DB/ResultSet/Nearby.pm index a0ccb8a6d..670673ab3 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Nearby.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Nearby.pm @@ -36,16 +36,4 @@ sub nearby { return \@problems; } -# XXX Not currently used, so not migrating at present. -#sub fixed_nearby { -# my ($dist, $mid_lat, $mid_lon) = @_; -# mySociety::Locale::in_gb_locale { select_all( -# "select id, title, latitude, longitude, distance -# from problem_find_nearby(?, ?, $dist) as nearby, problem -# where nearby.problem_id = problem.id and state='fixed' -# site_restriction -# order by lastupdate desc", $mid_lat, $mid_lon); -# } -#} - 1; |