0

I am trying to return some tables from my database as geo JSON to the leaflet. There are about 4000 rows in each, but rendering the geo JSON on my leaflet map is so slow. Am I running the query the wrong way? is there a way to improve performance

def layers():
    
    conn = psycopg2.connect( host=hostname, user=username, password=password, dbname=database )

    c = conn.cursor()
    c.execute("select layer, type, st_AsGeoJSON(geom4326) FROM eng_floodzone2 ")
    rows = c.fetchall()
    objects_list = []
    for row in rows:
        #geo_json={
            varcons = collections.OrderedDict()
            varcons["type"]= "Feature"
            varcons["name"]= row[0]
            varcons["properties"]= {"name": row[0]}
            varcons["geometry"]= json.loads(row[2])
            objects_list.append(varcons)
            
    getfloodzone2 = json.dumps(objects_list)

    c.execute("select sac_name, st_AsGeoJSON(geom4326) FROM eng_sac_pg ")
    rows = c.fetchall()
    objects_list = []
    for row in rows:
            varcons = collections.OrderedDict()
            varcons["type"]= "Feature"
            varcons["name"]= row[0]
            varcons["properties"]= {"SAC_NAME": row[0]}
            varcons["geometry"]= json.loads(row[1])
            objects_list.append(varcons)
    getsac = json.dumps(objects_list)
        with app.app_context():
        return render_template('index2.html', 
                               floodzone2 = getfloodzone2,
                               sac = getsac
                                )
    c.close()
    


if __name__ == '__main__':
    app.run( debug= True, use_reloader=False,  threaded=True)

JacquesJ
  • 22
  • 3
lloyd
  • 433
  • 1
  • 5
  • 15
  • What is the bottleneck? Db query? Sending response to browser? Rendering in UI?.. – ghybs Feb 01 '21 at 17:25
  • @ghybs when I render the geojson on the client side, it is renders really slowly, like the map hangs a lot and the page takes time to load up as well – lloyd Feb 01 '21 at 17:30
  • 4,000 features is really a lot. Have you tried the canvas option? – ghybs Feb 01 '21 at 17:52
  • @ghybs no. I haven't. How does that work please? – lloyd Feb 01 '21 at 18:02
  • See https://stackoverflow.com/questions/43015854/large-dataset-of-markers-or-dots-in-leaflet/43019740#43019740 – ghybs Feb 01 '21 at 18:14
  • @lloyd depending on the geometries, 4k is quite a lot. If they're too 'complex' consider simplifying them with https://postgis.net/docs/ST_Simplify.html (of course, if it makes sense to your use case) – Jim Jones Feb 01 '21 at 18:58

0 Answers0