backend.py

import flask
import os
import psycopg2
import geojson
from dotenv import load_dotenv
from flask_cors import CORS

load_dotenv()

DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_HOST = os.getenv("DB_HOST")
DB_PORT = os.getenv("DB_PORT")

QUERY = """with ranked_stores as (select id, name, st_y(location) as lat, st_x(location) as long,
ntile(10) over (order by random()) as percentile
from mystores
where city = 'Pune')
select name, lat, long, case when percentile = 1 then 'tier1' when percentile between 2 and 5 then 'tier2' else 'tier3' end as tier from ranked_stores;"""

def generate_gejson():
    with psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASSWORD, host=DB_HOST, port=DB_PORT) as conn:
        with conn.cursor() as cur:
            tier_1_features = []
            tier_2_features = []
            tier_3_features = []

            cur.execute(QUERY)
            for row in cur.fetchall():
                if row[3] == "tier1":
                    point = geojson.Point((row[2], row[1]))
                    tier_1_features.append(geojson.Feature(geometry=point, properties={"name": row[0]}))
                elif row[3] == "tier2":
                    point = geojson.Point((row[2], row[1]))
                    tier_2_features.append(geojson.Feature(geometry=point, properties={"name": row[0]}))
                else:
                    point = geojson.Point((row[2], row[1]))
                    tier_3_features.append(geojson.Feature(geometry=point, properties={"name": row[0]}))

            tier_1_feature_collection = geojson.FeatureCollection(tier_1_features)
            tier_2_feature_collection = geojson.FeatureCollection(tier_2_features)
            tier_3_feature_collection = geojson.FeatureCollection(tier_3_features)

            return [tier_1_feature_collection, tier_2_feature_collection, tier_3_feature_collection]

app = flask.Flask(__name__)
CORS(app)

@app.route("/")
def index():
    return flask.jsonify(generate_gejson())

if __name__ == "__main__":
    app.run(host="0.0.0.0", debug=True)