Asked  7 Months ago    Answers:  5   Viewed   36 times

Ok I have a database with about 1800 rows, each has a column lat and long, what I am trying to do, it query against Google Maps V3 .getBounds The result is something like ((33.564398518424134, -79.38014701875002), (35.375726155241175, -76.08424858125002)) What I want to do, is get every record whose lat & lng in the database is within those bounds. Any suggestions?

Im thinking I would have to extrapolate the other 2 corners of the map, since .getBounds only contains 2 points (2 corners of the map I suppose), and I would want to be within 4 points. If I did that I would end up with an array something like...Now correct me if I am wrong but this should be NW, NE, SW, SE corners of the map, in lat-lng.

33.564398518424134, -79.38014701875002
33.564398518424134, -76.08424858125002
35.375726155241175, -79.38014701875002
35.375726155241175, -76.08424858125002

Ok, so If I have this data, how would I build a query to get the rows that are within these coordinates? Table is called tilistings - columns are lat and lng ...also if there is an easier way, or I am just crazy with my methodology, please feel free to let me know.

Thanks

 Answers

58

if from Google: ( (a, b), (c, d) )

SELECT * FROM tilistings WHERE lat > a AND lat < c AND lng > b AND lng < d

Wednesday, March 31, 2021
 
DMTintner
answered 7 Months ago
60

This is how it should be (sorry for the indentation):

<?php
$conn = mysql_connect("localhost:4040", "xxxx", "xxxx") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
?>

    <html>
    <head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
        <title>Google Maps</title>
        <style type="text/css">
            body { font: normal 10pt Helvetica, Arial; }
            #map { width: 350px; height: 300px; border: 0px; padding: 0px; }
        </style>
        <script src="http://maps.google.com/maps/api/js?key=xxxxxxxxxxxxxxx&sensor=false" type="text/javascript"></script>
        <script type="text/javascript">
            //Sample code written by August Li
            var icon = new google.maps.MarkerImage("http://maps.google.com/mapfiles/ms/micons/blue.png",
                       new google.maps.Size(32, 32), new google.maps.Point(0, 0),
                       new google.maps.Point(16, 32));
            var center = null;
            var map = null;
            var currentPopup;
            var bounds = new google.maps.LatLngBounds();
            function addMarker(lat, lng, info) {
                var pt = new google.maps.LatLng(lat, lng);
                bounds.extend(pt);
                var marker = new google.maps.Marker({
                    position: pt,
                    icon: icon,
                    map: map
                });
                var popup = new google.maps.InfoWindow({
                    content: info,
                    maxWidth: 300
                });
                google.maps.event.addListener(marker, "click", function() {
                    if (currentPopup != null) {
                        currentPopup.close();
                        currentPopup = null;
                    }
                    popup.open(map, marker);
                    currentPopup = popup;
                });
                google.maps.event.addListener(popup, "closeclick", function() {
                    map.panTo(center);
                    currentPopup = null;
                });
            }           
            function initMap() {
                map = new google.maps.Map(document.getElementById("map"), {
                    center: new google.maps.LatLng(0, 0),
                    zoom: 14,
                    mapTypeId: google.maps.MapTypeId.ROADMAP,
                    mapTypeControl: true,
                    mapTypeControlOptions: {
                        style: google.maps.MapTypeControlStyle.HORIZONTAL_BAR
                    },
                    navigationControl: true,
                    navigationControlOptions: {
                        style: google.maps.NavigationControlStyle.ZOOM_PAN
                    }
                });
<?php
$query = mysql_query("SELECT * FROM poi_example")or die(mysql_error());
while($row = mysql_fetch_array($query))
{
  $name = $row['name'];
  $lat = $row['lat'];
  $lon = $row['lon'];
  $desc = $row['desc'];



  echo("addMarker($lat, $lon, '<b>$name</b><br />$desc');n");

}

?>
 center = bounds.getCenter();
     map.fitBounds(bounds);

     }
     </script>
     </head>
     <body onload="initMap()" style="margin:0px; border:0px; padding:0px;">
     <div id="map"></div>
     </body>
     </html>

The issue is: addmarker in the call va addMarker in function definition (JS is case-sensitive)

Saturday, May 29, 2021
 
ranhan
answered 5 Months ago
58

Starting the query from the Module side and excluding with whereDoesntHave should work in this case:

