I have many nodes tied to location fields..
I expose the distance filter
I enter the exact coordinates of one of my nodes, and then a distance that encompasses all nodes.
I only retrieve some of the nodes but not all of them.

I have pager set to no limitations.
filters otherwise are correct.

Seems like this should be easily reproducible.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

tpainton’s picture

Ah.. it seems that the issue is that the filter fails for anything under 10Km??

tpainton’s picture

see following post.

tpainton’s picture

Okay, I THINK I figured this out. (I just couldn't walk away) If you use the Google Maps widget to select the location there is a chance that location will not be returned in the proximity search, even though it should be. I am not sure exactly why, but I did correct the problem by looking at the field's table. I noticed that the nodes that were not being returned had larger precision values in some fields, specifically the sin, cos, rad fields. When I rounded these down to match other fields from other nodes that WERE being returned, the problem subsided. Purely by coincidence, I decided to go with the Lat/Long fields, instead of the Google Map Widget and my problem SEEMS to be fixed when adding new nodes using the lat/long fields instead of the Google Maps

I re-examined the SQL table, and it appears now that the cos, sin, rad fields are all holding floats that have equal precision.

I would say to reproduce this bug, try creating some nodes using the google map widget and some using the lat/long fields only and my prediction is that you will have issues with the Google Map created nodes..

I am young into this and still testing, but it's all I have so far.

tpainton’s picture

Also, I am now able to search proximities < 10km, where before I was not when using the Google Maps Widget.

tpainton’s picture

Unfortunately this module is plagued with a problem. It does not consistently return nodes within the specified search parameters. I had thought that I had figured out that it was in some way related to widget, (See above) but alas, it has nothing to do with this. I have one node set at 44.225053, -88.577905. If I utilize the following view..

$view = new view;
$view->name = 'prox';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'prox';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Proximity';
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['query']['options']['query_comment'] = FALSE;
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'node';
/* Field: Content: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['label'] = '';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 0;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 0;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 1;
/* Sort criterion: Content: Post date */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'node';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'trophy' => 'trophy',
);
/* Filter criterion: Geolocation: Distance */
$handler->display->display_options['filters']['field_location_distance']['id'] = 'field_location_distance';
$handler->display->display_options['filters']['field_location_distance']['table'] = 'field_data_field_location';
$handler->display->display_options['filters']['field_location_distance']['field'] = 'field_location_distance';
$handler->display->display_options['filters']['field_location_distance']['value'] = array(
  'latitude' => '1',
  'longitude' => '1',
  'search_distance' => '100',
  'search_units' => 'kilometers',
);
$handler->display->display_options['filters']['field_location_distance']['exposed'] = TRUE;
$handler->display->display_options['filters']['field_location_distance']['expose']['operator_id'] = '';
$handler->display->display_options['filters']['field_location_distance']['expose']['label'] = 'Distance';
$handler->display->display_options['filters']['field_location_distance']['expose']['use_operator'] = FALSE;
$handler->display->display_options['filters']['field_location_distance']['expose']['operator'] = 'field_location_distance_op';
$handler->display->display_options['filters']['field_location_distance']['expose']['identifier'] = 'field_location_distance';

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'proximity';

With the following paramters, 44.225121, -88.577921 and 100Km as distance, I get nothing back....right now. Oh it worked great for the last week, but now, nothing, nada. Come to think of it, 2 weeks ago it wasn't working either. I made the mistake of assuming I had changed something but today, it was working at noon and now it's not working at 3pm and I have changed nothing on the drupal side of the server.

The module has a peculiar bug, and since I am no good at all with Views, I don't think I'm going to be the one to find it. Given that, and the fact that this thread hasn't gotten even a comment leaves be thinking that this might be semi abandoned, or near to it. I don't think I have any other options but to move EVERYTHING to OpenLayers Proximity.. which will take a few weeks since my Android project is rigged up to use this module.. Time to rewrite a bunch of java.

Funksmaname’s picture

any advance on this? I see a fantastic new widget has been added, where I put a bunch of issues that seem more related to this thread than that one... I'm still getting inconsistant results with proximity searches. Should I look elsewhere? it's so close, yet so far...

Funksmaname’s picture

Version: 7.x-2.0-alpha3 » 7.x-2.x-dev
Priority: Major » Critical
Funksmaname’s picture

In my case all nodes were added using the google widget so I don't think that's the issue.

