From wskinner@fast.net Mon May 17 05:20:05 1999 Received: from mxu4.u.washington.edu (mxu4.u.washington.edu [140.142.33.8]) by lists.u.washington.edu (8.9.3+UW99.02/8.9.3+UW99.01) with ESMTP id FAA29700 for ; Mon, 17 May 1999 05:20:04 -0700 Received: from jennyjump.state.nj.us (jennyjump.state.nj.us [199.20.64.40]) by mxu4.u.washington.edu (8.9.3+UW99.02/8.9.3+UW99.01) with ESMTP id FAA23703 for ; Mon, 17 May 1999 05:20:04 -0700 Received: from dohqb01.doh.state.nj.us (DOHQB01.doh.state.nj.us [205.148.40.1]) by jennyjump.state.nj.us (8.9.3/8.9.3) with SMTP id IAA21744 for ; Mon, 17 May 1999 08:18:54 -0400 (EDT) Received: from [10.17.1.96] by dohqb01.doh.state.nj.us (AIX 4.1/UCB 5.64/4.03) id AA126274; Mon, 17 May 1999 08:17:25 -0400 Message-Id: <3740337D.3C80B74E@fast.net> Date: Mon, 17 May 1999 08:19:25 -0700 From: Ric Skinner X-Mailer: Mozilla 4.5 [en] (WinNT; I) X-Accept-Language: en Mime-Version: 1.0 To: WAPHGIS Subject: [Fwd: SUM: Data Cleaning for Geocoding] Content-Type: multipart/mixed; boundary="------------78C7EF46ABB2C60BF194FB7E" This is a multi-part message in MIME format. --------------78C7EF46ABB2C60BF194FB7E Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit My apologies for a possible duplicate, however email sending problems on this end may have prevented this reaching WAPHGIS. --------------78C7EF46ABB2C60BF194FB7E Content-Type: message/rfc822 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Mozilla-Status2: 00000000 Message-ID: <373C9E8C.BD4644D7@fast.net> Date: Fri, 14 May 1999 15:07:09 -0700 From: Ric Skinner X-Mailer: Mozilla 4.5 [en] (WinNT; I) X-Accept-Language: en MIME-Version: 1.0 To: WAPHGIS Subject: SUM: Data Cleaning for Geocoding Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Thanks to Philippe THOMAS (thomas@cybercable.fr), Jay Morgan (jmorgan@towson.edu), Antonio Morais Arnaud (ama@di.fct.unl.pt), James A. Woods (jawoods-gcs@worldnet.att.net), Joe Lewis (joe@dsslink.com), Frank M. Howell (fmh1@ra.msstate.edu), Marjorie Roswell (roswell@umbc.edu), Mike Hodge (mmhodge@CCS1.ORG) for responding to my query: "We have a very large (1 million records) address file that contains dirty and missing values. The vast majority of dirty values are misspelled street names, misspelled city name, incorrect zip code. We envision data hygiene will correct the dirty data and fill in missing values. We propose to develop a MS-Access data hygiene application and pass the file through prior to geocoding. We are considering using data files from USPS ('City State File'; 'Five Digit ZIP Code File'; http://www.usps.gov/ncsc/products/) and Semaphore Corp's. 'ZIP4' software (http://www.semaphorecorp.com/cgi/zp4.html). Has anyone used any of these products for data hygiene in preparation for geocoding? What other methods are used for data hygiene of large files?" I apologize for taking so long in summarizing. I was awaiting a copy of ZIP4 so I could report on its usefulness, but it still hasn't arrived (state bureaucracy!). So, here's what I can summarize to this point. Responses: 1. Ask The Direct Marketing Association (www.the-dma.com) or the DM News newspaper or website for lists of software doing address hygiene. Many companies such Anchor or Experian will clean your file for a very low budget. Try also www.centrusonline.com who will do online address cleaning. 2. Pass files for geocoding through the ZIP+4 program of Mailer's software. It cleans up addresses, adds +4 coding to correct addresses, and highlights address problems for incorrect addresses. Reportedly a much better hit rate in ArcView was achieved by preprocessing the data in this way. 3. Contact Matt Jaro, the matching Guru, at mjaro@matchware.com or Max Eveleth Jr.(meveleth@matchware.com), Executive VP, MatchWare Technologies, 4. Several responses discussed Semaphore Corp.'s. ZP4 and found it to be a good to excellent product. One has to remember that this product is not designed for use with geo-coding, but rather with rectifying addresses to U.S.P.S. standards. 5. Centrus reportedly has outstanding data cleansing algorithms, is easy to use, and is CASS certified. It is no magic bullet, of course. We've seen values in street fields such as "50 feet past the police station" over time. Centrus won't solve this. And there are still some instances where global search and replace is a good bet before batching a file. 6. We've made use of several of these types of products for "cleaning" address files prior to geocoding operations. These have included both private addresses as well as business locations. I've talked with several companies which make such automated-mailing software/data programs, including Semaphore Corp., about their product line and their utility for geocoding operations. In many ways, they "don't get it". While ZIP4 was perhaps the most complete such utility, their supposed need to time-out the use of the software when the next version of the underlying USPS database was released so that compatibility with the Coding Accuracy Support System (CASS) was maintained proved to be a nuisance. That is, they'd make you fuss severely with them over your desire NOT to maintain a quarterly subscription to their product, for more $$$, since CASS-compatibility was "required". 7. One user uses ZIP4 in manual mode but StreetRite in batch mode, however acknowledges this is because early versions of zp4 tended toward some false matches. I suspect they ironed most of that out. 8. Use Access 97, and a mix of update queries, combined with manual looking at the data to clean up data. 9. Use find-and-replace with Microsoft Word or more complex queries with Microsoft Access and SQL 7. Use an ESRI script View.CreateStandardizedAddress (stanshpe.ave) in the C:\ESRI\AV_GIS30\ARCVIEW\Samples\scripts directory to create a standardized address table and to use a script to remove the problem apartment numbers. 10. Other companies that had software that would clean up address data: http:\\www.mirus-ltd.com/miruszip.htm and http:\\ezlanguage.com/maplinx/mailmanager/index.html -- Ric Skinner Research Scientist -- GIS NJ Dept. of Health & Senior Services Cancer Epidemiology Services 3635 Quakerbridge Rd. P.O. Box 369 Trenton, NJ 08625-0369 Phone: 609-588-3500 FAX: 609-588-3638 wskinner@fast.net and Co-Chair 2nd International Health Geographics Conference (in planning) 1st IHGC website: www.jhsph.edu/ihgc --------------78C7EF46ABB2C60BF194FB7E-- .