Limiting MySQL query to current view

This takes off where This page leaves you
It's assumed you have a map that can take data from the database and display them on the maps.
This way uses XML formatted data, then reads the attributes with the GXmlHTtp methods.
the drawback with just querying the database and plotting the marks is you will have marks that out of view. too many marks can slow down rendering the page.
Notice in the map to the right, if you zoom out, you should see the other marks that were off screen, yet plotted.
one solution would be to grab the map bounds, then use the lat and long of the border as limits for your query.
Zoom back in to Portland area
Now, if you click on 'two' below, it should have queried just what the screen is viewing right now. To verify it, zoom out. Notice, there are no marks in the area that wasn't viewed before. You can zoom in and out, click one and two to see the difference. 'two' and 'one' are the same query, except 'two' is limited to the viewing area.

okay, how to do it.
If you take the sample pages from the first tutorial, there are just a couple of changes to be done.
If you check the source of this page, you'll see the code for button one and two have the same format.
In maps.js I've added a couple of lines: 
		mapbounds=map.getBounds();
		var mapne=mapbounds.getNorthEast();
		var mapsw=mapbounds.getSouthWest();
		var mapSWLat=mapsw.lat();
		var mapSWLng=mapsw.lng();
		var mapNELat=mapne.lat();
		var mapNELng=mapne.lng();

		url = url+"&nelat="+mapNELat+"&nelng="+mapNELng+"&swlat="+mapSWLat+"&swlng="+mapSWLng;
I create a mapbounds object with the current bounds, then take out the 2 diagonal corner Lats and Longs. The bottom line, tacks on the coordinates to the 'url' variable, then gets passed to the 'marks.php' file. That's all the changes that need to be made to the maps.js file. (in theory, we haven't altered the 'index.html' file at all.)
The next step is fairly easy. All it requires is to alter the marks.php file to read the coordinates being passed.
Somewhere in marks.php, you need to add the lines to grab the coordinates, I do this at the top of the php. 
$rlat=$_GET['nelat'];
$rlng=$_GET['nelng'];
$llat=$_GET['swlat'];
$llng=$_GET['swlng'];
Now, you have the variables that should have been read in, now, just alter the query to look for them. 
$query = " SELECT name,lat,lng from tutor WHERE   lat > $llat and lat < $rlat and lng > $llng and lng < $rlng  limit 30  ";
Essentially, it checks to be sure the latitude is greater than the left latitude (SW latitude), and less than the right latitude (NE Latitude). Same thing for the longitudes.
That's it. Now, what's the problem? If you're in the US, there shouldn't be a problem. The problem arises when you have a view that spans the international dateline. If you have a view in the US, then the SW corner coordinates will have lower values for Latitude and Longitude, than the NE corner. for instance, the bounds of a view in the US could have SW(44,-120)...NE(47,-117). The query will work fine for that. The values increase from the lower left corner, to the upper right corner of the view.
When the view spans the dateline, you could have SW(44, 170) NE(47,-170). The values do not increase in value, in fact, you have a really screwed up query you have to do. My solution follows.
I have two queries for limiting based on Lat and Long. The normal query like above, and a second query for when the view spans the dateline. First, I check to be sure the left longitude is less than the right longitude. If it is, I do the normal query.
If the left longitude is greater than the right longitude, then the view is spanning the dateline and the second query is assigned instead. With the (44,170) and (47,-170), corners, I check to see if the longitude is between 170 and 180 *OR* between -170 and -180. (170 and -170 were read in to the variables)
You don't have the problem with the latitude. 
if( $llng < $rlng){
	$query = " SELECT name,lat,lng from tutor WHERE   lat > $llat and lat < $rlat and lng > $llng and 
	lng < $rlng and mag > '$searchitem' limit 30  ";
		}
else {
	$query = " SELECT name,lat,lng from tutor WHERE   lat > $llat and lat < $rlat and ((lng > -180 and 
	lng < $rlng)or(lng<180 and lng>$llng)) and mag > '$searchitem'  limit 30  ";

		}

Feel free to link to this page whereever you like.

Feel free to email me with questions, comments or any gripes you have about this page. roger.in.eugene@gmail.com


Tutorial for altering markers based on dates
Here's a page for help inserting images into a MySQL database.

Page that grabs quake data from a database then displays earthquakes that have happened recently
Web hosting