Integrating SQLite data with Google Maps in a Flask application

Other Questions JavaScript
1

I’m working on a Flask app that needs to show data from SQLite on a Google Map. Here’s what I’ve got so far:

@app.route('/mapview', methods=['GET'])
def display_map():
    db = sqlite3.connect('mydata.db')
    cursor = db.cursor()
    lats = cursor.execute('SELECT lat FROM locations').fetchall()
    longs = cursor.execute('SELECT lng FROM locations').fetchall()
    return render_template('map.html', latitudes=lats, longitudes=longs)

The Google Maps example adds points like this:

function getMapPoints() {
    return [
        new google.maps.LatLng(40.7128, -74.0060),
        new google.maps.LatLng(34.0522, -118.2437)
    ];
}

I want to use my database info instead. I tried this in my map.html:

function getMapPoints() {
    var mapPoints = [];
    // How do I add points from Flask variables here?
    return mapPoints;
}

I’m stuck on how to get the lat and long data from Flask into this JavaScript function. Any ideas?

2

hey, i’ve done something like this before. u can use jinja2 to pass the data directly to ur javascript. try this:

function getMapPoints() {
    return {{ coordinates | tojson | safe }};
}

in ur flask route, just make sure to format the coordinates correctly:

coordinates = [{'lat': lat, 'lng': lng} for lat, lng in cursor.execute('SELECT lat, lng FROM locations')]

should work like a charm!

3

I’ve implemented a similar feature in a recent project. Here’s an efficient approach:

Modify your Flask route to return JSON data instead of rendering a template:

@app.route('/mapdata')
def get_map_data():
    with sqlite3.connect('mydata.db') as db:
        cursor = db.cursor()
        coordinates = cursor.execute('SELECT lat, lng FROM locations').fetchall()
    return jsonify(coordinates)

Then, in your map.html, use AJAX to fetch the data:

function getMapPoints() {
    return fetch('/mapdata')
        .then(response => response.json())
        .then(data => data.map(coord => new google.maps.LatLng(coord[0], coord[1])));
}

This approach separates concerns, improves performance by loading data asynchronously, and allows for easier updates without refreshing the entire page.

4

I’ve tackled a similar issue in one of my projects. Here’s what worked for me:

In your Flask route, instead of passing separate lists for latitudes and longitudes, combine them into a single list of coordinates:

coordinates = cursor.execute('SELECT lat, lng FROM locations').fetchall()
return render_template('map.html', coordinates=coordinates)

Then in your map.html, you can use Jinja2 templating to pass the data to JavaScript:

function getMapPoints() {
    var mapPoints = [
        {% for coord in coordinates %}
            new google.maps.LatLng({{coord[0]}}, {{coord[1]}}),
        {% endfor %}
    ];
    return mapPoints;
}

This approach keeps your data processing in Python and seamlessly integrates it with your JavaScript. It’s cleaner and more efficient than trying to manipulate separate lat/long lists in JS.

Remember to properly sanitize your inputs and use parameterized queries if you’re accepting user input for your SQL queries. Also, consider paginating your results if you’re dealing with a large dataset to improve performance.