diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/migrate_from_osgb36_to_wgs84.pl | 201 | ||||
| -rw-r--r-- | db/schema.sql | 40 |
2 files changed, 229 insertions, 12 deletions
diff --git a/db/migrate_from_osgb36_to_wgs84.pl b/db/migrate_from_osgb36_to_wgs84.pl new file mode 100644 index 000000000..62eac8296 --- /dev/null +++ b/db/migrate_from_osgb36_to_wgs84.pl @@ -0,0 +1,201 @@ +#!/usr/bin/env perl + +use strict; +use warnings; + +=head1 DESCRIPTION + +This script will take a FMS database with eastings and northings in and migrate +it to latitude and longitude. It touches the following tables and functions: + +=cut + +use FindBin; +use lib "$FindBin::Bin/../perllib"; +use lib "$FindBin::Bin/../commonlib/perllib"; + +use mySociety::Config; +use mySociety::DBHandle qw(dbh); +use Utils; + +BEGIN { + mySociety::Config::set_file("$FindBin::Bin/../conf/general"); + mySociety::DBHandle::configure( + Name => mySociety::Config::get('BCI_DB_NAME'), + User => mySociety::Config::get('BCI_DB_USER'), + Password => mySociety::Config::get('BCI_DB_PASS'), + Host => mySociety::Config::get( 'BCI_DB_HOST', undef ), + Port => mySociety::Config::get( 'BCI_DB_PORT', undef ) + ); +} + +my $UPDATE_BATCH_SIZE = 1; # FIXME - should be ~ 500 + +migrate_problem_table(); +migrate_problem_find_nearby_function(); +migrate_alert_table(); + +=head2 problem table + +Add columns 'latitude' and 'longitude'. +Update all entries coverting from e,n to lon,lat. +Make the lat, lon columns not null. +Drop the 'problem_state_easting_northing_idx' index. +Create new index 'problem_state_latitude_longitude_idx'. +Drop the 'easting' and 'northing' columns. + +=cut + +sub migrate_problem_table { + + my $dbh = dbh(); + + # add columns + print "add latitude, longitude columns\n"; + $dbh->do("ALTER TABLE problem ADD $_ double precision") + for qw(latitude longitude); + $dbh->commit; + + # create a query for rows that need converting + my $rows_to_convert_query = $dbh->prepare( # + "SELECT id, easting, northing FROM problem" + . " WHERE latitude is NULL limit $UPDATE_BATCH_SIZE" + ); + + # update query + my $update_lat_lon_query = $dbh->prepare( # + "UPDATE problem SET latitude = ?, longitude = ? WHERE id = ?" + ); + + # loop through the entries in batches updating rows that need it. Do this in + # Perl rather than SQL for conveniance. + while (1) { + $rows_to_convert_query->execute; + last unless $rows_to_convert_query->rows; + while ( my $r = $rows_to_convert_query->fetchrow_hashref ) { + my ( $latitude, $longitude ) = + Utils::convert_en_to_latlon( $r->{easting}, $r->{northing} ); + print "update problem $r->{id}: ( $latitude, $longitude )\n"; + $update_lat_lon_query->execute( $latitude, $longitude, $r->{id} ); + } + $dbh->commit; # every batch of updates + } + + print "make latitude, longitude columns not null\n"; + $dbh->do("ALTER TABLE problem ALTER COLUMN $_ SET NOT NULL") + for qw(latitude longitude); + $dbh->commit; + + # drop old index, create new one + print "drop and create indexes\n"; + $dbh->do("DROP INDEX problem_state_easting_northing_idx"); + $dbh->do( "CREATE INDEX problem_state_latitude_longitude_idx " + . "ON problem(state, latitude, longitude)" ); + $dbh->commit; + + # drop columns + print "drop easting, northing columns\n"; + $dbh->do("ALTER TABLE problem DROP $_") for qw(easting northing); + $dbh->commit; + +} + +=head2 problem_find_nearby function + +Convert to use lat and long. +Also swap parameter order so that it is lat,lon rather than lon,lat to be consistent with pledgebank etc + +=cut + +sub migrate_problem_find_nearby_function { + my $dbh = dbh(); + + print "drop the existing problem_find_nearby function\n"; + $dbh->do( +"DROP FUNCTION problem_find_nearby ( double precision, double precision, double precision)" + ); + + print "create the new one\n"; + $dbh->do(<<'SQL_END'); + create function problem_find_nearby(double precision, double precision, double precision) + returns setof problem_nearby_match as + ' + -- 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 angle_between(radians(longitude), radians($2)) + < $3 / (R_e() * cos(radians($1 + $3 / R_e())))) + -- 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 +SQL_END + + $dbh->commit; +} + +=head2 alert table + +NOTE: only for alert_types 'local_problems' or 'local_problems_state' + +parameter: convert easting to longitude +parameter2: convert nothing to latitude + +create a new column 'is_migrated' to use during migration in case of crash. + +=cut + +sub migrate_alert_table { + my $dbh = dbh(); + + print "Adding 'is_migrated' column\n"; + $dbh->do("ALTER TABLE alert ADD COLUMN is_migrated bool DEFAULT false"); + $dbh->commit; + + # create a query for rows that need converting + my $rows_to_convert_query = $dbh->prepare( # + "SELECT id, parameter, parameter2 FROM alert" + . " WHERE alert_type IN ('local_problems','local_problems_state')" + . " AND is_migrated = false" + . " LIMIT $UPDATE_BATCH_SIZE" + ); + + # update query + my $update_lat_lon_query = $dbh->prepare( # + "UPDATE alert SET parameter2 = ?, parameter = ?, is_migrated = true" + . " WHERE id = ?" + ); + + # loop through the entries in batches updating rows that need it. Do this in + # Perl rather than SQL for conveniance. + while (1) { + $rows_to_convert_query->execute; + last unless $rows_to_convert_query->rows; + while ( my $r = $rows_to_convert_query->fetchrow_hashref ) { + my ( $latitude, $longitude ) = + Utils::convert_en_to_latlon( $r->{parameter}, $r->{parameter2} ); + print "update alert $r->{id}: ( $latitude, $longitude )\n"; + $update_lat_lon_query->execute( $latitude, $longitude, $r->{id} ); + } + $dbh->commit; # every batch of updates + } + + print "drop 'is_migrated' column\n"; + $dbh->do("ALTER TABLE alert DROP COLUMN is_migrated"); + $dbh->commit; +} + diff --git a/db/schema.sql b/db/schema.sql index 53d188244..fbff047fb 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -121,8 +121,8 @@ create table problem ( -- Problem details postcode text not null, - easting double precision not null, - northing double precision not null, + latitude double precision not null, + longitude double precision not null, council text, -- the council(s) we'll report this problem to areas text not null, -- the voting areas this location is in category text not null default 'Other', @@ -155,7 +155,7 @@ create table problem ( whensent timestamp, send_questionnaire boolean not null default 't' ); -create index problem_state_easting_northing_idx on problem(state, easting, northing); +create index problem_state_latitude_longitude_idx on problem(state, latitude, longitude); create table questionnaire ( id serial not null primary key, @@ -196,25 +196,41 @@ create type problem_nearby_match as ( distance double precision -- km ); --- problem_find_nearby EASTING NORTHING DISTANCE --- Find problems within DISTANCE (km) of (EASTING, NORTHING). +-- problem_find_nearby LATITUDE LONGITUDE DISTANCE +-- Find locations within DISTANCE (km) of (LATITUDE, LONGITUDE). create 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%: + -- http://www.ga.gov.au/nmd/geodesy/datums/distance.jsp + -- 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. ' -- trunc due to inaccuracies in floating point arithmetic select problem.id, - sqrt(($1 - easting) ^ 2 - + ($2 - northing) ^ 2) - as distance + 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 angle_between(radians(longitude), radians($2)) + < $3 / (R_e() * cos(radians($1 + $3 / R_e())))) -- ugly -- unable to use attribute name "distance" here, sadly - sqrt(($1 - easting) ^ 2 - + ($2 - northing) ^ 2) - < $3 * 1000 + 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; -- should be "stable" rather than volatile per default? @@ -277,7 +293,7 @@ create table alert_type ( create table alert ( id serial not null primary key, alert_type text not null references alert_type(ref), - parameter text, -- e.g. Problem ID for new updates + parameter text, -- e.g. Problem ID for new updates, Longitude for local problem alerts parameter2 text, -- e.g. Latitude for local problem alerts email text not null, confirmed integer not null default 0, |