Lubar, I saw your post at Stack Overflow but am going to post a similar answer here for consistency. It's a good question. I work in the address verification industry and have tackled your kind of problem before.
I linked to this Stack Overflow question in a comment; and it's important to know that there's really no guarantee about the format of complete freeform street addresses. As mentioned in the linked post, complete addresses can look like any of these:
1) 102 main street
Anytown, state
2) 400n 600e #2, 52173
3) p.o. #104 60203
4) 1234 LKSDFJlkjsdflkjsdljf #asdf 12345
5) 205 1105 14 90210
(The reasons are explained in the linked post.) I realize that GeoPy returns addresses in a certain format -- depending on the geocoder used (which resulting format is out of GeoPy's control), but addresses can look all sorts of ways within a certain component (like having commas), and it's important to know that standardized addresses don't have commas (according to USPS Publication 28).
I helped work on an API just recently called US Street Address API from SmartyStreets; it was just upgraded to support geocoding and single-line address parsing.
GeoPy is designed to geocode, not parse into components (that task is actually really difficult for reasons I won't get into here). The US Street Address API will, however, componentize the address and return coordinates and other information about the address, and only if the addresses are real; no "guessed" results.
To parse a single-line address into components using Python, simply put the entire address into the "street" field:
import json
import pprint
import urllib
LOCATION = 'https://api.smartystreets.com/street-address/'
QUERY_STRING = urllib.urlencode({ # entire query sting must be URL-Encoded
'auth-token': r'YOUR_API_KEY_HERE',
'street': '1 infinite loop cupertino ca 95014'
})
URL = LOCATION + '?' + QUERY_STRING
response = urllib.urlopen(URL).read()
structure = json.loads(response)
pprint.pprint(structure)
The resulting JSON object will contain a components object which will look something like this:
"components": {
"primary_number": "1",
"street_name": "Infinite",
"street_suffix": "Loop",
"city_name": "Cupertino",
"state_abbreviation": "CA",
"zipcode": "95014",
"plus4_code": "2083",
"delivery_point": "01",
"delivery_point_check_digit": "7"
}
The response will also include the combined first_line and delivery_line_2 so you don't have to manually concatenate those if you need them.
There are a few issues to consider. First, have you considered using a VIEW? I.e.
CREATE OR REPLACE VIEW places_with_coods AS
SELECT gid, ST_Y(geo::geometry) AS lat, ST_X(geo::geometry) AS lon
FROM places;
However, if you want to use triggers, these changes will make things work. The important pieces being that NEW is the row, which can be modified and returned. Also, use ST_Y and not new.st_y. Lastly, the trigger must be a BEFORE trigger, since it modifies the record before it is stored.
CREATE OR REPLACE FUNCTION update_tg2()
RETURNS trigger AS
$$
BEGIN
NEW.lat := ST_Y(NEW.geo::geometry);
NEW.lon := ST_X(NEW.geo::geometry);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
DROP TRIGGER IF EXISTS triger_coords2 on places;
CREATE TRIGGER triger_coords2 BEFORE INSERT OR UPDATE ON places
FOR EACH ROW EXECUTE PROCEDURE update_tg2();
Best Answer
Just change the second line
to
The "type modifier" is a 2.0 feature. You might have other problems if GeoAlchemy expects PostGIS 2.0+, but for this particular function, just removing the typmod syntax should suffice.