#!/Python27/python.exe

######################################################################################################################################
# importation de l'adaptateur
import psycopg2

# Try to connect

try:
    conn=psycopg2.connect(dbname='web_service', user='laurent', host='localhost', password='cactus')

except:
    print "I am unable to connect to the database"


# Definition of the cursor
cur = conn.cursor()

# Modification du niveau d' "l'isolement" de la bd
conn.set_isolation_level(0)

######################################################################################################################################


# Intersection between one point and a vector layer


try:

    #??Parametres :

    x = 679850
    y = 6971006

    selectString = "SELECT ST_AsText(geom), cult_lib FROM rpg WHERE ST_Intersects(SELECT ST_GeomFromText('POINT(%s %s)',2154), rpg)" % (x, y)

    cur.execute(selectString)
    results = cur.fetchall()
    response_objects = []
    for row in results:
        geom = row[0]
        attrib = row[1]
        response_data = {}
        response_data['geom'] = geom
        response_data['attrib'] = attrib

    print json.dumps(response_data)

except:
    print "Bad request"

######################################################################################################################################

# Intersection between one point and the raster (DEM)

try:

    #Parametres :

    x = 679850
    y = 6971006

    selectString = "WITH points2d AS(SELECT ST_GeomFromText('POINT(%s %s)',2154) AS geom) SELECT ST_Value(bd_alti_25m_somme.rast, 1, p.geom, true) AS val FROM bd_alti_25m_somme, points2d p WHERE ST_Intersects(bd_alti_25m_somme.rast, p.geom)" % (x, y)


    cur.execute(selectString)
    results = cur.fetchall()
    response_objects = []
    for row in results:
        geom = row[0]
        attrib = row[1]
        response_data = {}
        response_data['geom'] = geom


    print json.dumps(response_data)

except:
    print "Bad request"