<reponame>salilbatra/Spatial-joins-on-relational-database-and-key-value-store<gh_stars>0
import psycopg2
import time, os
conn = psycopg2.connect(database='postgis_22_DBSpatial', user='postgres', password='<PASSWORD>', host='127.0.0.1', port='5432')
#print("opened database very very successfully")

file = open('resultPostgis.geojson', 'w')
cur = conn.cursor()
start = time.time()
#print(datetime.time)
cur.execute("""SELECT ng.name, cs.latitude, cs.longitude
                        FROM "neighborhoods" AS ng, "collisions" AS cs
                           WHERE ST_Contains(ng.geom,cs.geom);""")

cur.execute("""SELECT ng.name, cs.latitude, cs.longitude
                FROM "neighborhoods" AS ng, "collisions" AS cs
                    WHERE _ST_Contains(ng.geom,cs.geom);""")

cur.execute("""SELECT ng.name, cs.latitude, cs.longitude
                FROM "neighborhoods" AS ng, "collisions" AS cs
                WHERE ST_Intersects(ng.geom,cs.geom);""")

cur.execute("""SELECT ng.name, cs.latitude, cs.longitude
                FROM "neighborhoods" AS ng, "collisions" AS cs
                    WHERE _ST_Intersects(ng.geom,cs.geom);""")
#cur.execute("""SELECT ng.geom FROM "neighborhoods" as ng WHERE ng.name = 'SOMERTON'""")
end = time.time()-start

for row in cur.fetchall():
    file.write(str(row))
print(end)
import psycopg2
import time, os
conn = psycopg2.connect(database='postgis_22_DBSpatial', user='postgres', password='<PASSWORD>', host='127.0.0.1', port='5432')
#print("opened database very very successfully")

file = open('resultPostgis.geojson', 'w')
cur = conn.cursor()
start = time.time()
#print(datetime.time)
cur.execute("""SELECT ng.name, cs.latitude, cs.longitude
                        FROM "neighborhoods" AS ng, "collisions" AS cs
                           WHERE ST_Contains(ng.geom,cs.geom);""")

cur.execute("""SELECT ng.name, cs.latitude, cs.longitude
                FROM "neighborhoods" AS ng, "collisions" AS cs
                    WHERE _ST_Contains(ng.geom,cs.geom);""")

cur.execute("""SELECT ng.name, cs.latitude, cs.longitude
                FROM "neighborhoods" AS ng, "collisions" AS cs
                WHERE ST_Intersects(ng.geom,cs.geom);""")

cur.execute("""SELECT ng.name, cs.latitude, cs.longitude
                FROM "neighborhoods" AS ng, "collisions" AS cs
                    WHERE _ST_Intersects(ng.geom,cs.geom);""")
#cur.execute("""SELECT ng.geom FROM "neighborhoods" as ng WHERE ng.name = 'SOMERTON'""")
end = time.time()-start

for row in cur.fetchall():
    file.write(str(row))
print(end)
#print("opened database very very successfully") #print(datetime.time) SELECT ng.name, cs.latitude, cs.longitude                         FROM "neighborhoods" AS ng, "collisions" AS cs                            WHERE ST_Contains(ng.geom,cs.geom); SELECT ng.name, cs.latitude, cs.longitude                 FROM "neighborhoods" AS ng, "collisions" AS cs                     WHERE _ST_Contains(ng.geom,cs.geom); SELECT ng.name, cs.latitude, cs.longitude                 FROM "neighborhoods" AS ng, "collisions" AS cs                 WHERE ST_Intersects(ng.geom,cs.geom); SELECT ng.name, cs.latitude, cs.longitude                 FROM "neighborhoods" AS ng, "collisions" AS cs                     WHERE _ST_Intersects(ng.geom,cs.geom); #cur.execute("""SELECT ng.geom FROM "neighborhoods" as ng WHERE ng.name = 'SOMERTON'""")