Trying to save a point with a high degree of accuracy, eg: POINT (-0.048065185546875 0.17715425874914836)
Causes a PDOException: in field_sql_storage_field_storage_write() .

Investigating the actual exception message reveals:
SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'field_location_geohash' at row 1

Turns out the above point generates the following geohash: ebpcnbhzzzzyrfpgzcxzrfxy which is 24chars long. Geofield's geohash column is limited to 16 characters.

Geofield should probably either be more lenient about geohash lengths or do some simplification before calculating the geohash to ensure its not trying to save data that excedes 16 chars.

CommentFileSizeAuthor
#10 geofield-db_update_problem-1841932-10.patch2.53 KBdwadson
#8 geofield-db_update_problem-1841932-8.patch1.35 KBdwadson
#1 1841932.truncate_geohash_len.patch1.2 KBtnightingale

Comments

tnightingale’s picture

tnightingale commented
StatusFileSize
new 1841932.truncate_geohash_len.patch1.2 KB

Chatted with phayes on IRC. He determined that a 16char geohash is accurate to the size of a dust mite and that it is safe to truncate geohashes to 16 chars. Here is a patch that does so.

tnightingale’s picture

tnightingale commented
Status: Active » Needs review
Brandonian’s picture

Brandonian commented
Status: Needs review » Fixed

Yeah, but we're all going to feel really dumb in a few months when somebody wants to accurately map the location of individual molecules... :-)

Commited. Nice work, @tnightingale.

http://drupalcode.org/project/geofield.git/commit/103c6cf

cesareaugusto’s picture

cesareaugusto commented

I got a similar failure error when update.php to the Nov 16th dev. After that first attempt, every db update I do it says I got a pending update:

1 PENDING UPDATE
geofield module

7202 - Drops unused table fields srid, accuracy and source, adds geohash field, populates it.

Applying the update I got this geofield module message

Update #7202
Failed: DatabaseSchemaObjectExistsException: Cannot add field <em class="placeholder">field_data_field_posizione</em>.<em class="placeholder">field_posizione_geohash</em>: field already exists. in DatabaseSchema_mysql->addField() (line 328 of /home/sideoffc/public_html/includes/database/mysql/schema.inc).
mkostir’s picture

mkostir commented

Hi guys,
I have exactly the same problem as #4.

phayes’s picture

phayes commented
Title: Geohashes >16 chars in length break » Geohashes database update problems
Status: Fixed » Active
cesareaugusto’s picture

cesareaugusto commented

Did someone find any solution?

dwadson’s picture

dwadson commented
StatusFileSize
new geofield-db_update_problem-1841932-8.patch1.35 KB

The patch truncastes the existing geohash value to GEOFIELD_GEOHASH_LENGTH before populating the geohash column, which prevents the initial failure on updating.

The second error in #4 can be resolved by getting rid of the geohash field that the failed update added: 

ALTER TABLE field_data_field_posizione drop field_posizione_geohash;
ALTER TABLE field_revision_field_posizionedrop field_posizione_geohash;
cesareaugusto’s picture

cesareaugusto commented

The second error in #4 can be resolved by getting rid of the geohash field that the failed update added:

ALTER TABLE field_data_field_posizione drop field_posizione_geohash;
ALTER TABLE field_revision_field_posizionedrop field_posizione_geohash;

How could I do that in PHPMyAdmin?

dwadson’s picture

dwadson commented
Status: Active » Needs review
StatusFileSize
new geofield-db_update_problem-1841932-10.patch2.53 KB

Try this patch - it will delete the _geohash column if it already exists (from the original failed update).

Brandonian’s picture

Brandonian commented
Status: Needs review » Fixed

Thanks for the patch, @dwadson. I applied the patch from #8 (along with a followup modification for a typo introduced).

http://drupalcode.org/project/geofield.git/commit/e2ff6aa

cesareaugusto’s picture

cesareaugusto commented

@dwadson to which version should I apply your patch? I just upgraded to the latest alpha2, but the update error is still here!

To fix it should I just delete the field_posizione_geohash field within the field_data_field_posizione and field_revision_field_posizionedrop tables?

dwadson’s picture

dwadson commented

Yes, the #8 patch got committed, not the #10 patch which only tries to add the _geohash column if it doesn't exist.

Yes, delete that field and then update.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.