$id = 1;
$modules = Module::whereDoesntHave('sites', function($q) use ($id){
    $q->where('site_id', $id);
})->get();
Saturday, May 29, 2021
 
jakubos
answered 5 Months ago
90

Use JSON. In your PHP file, just output:

echo json_encode($gracz);

And change your ajax handler to accept json data:

$.ajax({   
    type:   "POST",
    async:  false,   
    url:    "char_info2.php",   
    data:  { name: merk },   
    dataType: "json",  
    success:  function(data) {
        // Use data.max_pz or whatever here
    } 
}); 
Saturday, May 29, 2021
 
kat_indo
answered 5 Months ago
51

I've Got the right code here its working perfect. it has been updated to go to the current location as well because I've been messing with it since the fix.

import com.google.android.gms.common.ConnectionResult;
import com.google.android.gms.common.GooglePlayServicesUtil;
import com.google.android.gms.maps.CameraUpdateFactory;
import com.google.android.gms.maps.GoogleMap;
import com.google.android.gms.maps.SupportMapFragment;
import com.google.android.gms.maps.model.BitmapDescriptorFactory;
import com.google.android.gms.maps.model.LatLng;
import com.google.android.gms.maps.model.Marker;
import com.google.android.gms.maps.model.MarkerOptions;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.app.Dialog;
import android.location.Criteria;
import android.location.Location;
import android.location.LocationListener;
import android.location.LocationManager;
import android.os.Bundle;
import android.support.v4.app.FragmentActivity;
import android.util.Log;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;


public class GetLocalBus extends FragmentActivity implements LocationListener {
    private static final String LOG_TAG = "JsOn ErRoR";

    private static final String SERVICE_URL = "";

    protected GoogleMap mapB;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.get_local);
        setUpMapIfNeeded();

     // Getting Google Play availability status
        int status = GooglePlayServicesUtil.isGooglePlayServicesAvailable(getBaseContext());

        // Showing status
        if(status!=ConnectionResult.SUCCESS){ // Google Play Services are not available

            int requestCode = 10;
            Dialog dialog = GooglePlayServicesUtil.getErrorDialog(status, this, requestCode);
            dialog.show();

        }else { // Google Play Services are available   

            // Getting reference to the SupportMapFragment of activity_main.xml
            SupportMapFragment fm = (SupportMapFragment) getSupportFragmentManager().findFragmentById(R.id.mapB);

            // Getting GoogleMap object from the fragment
            mapB = fm.getMap();

            // Enabling MyLocation Layer of Google Map
            mapB.setMyLocationEnabled(true); 



             // Getting LocationManager object from System Service LOCATION_SERVICE
            LocationManager locationManager = (LocationManager) getSystemService(LOCATION_SERVICE);

            // Creating a criteria object to retrieve provider
            Criteria criteria = new Criteria();

            // Getting the name of the best provider
            String provider = locationManager.getBestProvider(criteria, true);

            // Getting Current Location
            Location location = locationManager.getLastKnownLocation(provider);

            if(location!=null){
                    onLocationChanged(location);
            }

            locationManager.requestLocationUpdates(provider, 20000, 0, this);
        }
    }

    @Override
    protected void onResume() {
        super.onResume();
        setUpMapIfNeeded();
    }

    private void setUpMapIfNeeded() {
        if (mapB == null) {
            mapB = ((SupportMapFragment) getSupportFragmentManager().findFragmentById(R.id.mapB))
                    .getMap();
            if (mapB != null) {
                setUpMap();
            }
        }
    }

    private void setUpMap() {

        new Thread(new Runnable() {
            public void run() {
                try {
                    retrieveAndAddCities();
                } catch (IOException e) {
                    Log.e(LOG_TAG, "Cannot retrive cities", e);
                    return;
                }
            }
        }).start();
    }

    protected void retrieveAndAddCities() throws IOException {
        HttpURLConnection conn = null;
        final StringBuilder json = new StringBuilder();
        try {
            // Connect to the web service
            URL url = new URL(SERVICE_URL);
            conn = (HttpURLConnection) url.openConnection();
            InputStreamReader in = new InputStreamReader(conn.getInputStream());

            // Read the JSON data into the StringBuilder
            int read;
            char[] buff = new char[1024];
            while ((read = in.read(buff)) != -1) {
                json.append(buff, 0, read);
            }
        } catch (IOException e) {
            Log.e(LOG_TAG, "Error connecting to service", e);
            throw new IOException("Error connecting to service", e);
        } finally {
            if (conn != null) {
                conn.disconnect();
            }
        }

        // Must run this on the UI thread since it's a UI operation.
        runOnUiThread(new Runnable() {
            public void run() {
                try {
                    createMarkersFromJson(json.toString());
                } catch (JSONException e) {
                    Log.e(LOG_TAG, "Error processing JSON", e);
                }
            }
        });
    }

    void createMarkersFromJson(String json) throws JSONException {

        JSONArray jsonArray = new JSONArray(json);
        System.out.print(json);
        List<Marker> markers = new ArrayList<Marker>();

        for (int i = 0; i < jsonArray.length(); i++) {

            JSONObject jsonObj = jsonArray.getJSONObject(i);
            System.out.print(jsonObj.getJSONArray("latlng"));
            Marker marker = mapB.addMarker(new MarkerOptions()
                .title(jsonObj.getString("business_name"))
                .position(new LatLng(
                        jsonObj.getJSONArray("latlng").getDouble(0),
                        jsonObj.getJSONArray("latlng").getDouble(1)))
                .icon(BitmapDescriptorFactory.fromResource(R.drawable.graphic_map_icon))
            );
            markers.add(marker);
        }
    }

    @Override
    public void onLocationChanged(Location location) {
        // Getting latitude of the current location
        double latitude = location.getLatitude();

        // Getting longitude of the current location
        double longitude = location.getLongitude();     

        // Creating a LatLng object for the current location
        LatLng latLng = new LatLng(latitude, longitude);

        // Showing the current location in Google Map
        mapB.moveCamera(CameraUpdateFactory.newLatLng(latLng));

        // Zoom in the Google Map
        mapB.animateCamera(CameraUpdateFactory.zoomTo(15));

    }

    @Override
    public void onProviderDisabled(String provider) {
        // TODO Auto-generated method stub

    }

    @Override
    public void onProviderEnabled(String provider) {
        // TODO Auto-generated method stub

    }

    @Override
    public void onStatusChanged(String provider, int status, Bundle extras) {
        // TODO Auto-generated method stub

    }
}