It seems that results within a certain radius of the origination point are not being shown, while those further out within the proximity are. If I move the proximity away from the hidden nodes they re-appear and the ones near the new origin disappear... maybe this helps investigate the issue? If I move the origin away from all results and increase the proximity they all appear.

Funksmaname’s picture

I've been working someone far smarter than I am to try and track down the issue with proximity searches. We exported SQL queries from views and ran some search results directly in phpmyadmin.

It turns out that proximity is working, but due to inaccuracies in the formula being used, the results that are NOT, but SHOULD be included are shown as 'NULL' results in the field_geolocation_distance_filter column.

So results outside the proximity radius are not showing (which is good), and all those inside the search area with NULL are not showing... so to fix this we just need the SQL output to include be:

HAVING (( (field_geolocation_distance_filter < '10') OR (field_geolocation_distance_filter IS NULL)))

instead of HAVING (( (field_geolocation_distance_filter < '10')

If anyone has any ideas how we could do that with the module code and provide a patch that would be awesome. Unfortunately this is all beyond my tech level :)

RdeBoer’s picture

Re #9:

Not in a position to code right now, but the code generating the "HAVING" SQL is in /geolocation_proximity/handlers/geolocation_proximity_views_handler_filter_distance.inc, at the bottom of function query().

Following the suggestion in #9, the add_having() should probably become an add_having_expression() call, as used in views/handlers/views_handler_filter_group_by_numeric.inc

I may be able to have a look at this later.

Rik

RdeBoer’s picture

Re #9, #10

The patch below establishes what is suggested in #9.
The last line in geolocation_proximity/handlers/geolocation_proximity_views_handler_filter_distance.inc:

$this->query->add_having($this->options['group'], $this->field_alias, $filter_distance, $this->operator);

needs to be changed to:

$this->query->add_having_expression($this->options['group'], '(' . $this->field_alias . ' IS NULL) OR (' . $this->field_alias . $this->operator . $filter_distance . ')');

I may provide an official patch later.

Rik

Funksmaname’s picture

Thanks Ric! This has made much better results - the ones previously lost are now showing.
This has made a huge difference but I'm still having an issue with some odd results with small proximity numbers (<1k) - need to test further to see what might be causing that.

RdeBoer’s picture

Title: All nodes within the distance specified are not returned. » Not all nodes within the distance specified are returned.

Glad to help.
If you have a more accurate formula to plug in, let me know.
IP Geolocation Views & Maps also has a distance formula. I don't know if it is any better than the one used by this module.
Rik

Funksmaname’s picture

do you think it would be worth trying that formula in this proximity search? I've no idea how difficult that would actually be but if you'd like to try I'd be happy to test it.

Thanks again! :)

RdeBoer’s picture

The formula used in "IP Geolocation Views and Maps" distance function is the same as the one used by the Location module.
On closer examination it differs from the on in Geolocation Proximity in only one aspect: the radius of the earth.

Both IPGV&M and Location include a correction for the flattening of the earth at the poles.
If you want to introduce this you can do so by changing this line in geolocation_proximity.module

  $earth_radius = 6371;

to

  $earth_radius = 0.001 * ip_geoloc_earth_radius($filter_lat);

However, this won't change the values you get for locations in NZ by more than a few tens of meters or so and certainly won't alleviate the numerical instability symptoms you've seen for small distances.

I researched the topic a bit more and learned that for proximities of less than 1 km, the formula used (in all 3 modules) can become numerically unstable, if the precision of the numbers is limited. To counteract this, an alternative formula, the Haversine formula, is recommended, see http://en.wikipedia.org/wiki/Haversine_formula.
I did some tests with this but found that when decent precision is used the differences are again not dramatic (eg a distance of 88 meters became 91 meters).

So then I had a look at the database and found that field_geolocation_lat_sin, field_geolocation_lat_cos and field_geolocation_lng_rad are stored as floats, not doubles... this may be a contributing factor in the symptoms you're seeing. Strangely, the original lat and lng ARE stored as doubles (i.e. big floats in the schema in geolocation.install).

So my suggestion is to change these 3 columns in the database table field_data_field_geolocation to use doubles rather than floats. You can do this in the phpMyAdmin console, for instance.

