I have table in Postgres with a set of records that are street names which needs to be geocoded to get an intersection point.Some of the records would be bad input and when using the geocode_intersection() function in PostGIS instead of giving a null value for a record that it could not geocode,I get a syntax error and the whole processing stops.I had to find that record manually and delete it so that this single record doesn't affect the entire batch.
I would greatly appreciate if someone could help me write an exception logic block code to bypass the syntax error and process other records as it should. Below is my code. Thanks for your help.
CREATE TABLE addr_to_geocode_bkup(addid serial NOT NULL PRIMARY KEY,Road1 text,Road2 text,state text,city text,zip text,rating integer,pt text);INSERT INTO addr_to_geocode_bkup(road1,road2,state,city,zip)values('ALBERS','ROCKY RIVER DRIVE','OH','CLEVELAND','44111'); Have no problem with this 2 records below..It would return me a valid geocode for one record and a null value for the other if the previous record is not available.But if the previous record is present it give me a syntax error.
INSERT INTO addr_to_geocode_bkup(road1,road2,state,city,zip)values('MORSE ROAD','CHERRY BOTTOM ROAD','OH','COLUMBUS','43230');INSERT INTO addr_to_geocode_bkup(road1,road2,state,city,zip)values('MORSE RD','SAWMILL ROAD','OH','CLEVELAND','44111');UPDATE addr_to_geocode_bkup SET (rating, pt)= (g.rating,st_astext(ST_SnapToGrid(g.geomout, 0.000001)))FROM (SELECT DISTINCT ON (addid) addid, (g1.geo).*FROM (SELECT addid, (geocode_intersection(road1,road2,state,city,zip,1)) As geoFROM addr_to_geocode_bkup As agWHERE ag.rating IS NULL ) As g1ORDER BY addid, rating) As gWHERE g.addid = addr_to_geocode_bkup.addid;
أكثر...
I would greatly appreciate if someone could help me write an exception logic block code to bypass the syntax error and process other records as it should. Below is my code. Thanks for your help.
CREATE TABLE addr_to_geocode_bkup(addid serial NOT NULL PRIMARY KEY,Road1 text,Road2 text,state text,city text,zip text,rating integer,pt text);INSERT INTO addr_to_geocode_bkup(road1,road2,state,city,zip)values('ALBERS','ROCKY RIVER DRIVE','OH','CLEVELAND','44111'); Have no problem with this 2 records below..It would return me a valid geocode for one record and a null value for the other if the previous record is not available.But if the previous record is present it give me a syntax error.
INSERT INTO addr_to_geocode_bkup(road1,road2,state,city,zip)values('MORSE ROAD','CHERRY BOTTOM ROAD','OH','COLUMBUS','43230');INSERT INTO addr_to_geocode_bkup(road1,road2,state,city,zip)values('MORSE RD','SAWMILL ROAD','OH','CLEVELAND','44111');UPDATE addr_to_geocode_bkup SET (rating, pt)= (g.rating,st_astext(ST_SnapToGrid(g.geomout, 0.000001)))FROM (SELECT DISTINCT ON (addid) addid, (g1.geo).*FROM (SELECT addid, (geocode_intersection(road1,road2,state,city,zip,1)) As geoFROM addr_to_geocode_bkup As agWHERE ag.rating IS NULL ) As g1ORDER BY addid, rating) As gWHERE g.addid = addr_to_geocode_bkup.addid;
أكثر...