2

I have the following example (adapted from the PostGIS manual (version 2.2):

CREATE TABLE global_points ( 
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    location GEOGRAPHY(POINT,4326)
  );

INSERT INTO global_points (name, location) VALUES ('London', ST_GeographyFromText('SRID=4326;POINT(0 49)') );
INSERT ..........
c = conn.cursor()
try:
  query ="SELECT name FROM global_points WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(-110 29)'), 1000000);"
  c.execute(query)
  l = c.fetchall()

................. How shall I modify the query to allow for specifiyng the variables lon and lat in POINT in order to be able to iterate over a set of geographic locations?

2

Here are the basic usage patterns for inserting values with parameters with psycopg2:

http://initd.org/psycopg/docs/usage.html

For example:

data = 'value'
sql_insert = """INSERT INTO testtable (num) VALUES (%s);"""
cur.execute(sql_insert, data)

So where you want to insert LAT and LON, you need to create two variables and then two parameters to the insert query, then where you execute the query, pass in the variables holding the lat and lon values.

And we do this using this pattern:

addressid = row[0] #from the cursor
sql = 'insert into dqmt.newaddresses (addressid, lat, lon) values (%s, %s, %s);'
data = (addressid, result.latitude, result.longitude) #result is from geopy 
curDQMT.execute(sql, data)
1

Assuming you will be defining long and lat as python variables - this should work...

c.execute('''SELECT name FROM global_points WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(%s, %s)'), 1000000);''',(long,lat))
  • Thank you Ziggy and DPSSpatial for your answers. I have tried Ziggy's sugestion and got the following error: ...WHERE ST_DWithin(station_location,ST_GeographyFromText('SRID=4326;POINT(%s, %s)'), 1000000);''',(st_lon,st_lat)). psycopg2.InternalError: parse error - invalid geometry HINT: "SRID=4326;POINT(26.08, " <-- parse error at position 23 within geometry This happens if I'm assigning the numerical values of the latitude and longitute to the variables st_lon and st-lat (e.g. st_lon = 26.08, st_lat = 44.51). When using the numeric values in the query expression, it works. – Andrei Sep 13 '16 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.