gistfile1.txt

import requests
import json
import psycopg2
from osgeo import ogr
import pysolr

solr_url = "http://127.0.0.1:8983/solr/featuresearch"

solr = pysolr.Solr(solr_url, timeout=60)

solr.delete(q='*:*')


response = requests.get('http://worldmap.harvard.edu/data/search/api?start=0&limit=10')
data = json.loads(response.content)
total = data['total']

conn_string = "host='localhost' dbname='geonode_imports' user='geonode' password='geonode'"
#connString = "PG: host=%s dbname=%s user=%s password=%s" % ('localhost','geonode_imports','geonode','geonode')
# print the connection string we will use to connect
print "Connecting to database\n	->%s" % (conn_string)

conn = psycopg2.connect(conn_string)

cursor = conn.cursor()

for i in range(0, total, 10):
    url = 'http://worldmap.harvard.edu/data/search/api?start=%s&limit=10' % i
    print 'Fetching %s' % url
    response = requests.get(url)
    data = json.loads(response.content)
    for row in data['rows']:
        #cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")
        table_name = row['name'].split(":")[1]
        owner = row['owner_username']
        layerid = row['uuid']
        #table_name = 'rivers_1'
        text_columns = "SELECT column_name from information_schema.columns where table_name = '"+table_name+"' and data_type='character varying'"
        cursor.execute(text_columns);
        columns = cursor.fetchall()
        query_columns = ''
        for column in columns:
            column = column[0]
            column = '"'+str(column)+'"'
            #if len(columns) > 1:
            #    query_columns += str(column) + ','
            #else:
            query_columns += column + ','
        query = 'SELECT fid,%s ST_ASGeoJSON(the_geom) AS the_geom FROM "%s"' %(query_columns,table_name)
        cursor.execute(query)
        rows = cursor.fetchall()
        colnames = [desc[0] for desc in cursor.description]
        results = []
        for row in rows:
            results.append(dict(zip(colnames, row)))
        json_object = json.loads(json.dumps(results, indent=2))
        feature_columns = colnames
        feature_columns.remove('the_geom')
        feature_columns.remove('fid')
        for feature in json_object:
            text = ' '
            for col in feature_columns:
                text += feature[col] + ' '
            extent = ogr.CreateGeometryFromJson(str(feature['the_geom']))
            extent = extent.GetEnvelope()
            extent = "ENVELOPE(%s,%s,%s,%s)" % (extent[0],extent[1],extent[3],extent[2])
            solr_record = {
                           "LayerId": layerid,
                           "FeatureId": feature['fid'],
                           "LayerDate": "",
                           "FeatureDate": "",
                           "bbox": extent,
                           "FeatureText": text,
                           "owner": owner
            }
            solr.add([solr_record])
            print 'Features added to the core for layer %s' % (table_name)
        print 'the layer has been indexed'