Also here is the PHP

<?php




    if
    (isset($_GET['lat']) && isset($_GET['lon'])&& isset($_GET['dist'])){



    //make a simple database connection
        $db = new mysqli();



     $lat = $_GET['lat'];

         $lon = $_GET['lon'];

         $dist = $_GET['dist'];




        $search_sql = "SELECT business_id, business_name, CONCAT_WS(",",business_lat, business_lon) AS latlng, ( 3959 * acos( cos( radians(37) ) * cos( radians( " . $lat . " ) ) * cos( radians( " . $lon . " ) - radians(-122) ) + sin( radians(37) ) * sin( radians( " . $lat . " ) ) ) ) AS distance

                        FROM somewhere HAVING distance < " . $dist . " ORDER BY distance LIMIT 0 , 20";






        $search_results = $db->query($search_sql);



        if($search_results->num_rows){



     while ($row = $search_results->fetch_assoc())
{
    $return_data[] = array(
                            'business_name' => $row['business_name'],
                            'latlng'        => explode(',', $row['latlng']),
                            'business_id'   => $row['business_id'],
                            'distance'      => $row['distance']
                            );
}//end while


        }

        else{

            $return_data[] = array();

        }//end if




        }else{


        $return_data[] = array();

    }



    $bd_json = json_encode($return_data);



    $db->close();



    echo $bd_json;





?>

xml layout

    <fragment android:id="@+id/mapB" 
        android:layout_width="fill_parent" 
        android:layout_height="250.0dip" 
        android:layout_alignParentTop="true" 
        android:layout_centerInParent="true" 
        class="com.google.android.gms.maps.SupportMapFragment"
        xmlns:android="http://schemas.android.com/apk/res/android" />

    <TextView 
        android:id="@+id/TextView1" 
        android:layout_width="fill_parent" 
        android:layout_height="wrap_content" 
        android:layout_below="@+id/mapB" />
</RelativeLayout>
Friday, September 24, 2021
 
Dance Party2
answered 1 Month ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :