<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Welcome to Dava Ninja</title>
    <style>
        body {
            background-color: #002395; /* Dark blue background */
            color: white;
            font-family: Arial, sans-serif;
            margin: 0;
            display: flex;
            align-items: center;
            justify-content: space-between; /* Distribute content to the left and right */
            height: 100vh;
        }
        .left-container {
            display: flex;
            flex-direction: column;
            align-items: flex-start;
            padding: 20px;
        }
        .logo {
            width: 250px; /* Increased size of the logo */
            height: auto;
            position: absolute;
            top: 50px; /* Adjust as needed */
            left: 50px; 
        }
        .welcome-message {
            margin-top: 50px; /* Adjust spacing as needed */
            font-size: 50px;
        }
        .right-container {
            display: flex;
            align-items: center;
            justify-content: center;
            padding: 20px;
        }
        .extra-photo {
            width: 300px; /* Adjust size of the additional photo */
            height: auto;
            margin-right: auto;
        }
        .arrow-button {
            position: absolute;
            bottom: 20px;
            right: 20px;
            background-color: #007bff; /* Blue background for the circle */
            color: white;
            border: none;
            padding: 10px;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
            cursor: pointer;
        }

        .arrow-button svg {
            width: 16px; /* Size of the SVG chevron */
            height: auto;
            stroke: white;
            stroke-width: 2;
        }

        .arrow-button:hover {
            background-color: #0056b3; /* Darker blue on hover */
        }
    </style>
</head>
<body>
    <div class="left-container">
        <img src="{{ url_for('static', filename='dava_ninja_logo.png') }}" alt="Dava Ninja Logo" class="logo">
        <div class="welcome-message">Welcome to the maps portal of Dava Ninja</div>
    </div>
    <div class="right-container">
        <img src="{{ url_for('static', filename='Artboard.png') }}" alt="Artboard" class="extra-photo">
    </div>
    <button class="arrow-button" onclick="location.href='/map'">▶</button> <!-- Right arrow symbol -->
</body>
</html>

landing html

--------------------------------------

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Visited Locations</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f4f4;
            margin: 0;
            padding: 0;
            color: #333;
            text-align: center;
        }
        h1 {
            color: #0056b3;
        }
        .container {
            max-width: 1200px;
            margin: 0 auto;
            padding: 20px;
        }
        form {
            background-color: #fff;
            padding: 20px;
            border-radius: 8px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            margin-bottom: 20px;
        }
        label {
            display: block;
            margin: 10px 0 5px;
        }
        input[type="text"], input[type="number"] {
            width: 100%;
            padding: 8px;
            margin: 5px 0 10px;
            border: 1px solid #ccc;
            border-radius: 4px;
        }
        button {
            background-color: #007bff;
            color: white;
            border: none;
            padding: 10px 15px;
            border-radius: 4px;
            cursor: pointer;
        }
        button:hover {
            background-color: #0056b3;
        }
        .map-container {
            width: 100%;
            height: 500px;
            position: relative;
            border: 2px solid #ddd;
            margin: 20px auto;
            background-color: #fff;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        }
        #map {
            width: 100%;
            height: 100%;
        }
        .radio-group {
            display: flex;
            justify-content: center;
            margin: 20px 0;
        }
        .radio-group input[type="radio"] {
            display: none;
        }
        .radio-group label {
            margin: 0 10px;
            padding: 10px 20px;
            border: 2px solid #007bff;
            border-radius: 20px;
            cursor: pointer;
            transition: all 0.3s ease;
        }
        .radio-group input[type="radio"]:checked + label {
            background-color: #007bff;
            color: white;
        }
    </style>
    <link rel="stylesheet" href="https://unpkg.com/leaflet@1.7.1/dist/leaflet.css" />
    <script src="https://unpkg.com/leaflet@1.7.1/dist/leaflet.js"></script>
    <script>
        function initMap() {
            var map = L.map('map').setView([19.0760, 72.8777], 12);  // Center the map

            L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
                attribution: '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
            }).addTo(map);

            var locations = {{ locations | tojson | safe }}; // Pass locations data from Flask

            console.log("Fetched locations from server:", locations); // Log locations data for debugging

            function addMarkers(filter) {
                map.eachLayer(function(layer) {
                    if (!!layer.toGeoJSON) {
                        map.removeLayer(layer);
                    }
                });

                locations.forEach(function(location) {
                    if (filter === 'both' || location.type === filter) {
                        var color = location.type === 'live' ? 'green' : 'red';
                        if (location.coordinates[0] !== null && location.coordinates[1] !== null) {
                            L.marker(location.coordinates, {
                                icon: L.icon({
                                    iconUrl: 'http://maps.google.com/mapfiles/ms/icons/' + color + '-dot.png',
                                    iconSize: [32, 32]
                                })
                            }).addTo(map).bindPopup('Pincode: ' + location.pincode + '<br>Visits: ' + location.count);
                        }
                    }
                });
            }

            // Radio button event listeners
            document.querySelectorAll('.radio-group input[type="radio"]').forEach(function(input) {
                input.addEventListener('change', function() {
                    addMarkers(this.value);
                });
            });

            addMarkers('both'); // Initial load with both markers
        }
    </script>
