Show Google Maps markers based on user's current map view

Question

In my web app, I am using psycopg2 to query a postgreSQL database that holds thousands of location points. Let's say they can be categorized as offices and schools.

There are about 5500 offices and 550 schools. Fetching all of the results at once overloads it and causes my query to be cancelled. Therefore, I want to display only about 100 results, based on the part of the map the user is currently viewing.

My map's default view is of the United States. I want the database to only get 100 results each (100 offices and 100 schools) at any given time. If a user were to zoom in, it would be more detailed--different markers would appear.

This is what I have so far:

@app.route('/_ajax', methods= ['GET'])
def points():
    myConnection = psycopg2.connect(host=hostname, user=username,
    password=password, dbname=database)
    cur = myConnection.cursor()

    bounds = request.args.get('bounds')

    officeID = []
    office_info = []
    cur.execute("""SELECT DISTINCT(o.office_id) from db_office o
                   WHERE o.location && ST_MakeEnvelope(left, bottom, right, top, 4326)
                   GROUP BY o.office_id LIMIT 100""")
    for row in cur:
        officeID.append(row[0])
    for o in officeID:
    cur.execute("""SELECT ST_Y(o.location::geometry), 
                   ST_X(o.location::geometry),
                   o.owner, o.office_name from db_office o""".format(o))
    for row in cur:
        office_info.append([row[0], row[1], row[2], row[3]])
    offices = dict(zip(officeID, office_info))

    schoolID = []
    school_info = []
    cur.execute("""SELECT DISTINCT(s.school_id) from db_school s
                    WHERE s.location && ST_MakeEnvelope(left, bottom, right, top, 4326)
                   GROUP BY s.school_id LIMIT 100""")
    for row in cur:
        schoolID.append(row[0])
    for s in schoolID:
        cur.execute("""SELECT ST_Y(s.location::geometry), 
                       ST_X(s.location::geometry),
                       s.owner, s.school_name from db_school s""".format(s))
    for row in cur:
        school_info.append([row[0], row[1], row[2], row[3]])
    schools = dict(zip(schoolID, school_info))

return jsonify(offices=offices, schools=schools)

My AJAX call, which plots the markers, looks like this:

 $SCRIPT_ROOT = {{ request.script_root|tojson|safe }};
 $(function() {
    $.getJSON($SCRIPT_ROOT + '/_ajax?bounds=' + map.getBounds().toUrlValue(),
    function(data) {

    var places = [];
    var marker;

    Object.keys(data.offices).forEach(function(key) {
        var offices = data.offices[key];
        places.push(new google.maps.LatLng(offices[0], offices[1]))
        marker = new google.maps.Marker({
            position: {lat: offices[0], lng: offices[1]},
            map: map
        });
    });

    Object.keys(data.schools).forEach(function(key) {
       var schools = data.schools[key];
       places.push(new google.maps.LatLng(schools[0], schools[1]))
       marker = new google.maps.Marker({
           position: {lat: schools[0], lng: schools[1]},
           map: map,
       });
    });
});

To summarize, this code fetches 100 of the offices and 100 of the schools and puts it into a dictionary, where the ids are the keys and the values are the information like latitude and longitude, etc.

This works and shows 100 results each but is not dependent on user location. How do change the results fetched by the database based on the user's view of the map?

I think I need to add a `WHERE` parameter in my query, and do something involving `ST_Within` maybe? But I don't know what exactly — Sarah, Jul 27 '17 at 19:19

eNVy · Answer 1 · 2017-07-27T20:18:49.577

0

You need to first get the bounds of the Google Map as described here.

Then use the bounds to do an intersects query in Postgres as described here.

SELECT DISTINCT(o.office_id) from db_office o
WHERE o.location && ST_MakeEnvelope(left, bottom, right, top, 4326);
GROUP BY o.office_id LIMIT 100

Ajax call from javascript:

$.getJSON($SCRIPT_ROOT + '/_ajax?bounds=' + map.getBounds().toUrlValue(), function (response) { 
    //do something with response here
});

edited Jul 27 '17 at 20:18

answered Jul 27 '17 at 19:31

eNVy

487
3
10

So according to that first post, I can access the bounds in my Javascript (using map.getBounds) but how do I reference that in my query? – Sarah Jul 27 '17 at 19:40
I'm still not sure how to get the bounds. The map.getBounds method is used in JavaScript, so how do I access the "left, bottom, right, top" values referenced in your code? – Sarah Jul 27 '17 at 19:54
How are you calling your ajax script? Just send the bounds as querystring parameters. I have updated the answer. – eNVy Jul 27 '17 at 20:04
I updated the question to show how I'm using my ajax call – Sarah Jul 27 '17 at 20:17
I updated the answer. On the python side you will have to read the querystring and parse the bounds and update your query. You can do it as described [here](https://stackoverflow.com/questions/11774265/how-do-you-get-a-query-string-on-flask) – eNVy Jul 27 '17 at 20:20
Ok so in my `@app.route(/_ajax)` I put `bounds = request.args.get('bounds')` Is that correct? I also get an error in the AJAX that `map.getBounds()` is undefined – Sarah Jul 27 '17 at 20:30
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/150350/discussion-between-envy-and-sarah). – eNVy Jul 27 '17 at 20:31
No, unfortunately :\ – Sarah Jul 27 '17 at 22:14

Show Google Maps markers based on user's current map view

1 Answers1