gistfile1.txt

import psycopg2
import requests
import json
import osm2geojson
import os, subprocess

ogr_base_path = "//Users/rmatsuoka/Google Drive/develop/python/sda/"
ogr_target_table = "t_dev_public_feat3"

params = {
  'dbname': 'postgres',
  'user': 'XXXXXXX',
  'password': 'XXXXXXXXX',
  'host': 'sda-geo.cxa7xe2eptdq.us-east-2.rds.amazonaws.com',
  'port': 5432
}

county_query = "SELECT name, id  FROM t_dev_counties WHERE exported = 0"
export_query = "UPDATE t_dev_counties SET exported = 1 WHERE id = '{cid}'"

#overpass_url = "x"
overpass_url = "https://overpass-api.de/api/interpreter"
#overpass_url = "https://overpass.openstreetmap.fr/api/interpreter"
overpass_query = """
[out:json][timeout:1000];
area[name="{county_name}"][boundary="administrative"][admin_level=6];
// gather results
(
  way["landuse"="recreation_ground"](area);
  way["leisure"="park"](area);
  way["leisure"="pitch"](area);
  way["landuse"="cemetery"](area);
  way["landuse"="forest"](area);
  way["amenity"="college"](area);
  way["amenity"="place_of_worship"](area);
  way["tourism"="theme_park"](area);
  relation["landuse"="recreation_ground"](area);
  relation["leisure"="park"](area);
  relation["leisure"="pitch"](area);
  relation["landuse"="cemetery"](area);
  relation["landuse"="forest"](area);
  relation["amenity"="college"](area);
  relation["amenity"="place_of_worship"](area);
  relation["tourism"="theme_park"](area);
);
// print results
out body;
>;
out skel qt;
"""

print("#######################Initializing DB Connection")
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(county_query)
rows = cur.rowcount

uconn = psycopg2.connect(**params)



print(f"Rows: {rows}")

for record in cur:
    cname = record[0]
    cid = record[1]
    cid_safe = cid.replace('/','-')
    query_str = overpass_query.format(county_name=cname)

    ## request data from overpass server
    #print(f"Request str = {query_str}")
    try:
        response = requests.post(url = overpass_url, data = {'data': query_str})
        #print(f"Response = {response}")
        data = response.json()
    except:
        print('Overpass Request Failed')
        continue

    ## convert to geojson
    geojson = osm2geojson.json2geojson(data)

    ##print(geojson)

    ## dump output
    filename = f"output-d/{cid_safe}.geojson"
    print(f"Writing file: {filename}")
    with open(filename, "w") as outfile: 
        json.dump(geojson, outfile) 
        
    ##ogr2ogr
    #print("Ogr2Ogr")
    ogr_file = f"{filename}" 
    ogr_command = f"ogr2ogr -f PostgreSQL PG:\"host=sda-geo.cxa7xe2eptdq.us-east-2.rds.amazonaws.com port=5432 user='XXXXXX' password='XXXXXXX' dbname='postgres'\" {ogr_file} -nln {ogr_target_table} -update -addfields -forceNullable -skipfailures"
    os.system(ogr_command)
    
    #indicate county read and written
    ucur = uconn.cursor()
    ucur.execute(export_query.format(cid=cid))
    uconn.commit()