Views Exposed Filter for Proximity SQL error

@joelinvisible, can you provide an export of your view?

Off the cuff, I suspect that it's an issue with null values in your geocoded data, but I don't have anything to back that up yet. Do you have any blank Address fields with your data?

This should be fixed with the commit I did today for #1614344: Make input optional in proximity searches - fix for Notice

http://drupalcode.org/project/geofield.git/commit/8e1ff0e

If this isn't the case, please reopen.

Looks like this problem resurfaced after you revamped your filter handler code in late 2012. Uploaded a patch which solves the problem for me. Issue was that on initial display there were empty latitude and longitude values in $options array.

#5 - agreed, have same issue. Patch did not apply cleanly but I replicated your fix against our repo and it fixed the issue.

I already have the same problem.

Here is my view:

$view = new view();
$view->name = 'geo_test';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Geo test';
$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'] = 'Geo test';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['use_more_text'] = 'altro';
$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['exposed_form']['type'] = 'basic';
$handler->display->display_options['exposed_form']['options']['submit_button'] = 'Applica';
$handler->display->display_options['exposed_form']['options']['reset_button_label'] = 'Ripristina';
$handler->display->display_options['exposed_form']['options']['exposed_sorts_label'] = 'Ordina per';
$handler->display->display_options['exposed_form']['options']['sort_desc_label'] = 'Disc';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Comportamento se non ci sono risultati: Globale: Area di testo */
$handler->display->display_options['empty']['area']['id'] = 'area';
$handler->display->display_options['empty']['area']['table'] = 'views';
$handler->display->display_options['empty']['area']['field'] = 'area';
$handler->display->display_options['empty']['area']['empty'] = TRUE;
$handler->display->display_options['empty']['area']['content'] = 'Nessun risultato trovato.';
$handler->display->display_options['empty']['area']['format'] = 'full_html';
/* Campo: Contenuto: Geofield - proximity */
$handler->display->display_options['fields']['field_geofield_distance']['id'] = 'field_geofield_distance';
$handler->display->display_options['fields']['field_geofield_distance']['table'] = 'field_data_field_geocode';
$handler->display->display_options['fields']['field_geofield_distance']['field'] = 'field_geofield_distance';
$handler->display->display_options['fields']['field_geofield_distance']['precision'] = '0';
$handler->display->display_options['fields']['field_geofield_distance']['geofield_proximity_manual'] = array(
  'lat' => '',
  'lon' => '',
);
$handler->display->display_options['fields']['field_geofield_distance']['geofield_proximity_entity_url_field'] = 'field_geocode';
$handler->display->display_options['fields']['field_geofield_distance']['geofield_proximity_current_user_field'] = 'field_geocode';
$handler->display->display_options['fields']['field_geofield_distance']['radius_of_earth'] = '6371';
/* Campo: Contenuto: Titolo */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
/* Criterio di ordinamento: Contenuto: Geofield - proximity */
$handler->display->display_options['sorts']['field_geofield_distance']['id'] = 'field_geofield_distance';
$handler->display->display_options['sorts']['field_geofield_distance']['table'] = 'field_data_field_geocode';
$handler->display->display_options['sorts']['field_geofield_distance']['field'] = 'field_geofield_distance';
$handler->display->display_options['sorts']['field_geofield_distance']['source'] = 'exposed_geofield_filter';
$handler->display->display_options['sorts']['field_geofield_distance']['geofield_proximity_manual'] = array(
  'lat' => '',
  'lon' => '',
);
$handler->display->display_options['sorts']['field_geofield_distance']['geofield_proximity_entity_url_field'] = 'field_geocode';
$handler->display->display_options['sorts']['field_geofield_distance']['geofield_proximity_current_user_field'] = 'field_geocode';
/* Criterio del filtro: Contenuto: Pubblicato */
$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;
/* Criterio del filtro: Contenuto: Tipo */
$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(
  'clienti' => 'clienti',
);
/* Criterio del filtro: Contenuto: Geofield - proximity */
$handler->display->display_options['filters']['field_geofield_distance']['id'] = 'field_geofield_distance';
$handler->display->display_options['filters']['field_geofield_distance']['table'] = 'field_data_field_geocode';
$handler->display->display_options['filters']['field_geofield_distance']['field'] = 'field_geofield_distance';
$handler->display->display_options['filters']['field_geofield_distance']['operator'] = '<=';
$handler->display->display_options['filters']['field_geofield_distance']['value'] = array(
  'distance' => '300',
  'distance2' => 200,
  'unit' => '6371',
  'origin' => '',
);
$handler->display->display_options['filters']['field_geofield_distance']['exposed'] = TRUE;
$handler->display->display_options['filters']['field_geofield_distance']['expose']['operator_id'] = 'field_geofield_distance_op';
$handler->display->display_options['filters']['field_geofield_distance']['expose']['label'] = 'Geofield - proximity';
$handler->display->display_options['filters']['field_geofield_distance']['expose']['operator'] = 'field_geofield_distance_op';
$handler->display->display_options['filters']['field_geofield_distance']['expose']['identifier'] = 'field_geofield_distance';
$handler->display->display_options['filters']['field_geofield_distance']['expose']['required'] = TRUE;
$handler->display->display_options['filters']['field_geofield_distance']['expose']['remember_roles'] = array(
  2 => '2',
  1 => 0,
  3 => 0,
  4 => 0,
);
$handler->display->display_options['filters']['field_geofield_distance']['source'] = 'geocoder';
$handler->display->display_options['filters']['field_geofield_distance']['geofield_proximity_entity_url_field'] = 'field_geocode';
$handler->display->display_options['filters']['field_geofield_distance']['geofield_proximity_current_user_field'] = 'field_geocode';

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['path'] = 'geo-test';
$translatables['geo_test'] = array(
  t('Master'),
  t('Geo test'),
  t('altro'),
  t('Applica'),
  t('Ripristina'),
  t('Ordina per'),
  t('Asc'),
  t('Disc'),
  t('Nessun risultato trovato.'),
  t('Geofield - proximity'),
  t('.'),
  t(','),
  t('Titolo'),
  t('Page'),
);