</head>
<body onload="initMap()">
    <div class="container">
        <h1>Visited Locations</h1>
        <form method="post" action="{{ url_for('index') }}">
            <label for="pincode">Enter Pincode:</label>
            <input type="text" id="pincode" name="pincode" required>
            <label for="count">Enter Count:</label>
            <input type="number" id="count" name="count" required>
            <button type="submit">Update</button>
        </form>
        <div class="radio-group">
            <input type="radio" id="show-both" name="filter" value="both" checked>
            <label for="show-both">Show Both</label>
            <input type="radio" id="show-live" name="filter" value="live">
            <label for="show-live">Live Locations</label>
            <input type="radio" id="show-dead" name="filter" value="dead">
            <label for="show-dead">Dead Locations</label>
        </div>
        <div class="map-container">
            <div id="map"></div>
        </div>
    </div>
</body>
</html>

index.html

-------------------------------

from flask import Flask, render_template, request, redirect, url_for
import psycopg2
import json
from geopy.geocoders import Nominatim

app = Flask(__name__)

# Database connection parameters
DB_HOST = 'aws-0-ap-south-1.pooler.supabase.com'
DB_PORT = '6543'
DB_NAME = 'postgres'
DB_USER = 'postgres.lvwgmrdzbsgpwxgmjmcf'
DB_PASSWORD = 'mJ37Ni[1a]Rs!'

# Initialize geolocator
geolocator = Nominatim(user_agent="geoapiExercises")

def get_db_connection():
    return psycopg2.connect(
        host=DB_HOST,
        port=DB_PORT,
        database=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD
    )

# Function to update latitude and longitude for missing pincode data
def update_missing_coordinates():
    conn = get_db_connection()
    cursor = conn.cursor()

    # Fetch pincodes where latitude and longitude are NULL
    cursor.execute('SELECT pincode FROM pincode_data WHERE latitude IS NULL OR longitude IS NULL;')
    pincodes = cursor.fetchall()

    for pincode_entry in pincodes:
        pincode = pincode_entry[0]
        location = geolocator.geocode({"postalcode": pincode, "country": "IN"})  # Assuming country as India
        if location:
            latitude = location.latitude
            longitude = location.longitude

            # Update the database with these coordinates
            cursor.execute(
                'UPDATE pincode_data SET latitude = %s, longitude = %s WHERE pincode = %s;',
                (latitude, longitude, pincode)
            )
            conn.commit()
            print(f"Updated Pincode {pincode}: Latitude {latitude}, Longitude {longitude}")

    cursor.close()
    conn.close()

@app.route('/')
def home():
    return render_template('landing.html')

