Increase precision of auxiliary data columns

Thanks. This patch belongs here :)

/**
 * Convert auxiliary data columns to double and recalculate existing data.
 */
function geolocation_update_7101() {
  $table_prefixes = array('field_data_', 'field_revision_');
  $fields = geolocation_get_geolocation_fields();

  foreach ($table_prefixes as $table_prefix) {
    foreach ($fields as $field) {

      $field_name = $field['field_name']; // eg 'field_mygeolocname' ;
      $table = $table_prefix . $field_name;

      // Convert three db columns from float to double precision
      $columns = array($field_name . '_lat_sin', $field_name . '_lat_cos', $field_name . '_lng_rad');
      $spec = array('type' => 'float', 'size' => 'big', 'not null' => TRUE, 'default' => 0.0);
      // Or this, which is what Geofield uses:
      // $spec = array('type' => 'numeric', 'precision' => 18, 'scale' => 12, 'default' => 0.0);
      foreach ($columns as $column) {
        db_change_field($table, $column, $column, $spec);
      }

      // Now update these columns by re-calculating and re-storing existing values
      $column_lat = $field_name . '_lat';
      $column_lng = $field_name . '_lng';

      $query = db_select($table, 'g')
        ->fields('g', array('revision_id', $column_lat, $column_lng));
      $results = $query->execute();

      foreach ($results as $row) {
        $lat_rad = deg2rad($row->{$column_lat});
        $lat_sin = sin($lat_rad);
        $lat_cos = cos($lat_rad);
        $lng_rad = deg2rad($row->{$column_lng});

        // Cannot use drupal_write_record() within hook_update_N(), as the database
        // schema cannot be relied on when a user is running a series of updates.
        $updated = db_update($table)
          ->fields(array(
            $columns[0] => $lat_sin,
            $columns[1] => $lat_cos,
            $columns[2] => $lng_rad
          ))
          ->condition('revision_id', $row->revision_id)
          ->execute();
      }
    }
  }
  return t('Geolocation auxiliary data converted to double precision.');
}

function geolocation_get_geolocation_fields() {
  $types = array_keys(geolocation_field_info()); // returns one value in our case
  $fields = array();
  foreach (field_info_fields() as $field) {
    if (in_array($field['type'], $types)) {
      $fields[] = $field;
    }
  }
  return $fields;
}

One comment: Lets use what Geofield uses.

$spec = array('type' => 'numeric', 'precision' => 18, 'scale' => 12, 'default' => 0.0);

Any help to turn this into a real patch is appreciated :)

the real patch :)

We need some testing here. When testing the patch you should compare the database schema (especially of all geolocation fields) and the values for lat_sin, lat_cos, lng_rad before and after running update.php.

The patch above takes care of all existing fields. What about new fields added via the UI? The patch does not alter the schema itself but only existing fields, right? Do we need to change the schema as well? Guess so...

Patch looks good. Thanks, committed!

Patch fails: http://drupal.org/node/1867686

• #1867686: Update #7101 fails
• #2048129: Failed db update from v1.1 - DatabaseSchemaObjectDoesNotExistException
• #2048213: during update i receive the foll database error

This definitely needs work.

Thanks for jjchinquist who pointed me into the right direction with $table = $table_prefix . $field_name;
#2048213: during update i receive the foll database error

Here is a patch, which I tested:

• Installed Geolocation Field 7.x-1.1
• Added Geolocation Field to some content types
• Added some nodes with Geolocation Field data
• Patched and updated without errors

This patch is should be in the latest 7.x-1.x-dev in a couple of minutes ... please test and review ...

The update.php ran both 7001 7101 and 7002 7102 without error. I was able to create and update a node on a mirrored test site of a production server - no issues. I think it is ok.

Thanks for testing this. I think this should be ready for 7.x-1.4