Ok, I solved! Maybe my solution should help someone else... That is:

In my view I added the Geocode-proximity field to fields list. So I set the "Source of Origin Point" to "Manually enter Point" as was specified in a couple of articles online: Wrong!

Solution:
"Source of Origin Point" have to be set to "Geocoded Location"!!!

P.S. I'm using latest module version, the one published on GIT.

I got stuck with this error for a couple of hours but finally figured out my issue.

It seems the "Geocoding Service" drop down for the proximity exposed filter reverts to "KML" after the view is saved.

When the view is saved I need to make sure that "Google Geocoder" is selected instead of "KML".

Would be great if there were some warning or alert rather than a strange error message.

@gianfrasoft & @sleepyscene have different solutions.

Is it now safe to use this with the latest stable release (7.x-2.0)?

I do hope that there's no need to keep using the git version.

I have both a map and list view. I'm trying to apply a 'sort' so the nearest location is show first in the list. I'm not sure I can include a 'distance' field - otherwise I could sort on that. Does anyone have any ideas on how to implement a sort like this?

I received this error after setting my "Source of Origin Point" to "Manually enter Point" and did not provide a default value. Once I added a default value, the error was gone.

I agree with mgifford, it would be nice to have some sort of notice instead of the error. Setting to needs work re: comment 6.

Stopshinal - There is a field-proximity sort handler available that you can use in conjunction with the exposed filter.

Here's a rerolled patch that applies cleanly. However, it I still receive the error on view edit with the patch applied.

I have to sleep now so and I just lost the page once so sorry I have to be quick.

In geofield/views/proximityplugins/geofieldProximityManual.inc the function getSourceValue($views_plugin) is, on face value at least, meant to check whether a submitted value or otherwise the manual defaults have been set or not in $views_plugin->value and if not, simply return 0 (set previously in the page, $views_plugin->options['geofield_proximity_manual']['lon']).

The problem is (isset($views_plugin->value) is always true so if the values in $views_plugin->value['origin']['lat'] or $views_plugin->value['origin']['lon'] are not set mySQL will attempt a RADIANS function on ''. We can check it's not == ''. We cannot simply check !empty since lat/lng should be able to be 0.

public function getSourceValue($views_plugin) {
    return array(
      'latitude' => (isset($views_plugin->value) && $views_plugin->value['origin']['lat'] !== '') ? $views_plugin->value['origin']['lat'] : $views_plugin->options['geofield_proximity_manual']['lat'],
      'longitude' => (isset($views_plugin->value) && $views_plugin->value['origin']['lon'] !== '') ? $views_plugin->value['origin']['lon'] : $views_plugin->options['geofield_proximity_manual']['lon'],
    );
	}

I don't believe the previous patches should be performed since then all results will be shown if either the latitude or longitude is 0 (i.e. PHP's special definition of empty). This excludes a lot of equatorial countries and the people who lives exactly on Greenwich's longitude :)

I could be wrong but I need sleep now (I've already fixed the !empty part above after some rest :)).

I haven't check all the other plug-ins either.

Moving this to "needs work" - the author of the last patch still reported "I still receive the error on view edit with the patch applied."

This does need to be reviewed though. The patch is over a year old and it's quite likely that other things have changed since then.

This fixed the SQL error and the patch rolled from the suggestion at comment #15.

@gianfrasoft

Awesome! :)

I had an issue with this module containing the same error when the proximity location data was not filled in. My issue was with the manual filter plugin, and after applying this patch the errors went away.

For context, I am using the exposed filter to pass back data to the view after getting the current location of the user. If I put a default value in the filter, then on the first page load, the wrong data would be displayed...or if getting their data fails at least the default view without geolocation works and goes to a fallback.

So, since this patch works for me and seems to be a harmless change with just more error checking, I'm going to mark it as RTBC.

Forgot to say the patch I used was #19.

I received this error after setting my "Source of Origin Point" to "Manually enter Point" and did not provide a default value. I don't believe I have a use case for this option but I just wanted to report that I received the error after successfully patching stable and dev versions.

As per #25, I think this needs more reviews.