@app.route('/map', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        pincode = request.form['pincode']
        count = int(request.form['count'])

        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute('''
            INSERT INTO pincode_data (pincode, count)
            VALUES (%s, %s)
            ON CONFLICT (pincode) DO UPDATE SET count = EXCLUDED.count;
        ''', (pincode, count))

        conn.commit()
        cursor.close()
        conn.close()

        # Update missing coordinates after inserting/updating pincode data
        update_missing_coordinates()

        return redirect(url_for('index'))

    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute('''
        SELECT pincode, count, latitude, longitude, "Live"
        FROM pincode_data
    ''')

    data = cursor.fetchall()
    cursor.close()
    conn.close()

    locations = []
    for pincode, count, latitude, longitude, live in data:
        if latitude is not None and longitude is not None:
            locations.append({
                'pincode': pincode,
                'count': count,
                'type': 'live' if live == 'YES' else 'dead',
                'coordinates': [latitude, longitude]
            })

    print("Locations to be passed to the template:", locations)  # Debug output

    return render_template('index.html', locations=json.dumps(locations))

if __name__ == '__main__':
    app.run(debug=True)



app.py


--------------------------------------------------------

Locations to be passed to the template: [{'pincode': '400029', 'count': 3, 'type': 'live', 'coordinates': [19.1273195333333, 72.8595228]}, {'pincode': '400049', 'count': 2, 'type': 'live', 'coordinates': [19.1947670132075, 72.8122774301887]}, {'pincode': '400050', 'count': 3, 'type': 'dead', 'coordinates': [19.0574624282236, 72.8297168906222]}, {'pincode': '400051', 'count': 1, 'type': 'live', 'coordinates': [19.0505452769841, 72.862380931746]}, {'pincode': '400052', 'count': 3, 'type': 'live', 'coordinates': [19.0740159641509, 72.8314172679245]}, {'pincode': '400053', 'count': 2, 'type': 'live', 'coordinates': [19.1377235663212, 72.8302541590674]}, {'pincode': '400054', 'count': 1, 'type': 'live', 'coordinates': [19.0827277461538, 72.8368151589744]}, {'pincode': '400055', 'count': 2, 'type': 'live', 'coordinates': [19.0806901941176, 72.8491250705882]}, {'pincode': '400104', 'count': 2, 'type': 'live', 'coordinates': [19.1622525030303, 72.8413226272727]}, {'pincode': '400056', 'count': 2, 'type': 'live', 'coordinates': [19.0520319064516, 73.0144761741935]}, {'pincode': '400057', 'count': 2, 'type': 'live', 'coordinates': [19.1030399783784, 72.8497443691892]}, {'pincode': '400058', 'count': 2, 'type': 'live', 'coordinates': [19.1224821057692, 72.8392868980769]}, {'pincode': '400059', 'count': 2, 'type': 'live', 'coordinates': [19.1157974483333, 72.933181245]}, {'pincode': '400060', 'count': 5, 'type': 'live', 'coordinates': [19.1356120794118, 72.8571157470588]}, {'pincode': '400069', 'count': 2, 'type': 'live', 
'coordinates': [19.1220548625, 72.8536978083333]}, {'pincode': '400102', 'count': 2, 'type': 'live', 'coordinates': [19.1425257833333, 72.8393386472222]}, {'pincode': '400098', 'count': 2, 'type': 'live', 'coordinates': [19.0790901909091, 72.8609298909091]}, {'pincode': '400097', 'count': 2, 'type': 'live', 'coordinates': [19.1837519111111, 72.8573469126984]}, {'pincode': '400093', 'count': 2, 'type': 'live', 'coordinates': [19.1262784038462, 72.8656881269231]}, {'pincode': '400072', 'count': 1, 'type': 'live', 'coordinates': [19.1063487066667, 72.89145942]}, {'pincode': '400076', 'count': 2, 'type': 'live', 'coordinates': [19.121953943125, 72.9128163975]}, {'pincode': '400614', 'count': 0, 'type': 'dead', 'coordinates': [19.027973448, 73.03560052]}, {'pincode': '400710', 'count': 0, 'type': 'dead', 'coordinates': [19.1084751, 73.0207738666667]}, {'pincode': '400083', 'count': 0, 'type': 'dead', 'coordinates': [19.11470522, 72.932251365]}]
127.0.0.1 - - [15/Sep/2024 23:20:10] "GET /map HTTP/1.1" 200 -


this is the output 

---------------------
now what i want to see it getting plotted on the map, and then when i update / add location it should get updated on map and database also