[postgis-users] Batch geocoding (~2 mil addresses)

[Stephen Woodbridge]

FYI. You can not do a COMMIT in a plpgsql or any stored procedures 
because the the function call is run in a transaction. The way to do 
this is in an external script like Perl or PHP where you cycle through 
500-1000 addresses and updates and then do the COMMIT.

-Steve

On 12/1/2011 9:04 AM, Ravi ada wrote:
> Thanks all for the suggestions. I am migrating this process to a bigger
> machine which has 16GB RAM and faster hard drives. I will incorporate these
> changes once the migration completes.
>
> There are two things I got so far I need to do.
> 1) use pgScript to commit every 500 or 1000 updates
> 2) send explain analyze to http://explain.depesz.com/ further evaluate the
> query plan
>
> I will keep you posted guys..
>
> Thanks
> Ravi Ada
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Andy
> Colson
> Sent: Wednesday, November 30, 2011 8:54 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Batch geocoding (~2 mil addresses)
>
> On 11/29/2011 11:18 PM, Ravi ada wrote:
>> Hello All,
>>
>> I have been fighting with PostGIS for the last week or two to geocode
>> about 2 million addresses. Here are the challenges that I am facing.
>>
>> 1)I sorted the addresses by zip, address, city to group the similar
>> addresses together.
>>
>> 2)So far I have seen only 50% of rating zero, that means perfect match.
>>
>> 3)Some addresses have Suite# or Apt# in address line 1. Geocode
>> function takes longer time for such addresses
>>
>> 4)Addresses that are on the interstate, may be on the service road.
>> These are taking longer to code.
>
> How about posting an "EXPLAIN ANALYZE" to http://explain.depesz.com/.
> Maybe we can speed it up.
>
> -Andy
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users