In PHP we need to execute once, something like this in hook_update_N(). This assumes that you named the Geolocation field, 'field_geolocation'.

  $names = array('field_geolocation_lat_sin', 'field_geolocation_lat_cos', 'field_geolocation_lng_rad');
  foreach ($names as $name) {
    db_change_field('field_data_field_geolocation', $name, $name, array('type' => 'float', 'size' => 'big', 'not null' => TRUE));
  }

Note that IF this works, the improvement will most likely only occur for newly added points, or current points that are re-saved.

derjochenmeyer’s picture

Great effort! Any patch resolving this issue is welcome. #11 is a good first step. Changing the pre calculated values to doubles in the DB seems good as well. The hook_update_N() could re-calcualte all existing values too.

tpainton’s picture

Glad to see some someone was having same issues. I was going a little crazy. I don't think however that the problem is only as simple as the wrong calculation, that would yield the wrong results all the time. At least for me, there were times when I was returning all nodes, and others when I would return zero nodes. I ended up moving the entire process to local client using JAVA and that is working perfectly for me. I would say that until this is fixed, there should be something somewhere that saves people a lot of time from installing a proximity view, that doesn't currently function. I wasted weeks of time on this.

Funksmaname’s picture

I lost you at hook_update_N() - is this something I should put in template.php?
Should the hook be:

geolocation_update_7100(
    $names = array('lat_sin', 'lat_cos', 'lng_rad');
    foreach ($names as $name) {
        db_change_field('field_data_field_geolocation', $name, $name, array('type' => 'float', 'size' => 'big', 'not null' => TRUE));
  }
)

Sorry to be a dunce :)

RdeBoer’s picture

@Funksmaname, #18:

Yes pretty much, well done! But we'll have to check that we have the field and table names right. I'm happy to verify that later.
And the code should not go in template.php, but in geolocation/geolocation.install.
It will then run ONCE when users install the new version of Geolocation and run the update.php script.
As mentioned by derjochenmeyer, #16, the function should be extended to run through all existing geolocations to recalculate the numbers and put them back as doubles.

For now, I suggest that on your sytem you use the UI console of phpMyAdmin (or similar database tool). Find table field_data_field_geolocation and change the type of the 3 columns in question from float to double. Then edit+save a few of the suspicious existing nodes to get them stored with double precision.
If that does not work there's little point in writing code that does the same thing!

Rik

Funksmaname’s picture

Hey Rik,
I changed the db column type in phpmyadmin.

Edit+save isn't enough - some results still had only 6 or 7 decimal places when resaved or trying to refetch the same address. Moving the locator manually by a millimeter suddenly resulted in 14 decimal places, even when the fetched address remained the same.

When doing address lookups instead of moving the pin, you get the short decimal place results - this probably needs changing in the fetching code?? I suspect if the address -> long/lat conversion always results in 6 or 7 decimal places as the point of origin, this could create problems with results even if they are all 14 decimal places? The lat particularly seems to only ever have 7 decimal places on an address conversion.

As I go through these and change them, it does appear that the ones invloved in the strange results are the short decimal place results.

This is definately getting better all the time - things that were before missing are now showing, but there are still odd results - like there's one node that shows up further than another that is hidden, but now that all of the nodes have a 14 decimal long/lat the point of origin being 6 might be causing the issue??

Funksmaname’s picture

when I do long/lat proximity tests (not using the new widget, and using all 14 decimal places) I still get strangness.

doing 0.1km from an exact long/lat of a node results in 3
but doing the same from one of the 2 results only brings 1 in 1 case, and brings 2 in another case.
The 1 result that shows in all 3 searches above is one that is most definately further than 0.1k from the others, and is the one previously mentioned magically appearing further than other hidden results.

This is starting to sound like the instructions to use the holy hand grenade!

I don't expect my users would ever search <1k from anywhere, but it's become a matter of principle! :P

RdeBoer’s picture

Nice work and analysis!

We may be able to get away with single precision (float) for the lat/long themselves. It's those 3 intermediate proximity calculation results that require double precision to avoid numerical instability at short distances.

One thing to be aware of is that at all times the reference point entered is the same for 1) distances calculated in the Views fields and 2) the Views filter. So when you change the reference point for the fields you have to change the reference point for the filter to be the same.

Funksmaname’s picture

we cross posted Rik, not sure if #21 helps?
in actual fact, none of the included results in #21 are within 0.1k of eachother, and doing the same test on other results gives 1 result in return... so maybe the results I keep trying with are corrupt in some way??

