Geofield truncates latitude/longitude field to four digits after decimal

We store the full value, but merely truncate the output of the formatter for readability.

This is really a feature request for two things:
1. A "lat, lon" output formatter
2. The ability to configure the output rounding (or turn it off entirely)

Totally doable and not too much work.

Hi ingram87,

I've added the ability to turn off those labels. It's in the formatter settings.

I was debugging this and it turns out that the truncated values for centroid lat / lon are actually being stored in the database. Looking into this I found that we are using "float", but not specifying a "size" for the float column. This is resulting in using a four-byte float which only gives you a totally of 6 digits. As per the schema API (http://drupal.org/node/159605) we should be using "big" which would give us 15 digits.

This is a significant (no pun intended!) issue because not only does it affect display (not such a big deal in my mind), but also affects views-queries and any spatial indexing we might be doing (ie via apache solr). We really should fix this for version 1.0. This will require us to do two things:

1. Add the "big" size to all our float columns in the install file
2. Add an update.php hook. This will basically involve gathering up all geofield tables and running db_change_field($table_name, $field_name, $field_name, $new_field_definition);

ingram,

the "Turning off labels" feature-request you made has been committed. You can find it in the development branch. The issue regarding truncating of significant digits is a real bug that we need to fix.

The 'big' size refers to the number of digits before the decimal point, you only need to be able to store -180 to 180 with xx after the decimal point. In mysql you can set what is displayed by setting the length of the float with something like '10,6' which will give you 6 decimal places displayed. Mysql stores the whole number that was submitted initially but only displays 4 digits after the decimal point by default, eg if the length is not set.

According to the mysql manual a single-precision floating-point number is accurate to approximately 7 decimal places. This translates to about 1 cm at the equator, see wikipedia.org/wiki/Decimal_degrees for more on this.

The Drupal schema (as far as I know) does not let you set length for the float type. I do it afterwards in phpmyadmin. This is obviously not an ideal solution but it works. It might be possible to change this in an hook_update_N() function or hook_enable() function using the SQL 'ALTER TABLE' statement.

Another solution would be to use something like

  'type' => 'numeric',
  'precision' => 10,
  'scale' => 7,

in hook_schema()

My own feeling is that using 'big' is overkill, 'float' is what should be used if a way of sizing it properly can be found and 'numeric' is OK using the above code snippet. I use 7 for scale on the assumption that the least significant digit has been rounded and is therefore 'unreliable' which does not mean much as the actual distance it represents is tiny. In my experience Google address lookups are at best accurate to within 10 -20 meters although addresses that have been manually altered might do much better.

Hope this helps.

I still had issues with this, after setting the fields to float(12,8) so had to change them to decimal fields. Is there much overhead in doing that?

Searched around for the best (SQL, MySQL in my case) column type to use for storing latitude/longitude data and came up with the following discussion: http://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-fo...
Where some suggest the Spatial extensions for MySQL. That might make things better, but less portable etc.

Considering the values I have here (with 12 decimals), and not liking something to round those numbers down, I chose for DECIMAL(18,12) and that fixed it for me.

This looks good teezee.

We should also provide an upgrade path for existing users.

Upgrade path is reeeeeaaaally difficult at the moment, due to this core issue: #937442: Field type modules cannot maintain their field schema (field type schema change C(R)UD is needed). I tried all day yesterday to build something that would work in most cases, but I ran into the 'modularity' of the Fields API and it's different ways of storing data using different field storage handlers.

I found out that the float type doesn't destroy the data after the precision, it basically rounds stuff when returning it. I think there are these four possible solutions (I think all of them suck):

1. Wait for issue #937442: Field type modules cannot maintain their field schema (field type schema change C(R)UD is needed) to be fixed, that should automatically present an available database update.
2. Provide an example SQL query in an UPDATE.txt.
3. Cook up a dangerous script which looks up all geofield instances, checks if they are handled by field_sql_storage, tries to find the database tables (including revisions) and columns involved and perform some db_change_field() thingy.
4. For a temporary workaround (until #937442: Field type modules cannot maintain their field schema (field type schema change C(R)UD is needed)) you could call geofield_compute_values() on one of the load/process/view moments. This will use the (unaltered) 'wkt' value and return a geometry array containing the un-truncated versions.

Manually changing the columns to DECIMAL(18,12) worked for me, and revealed all the correct data (as long as they were not edited). It's a shitty job but can be done if you don't want to wait for all the above. Don't forget to alter all involved lat, lon, top, bottom, left, right columns in all field tables and the field tables for revisions!

I changed the tables like you suggest and this fix's the values in the database. The display values are still truncated unless I use WKT. I would prefer to display latitude and longitude. Do I have to change the variable in the .inc?

Attached patch changes field type to numeric/decimal with a precision of 18 and scale of 12 (DECIMAL(18,12)) and includes an upgrade path using hook_update_N(). Hope this helps some of you. It will work regardless of #937442: Field type modules cannot maintain their field schema (field type schema change C(R)UD is needed), but only when field_sql_storage is used.

Forgot to change the status. Patch contains both field changes and an upgrade path. Not sure what the N in hook_update_N() should be. Used 7200 as it is a patch against Geofield 7.x-2.x-dev.

At a glance, patch looks good, but haven't tested yet. Tagging for sprint next week.

Any progress on getting this in the 2x-dev?

@teezee, it's on my radar. Since the issue is marked as needs review, I feel like either myself or someone else needs to independently test, and I haven't had a chance yet.

@teezee, the upgrade hook worked well for me, and db fields were updated as expected. The only issue that I noticed was that some of our field formatters were display insignificant digits. For example, a field with a lat/lon of 41, -86 would display as 41.000000000000, -86.0000000000000 in both the widget and the field formatter. I took care of it with a followup commit.

Otherwise, great patch. Committed.

http://drupalcode.org/project/geofield.git/commit/548e7df
http://drupalcode.org/project/geofield.git/commit/8f09b02

Marking as needs work b/c of #1657910: Field type update fails in PostgreSQL.

Sorry to open this up again,

however when upgrading from 2.x-dev(previous) to 2.x-dev(current), update hook 7200 causes an error:

The following updates returned messages

geofield module

Update #7200
Failed: PDOException: SQLSTATE[HY000]: General error: 1025 Error on rename of './d7/#sql-7885_1fed' to ' './d7/field_data_field_service_geofield' (errno: -1): ALTER TABLE {field_data_field_service_geofield} CHANGE `field_service_geofield_lat` `field_service_geofield_lat` DECIMAL(18, 12) NULL DEFAULT NULL; Array ( ) in db_change_field() (line 2985 of /home/d7/public_html/includes/database/database.inc).

Is this a database issue on my end or is the a problem altering the field?

colincalnan, are you using MySQL or postgres?

phayes, MySQL

Marking as fixed. The issue here no longer presents itself on upgrades from 1.x to 2.x, which is what is primarily supported.