geolocation search in a specific range

[electricarts]

Hello, 

i'm brand new to PW (coming from Contao CMS). For a new project i really wanna use PW instead of something else.    But because i'm not that php-hero, i wanna ask if someone has done this before and could give me some tips how to do it in PW. We need a "Geolocation Search". I think i've to explain this because i don't know the right english term for this:

In the frontend, the user type his hometown's zip-code (5 digits here in Germany) in a field, choose from pre-defined distances (lets say 10 km, 20 km and 50 km) around his location, hit search and see which locations from an address database are inside that radius ( in a simple list or which would be very nice at a Google Map). 

I've already discovered Ryan's Map Marker Module which translates the address to useable Lat/Long coordinates. What i don't understand right now is how to filter the address entries for the specific distances and to display them in the frontend. Has somebody done something like this already in PW or is there a module which i didn't found till now? 

Mario 

P.S. Sorry for my terrible english. 

[marcus]

One way to go would be to leave all the range calculation and filtering to Google Maps infrastructure (given that you're planing to use Google Maps) and therefore the frontend/JavaScript. PW could in this case "only" provide the unfiltered data itself (as json, for example) and a jQuery plugin like http://www.bjornblog.com/web/jquery-store-locator-plugin could do the rest

Edited September 16, 2015 by marcus

[LostKobrakai]

Moved to dev talk

[felix]

Maybe this helps you. I'm using it in one of our sites for the exact same thing (you'll have to adjust the table names and get the coordinates of the given zip, though). The SQL Query uses the Haversine formula ( http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/ ) to find coordinates within the given radius.

	    /**
	     * Retrieves a list of address records residing within the given $radius of the given geo location
	     *
	     * @param float $lat
	     * @param float $lng
	     * @param int $radius
	     * @return array
	     */

		private function proximitySearch($lat, $lng, $radius = 20) {

	        $sql = <<<SQL

SELECT * FROM (
	SELECT *,
		(
			( 2 * 6378.2 ) *
			ATAN2(
				( SQRT(
					 POW( SIN( ( ( $lat * PI() / 180 ) - ( lat * PI() / 180 ) ) / 2 ), 2 ) +
					 COS( lat * PI() / 180 ) *
					 COS( $lat * PI() / 180 ) *
					 POW( SIN( ( ( $lng - lng ) * PI() / 180 ) / 2 ), 2 )
				       )
				),
				( SQRT(
					1 - (
					 POW( SIN( ( ( $lat * PI() / 180 ) - ( lat * PI() / 180 ) ) / 2 ), 2 ) +
					 COS( lat * PI() / 180 ) *
					 COS( $lat * PI() / 180 ) *
					 POW( SIN( ( ( $lng - lng ) * PI() / 180 ) / 2 ), 2 )
					)
				       )
				)
		    )
		)
		AS distance
	FROM field_address_team
	ORDER BY distance
) AS distributordistance
WHERE distance <= $radius;

SQL;

			$result = wire('db')->query($sql);
			if(!function_exists('mysqli_fetch_all')) {
				$retVal = [];
				while ($row = $result->fetch_assoc()) {
						$retVal[] = $row;
				}
			} else {
				$retVal = $result->fetch_all(MYSQLI_ASSOC);
			}
			return $retVal;

		}

Usage (in this specific example the radius is extended by the factor 3 until at least 3 locations are found or the radius has been extended 3 times which would be 540km [(((20*3)*3)*3)] with default radius settings ):

		public function getTeamsInRadius() {

			$teamsArr = new \PageArray();
			$searchCount = 0;

			while (count($teamsArr) < 3 && $searchCount <= 3 ) {

			    try {
			        // extend search radius by factor 3 as long as result set counts less than 3 hits
			        //
			      	$teams = $this->proximitySearch($this->latitude, $this->longitude, $this->radius * pow(3, $searchCount));

			      	foreach( $teams as $team ) {

			      		$teamsArr->append( wire('pages')->get($team['pages_id']) );
			      	}

			        $searchCount++;

			    } catch (\Exception $e) {
			        break;
			    }

			}
			return $teamsArr;

		}

A good idea when using this is to cache the results (see this blog entry for examples) for each entered ZIP until new entries are added to the database as the SQL Queries are pretty performance intensive. The benefit of this method is: You don't have to query Google Maps (or another GeoService) every time you're searching for locations.

[Pierre-Luc]

Hey, I agree with you! I've slowly started adding different geographic input fields and ways to request them to ProcessWire (don't hold your breath, it's far from over! — and the repo is currently private..)

I've been following stuff about MySQL, and the coming 5.7 release will be a big progress. Calculations on a sphere (no need for the harvestine formula), a handful of handy functions and lots of other things will finally make it finally possible to have relatively good quality GIS on MySQL. If you want to have some fun, you can read about some of the improvements on http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/. 

It's still pretty easy to build those queries on PW, build a custom SQL query, get pages ID and build a pageArray from those IDs. You can use common fields like float and text and grab geometry from the stored lon, lat, or even store polygons and lines in text fields. The onus is on you to keep those values sane though, so it's still some work but not impossible.