Even going to 0.01k on some nodes gives 2 results... which is impossible.

RdeBoer’s picture

Also, the proximity area is circular around the reference point, not square or rectangular....

Funksmaname’s picture

dont suppose there's a way to plot the result radius to make it clearer? :S

RdeBoer’s picture

haha....

Actually you could approximate the area with a polygon.
Leaflet and OpenLayers have it. But not all Drupal mapping modules support these advanced features.

Funksmaname’s picture

ok, from further testing, other than a phantom node I think the results are more reliable than before (particularly with proximity >1) - though my tests are limited to the 19 nodes I have in the system currently so insufficient to be totally conclusive.

I suggest these changes be patched in for user testing.

RdeBoer’s picture

Ok, have had a stab at writing an "db update function" in geolocation.install, but don't think it's as simple as the hook_update_N() implementation outlined in #18....
.... because we're dealing with an update to a field (not a basic table), a field by a name we don't know as it's created through the UI....

Is there a hook_update_field_N() or something....?

RdeBoer’s picture

Further on #28...
To update all lat_sin, lat_cos and lng_rad values to double append this code to the file geolocation/geolocation.install and run .../update.php.
This goes through all revisions of all instances in all entity types of the Geolocation field(s) on your system.
Turned out to be a bit more involved than I'd hoped!

Note that the comment above function geolocation_update_7120() will be shown when the user is shown the pending updates when running update.php. So suggest to keep it there.

Rik

/**
 * 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;
}
RdeBoer’s picture

Status: Active » Patch (to be ported)

Summary

Created issue #1858592: Increase precision of auxiliary data columns in the Geolocation module queue repeating the above patch in #29 as the conversion to double precision is a Geolocation module issue, not a Geolocation Proximity issue.

However in summary of all of the above you're likely to also need the 1-line patch from #11 in this thread, which is to be applied to the Geolocation Proximity module.

The two patches together should make your Geolocation+Proximity combo a lot more robust, especially in cases where distances less than a few hundred meters are involved.

Rik

Funksmaname’s picture

Hey Rik,
Just to clarify - are the contents of this patch stuff I've already done or should I run the patch to improve things further? Thanks for your contribution! you're the man :)

RdeBoer’s picture

@Funksmaname, #31,
If you've gone through all nodes with Geolocation Fields and updated and saved them, after you changed those 3 db columns to double precision, then you're fine.
If you do decide to run the patch, ie update.php, you should remain fine.

Funksmaname’s picture

One thing though, the google maps input widget still results in 'short' decimal places when you do an address search (particularly lat). Wiggling the location marker gets a long result for the same address - but this isn't a step I can get users to go through! is there any way to make the 'get address' button result in a long decimal place result?

RdeBoer’s picture

I don't have the Google maps input widget handy on my system, so can't investigate right now. Somebody else who wants to spend some time on it?

But as as mentioned in #22, I feel single precision for the lat/long themselves is probably ok. It's those 3 intermediate calculation results that require double precision to avoid numerical instability at short distances. Do these show full precision when you inspect the associated database row in phpMyAdmin?

Funksmaname’s picture

nah, the stored long/lat in the db have 7 dp's - but if you think this doesn't matter as long as the calculation results are more accurate than that's all good :)

sorry i may have misunderstood, the numbers in lat_sin, lat_cos and lng_rad have 14 dp's in the db!

RdeBoer’s picture

Then you should be fine...

derjochenmeyer’s picture

Title: Not all nodes within the distance specified are returned. » Fix distance filter: some nodes within the specified distance are NOT returned
FileSize
847 bytes

Issue mentioned in #30 is fixed:
#1858592: Increase precision of auxiliary data columns

Here is the patch from #11 for testing...

derjochenmeyer’s picture

Status: Patch (to be ported) » Needs review
derjochenmeyer’s picture

Maximus8’s picture

Issue summary: View changes

Hey guys,

will there be any more patches fixing the problem with the filter? I use the latest version of geolocation and geolocation proximitiy but nodes which do have the same coordinates (-> distance should be 0) as the reference-coordinates are not yet shown in my view.
Otherwise i will just try to change the lat-value of the coordinates of each user (my reference-coordinates inserted with hook) in the database by 100 meters to avoid having the same searched coordinates as the reference point...dont know if its working, but if a user declares his location somewhere and doesnt see the nodes which are located at the same point, is kind of frustrating.

Thanks for information!

Max