1

I found the server with only 2 states data loaded is much faster than the server with all states loaded. My theory is bad formatted address that don't have a exact hit at first will cost much more time when the geocoder checked all states. With only 2 states this search is limited and stopped much early.

There is a restrict_region parameter in geocode function looks promising if it can limit the search range, since I have enough information or reason to believe the state information in my addresses input are correct. I wrote a query trying to use one state's geometry as the limiting parameter:

SELECT geocode('501 Fairmount DR , Annapolis, MD 20137', 1, the_geom) 
    FROM tiger.state WHERE statefp = '24';

and compared the performance with the simple version

SELECT geocode('501 Fairmount DR , Annapolis, MD 20137',1);

EDIT Note the city and zipcode in this input is wrong which is intentional. If the address is perfect there is no point to limit the search range since the geocoder can go to the right table at first try. Only this attempt is meaningful only when the zipcode or city is wrong.

I didn't find performance gain with the parameter. Instead it lost the performance gain from caching, which usually came from running same query immediately again because all the needed data have been cached in RAM.

Maybe my usage is not proper, or this parameter is not intended to work as I expected. However if the search range can be limited, the performance gain could be substantial, since it's the bad formatted addresses took the most time to geocode, and they also often mess up the already cached data because the geocoder need to search for states, even all my input are in one state and all data can be cached in RAM.

2

dracodoc,

No surprise the geometry filter doesn't boost performance. It was more designed to prevent matching against areas where absolutely an address can not exist. You could try simpyfying the geometry with ST_Simplify, but even that might not help much.

What ideally you might want to do is normalize the addresses first especially if your problem is bad normalization and then only filter for ones falling in the right state.

So something like:

WITH addys AS
 (SELECT original_address, normalize(original_address) As addy
 FROM your_table LIMIT 100)
 SELECT geocode(addy,1)
   FROM addys 
      WHERE (addy).stateAbbrev = 'MD';

If it can determine the address, it should be smart enough to go right away to the right tables. If you are noticing significant slow down, make sure you indexed and vacuum analyzed from the parent tables. That often is culprit for slow geocoding as you add more states. I've forgotten that myself on many occasion and wondered why things were so slow.

SELECT install_missing_indexes();
vacuum analyze verbose tiger.addr;
vacuum analyze verbose tiger.edges;
vacuum analyze verbose tiger.faces;
vacuum analyze verbose tiger.featnames;
vacuum analyze verbose tiger.place;
vacuum analyze verbose tiger.cousub;
vacuum analyze verbose tiger.county;
vacuum analyze verbose tiger.state;
  • 1. I tried the indexing and vacuuming and they didn't find any problems. Actually my server performance on good addresses are pretty good, it's the incomplete address or wrong address took the most time. 2. I filtered the address by state first and only process address of same state at a time, but the invalid address may have wrong zipcode or problematic street names which can only be solved with a more broad search. I was hoping there can be some method to limit this broad search. If the current implementation only search in specified state already, there is not much improvement can be done. – dracodoc Nov 28 '15 at 17:05
  • dracodoc, I'll take a look at geometry filter to see if I can improve the performance of it and will report back if I do. You could maybe try just passing in bounding box of a zip. The more complex a geometry the less likely it will cache and also longer it takes to process. So something like ST_Envelope(your_boundary) instead of the gizzillion points polygon a state might have. – LR1234567 Nov 29 '15 at 1:39
  • I didn't use zip because there could be lots of problem with it. Zipcode is used by usps for mail delivery routes, and they are not a bounding box. Census created ZCTA for this purpose. It's possible to get a bounding box for a ZCTA but it will have slightly difference with zipcode coverage. It's very possible that same location can be reported with different zipcode, so I'm afraid limiting the search to zip will have negative impact on accuracy. – dracodoc Nov 29 '15 at 17:07
  • LR1234567, I forgot to mention the test case should always have a missing or wrong zipcode. Because if the city and zipcode is correct there is no potential gain possible, the geocoder will go to the right table in first try. Only when the zipcode is missing or incorrect, the limiting parameter can help to reduce look up time. My example actually have the zipcode and city wrong but I didn't stress it at first. – dracodoc Nov 30 '15 at 15:03

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.