Do you know what the status of the PAGC project is? Is it still under active development? The last post was in 2008. <br><br><div class="gmail_quote">On Sat, May 7, 2011 at 5:00 PM, Stephen Woodbridge <span dir="ltr"><<a href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">Stephen,<br>
<br>
In a geocoder I wrote in 2000, I mapped city, state and/or zipcode into a list of counties, then only searched those counties. This greatly reduced the 3300 counties in Tiger to a small handful that needed to be searched. The FIPS-4 document has a mapping of all place names to counties. Zipcodes are easier to handle because you can just index the records with zipcodes but if you wanted to widen the search, then map the zipcode to a place name and then to counties.<br>
<br>
My first thought was to use inherited tables where each state or state equivalent is in a separate table and filter the tables by the state name of abbreviation. It sounds like you might be already doing something like this.<br>
<br>
I think trigrams would provide some benefits. I have used double metaphone, but only used the first code for fuzzy searches because this is a better phonic search key than soundex in my opinion because it matches thinks that sound alike even if they do not start with the same letter. Since I used it as a key I only used the first 4 characters of the metaphone code.<br>
<br>
I wrote my own tiger geocoder in C and have since abandoned that in favor of PAGC. PAGC has just recently refactored the code to support SQLite as a datastore and has had some work done to support Postgresql in addition to the previous support for Berkeley DB. One idea that I had was that with appropriate support for database backing stores, that the PAGC library might be able to be wrapped in a stored procedure which would then provide a high quality and high performance geocoder in the database.<br>
<br>
Here is an instance that I created using SQLite as the backing store and Tiger 2010 data:<br>
<br>
<a href="http://imaptools.com:8080/geocode/" target="_blank">http://imaptools.com:8080/geocode/</a><br>
<br>
The single line parsing is broken at the moment, I have a ticket to look into that and hope to have it resolved shortly.<br>
<br>
PAGC project can be found here:<br>
<br>
<a href="http://www.pagcgeo.org/" target="_blank">http://www.pagcgeo.org/</a><br>
<br>
Regards,<br>
-Steve<div><div></div><div class="h5"><br>
<br>
<br>
On 5/6/2011 10:36 PM, Stephen Frost wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div></div><div class="h5">
* Johnathan Leppert (<a href="mailto:johnathan.leppert@gmail.com" target="_blank">johnathan.leppert@gmail.com</a>) wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Ok, I have it working, but it's very very slow. It takes about two seconds<br>
to geocode a single record. This isn't very realistic for large datasets.<br>
Anyone have any ideas on possible optimization? It appears to have created<br>
proper indexes on all the tables.<br>
</blockquote>
<br>
Yeah, there's a few things I've been working on. For one, a lot of the<br>
time is spent planning that massive query at the heart of the geocoding,<br>
when it really doesn't need to be re-planned for every query. It's done<br>
the way it is currently because the tables are partitioned up by state.<br>
I've got someone working on simply splitting that query up to be 53 (or<br>
however many...) queries, one for each state/territory, which will allow<br>
them to be planned once and then the plans re-used inside the same<br>
session. That should improve things.<br>
<br>
I've also been looking into using trigrams for things instead of the<br>
current indexes.. I think they're perform better with regard to speed,<br>
just need to make sure it still returns good results, etc..<br>
<br>
I'm very interested in anyone else working on this and any suggestions<br>
people have for how to improve it.. I've not been able to work on it<br>
much recently and while I've tried to delegate it to others, it's a<br>
pretty complex system which is, in particular, hard to test well..<br>
<br>
Thanks,<br>
<br>
Stephen<br>
<br>
<br>
<br></div></div><div class="im">
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></blockquote><div><div></div><div class="h5">
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br><i><font color="#CCCCCC">Johnathan</font></i><div><font color="#CCCCCC">Software Architect & Developer</font></div><div><font color="#CCCCCC">Columbus, Ohio</font></div>
<div><i><font color="#CCCCCC"><a href="http://twitter.com/#!/iamleppert" target="_blank">Follow me on Twitter: @iamleppert</a></font></i></div><br>