-------- Original Message -------- Subject: Re: Phoenix street network data Date: Mon, 16 Feb 2009 19:17:17 -0600 From: Chieko Maene <[log in to unmask]> Reply-To: [log in to unmask] To: [log in to unmask] References: <[log in to unmask]> Hi everyone, I finally had a chance to create geocodable street data using TIGER2008 - I had to. I know my patrons would ask me about it. Yes, TIGER2008 has been improved greatly, especially geometry-wise (more lines, spatially accurate) - how wonderful. Though, as Edward wrote, I realized it isn't easy to use TIGER2008 for geocoding. I thought it's just a matter of joining associated tables (EDGES/FEATNAMES/FACES) and merge fields in ArcGIS, but I was wrong. The main problem for me was the one-to-many relationship between EDGES and FEATNAMES. (In fact, EDGES's Prime Key TLID isn't unique - i.e. there are quite a few duplicate TLIDs in EDGES, looks like it's because the edges are between two counties - so, it's actually many-to-many relationship.) Since ArcGIS cannot handle one-to-many outer join (i.e. ArcGIS will grab only the first matching row information, not all matching rows), you would need another software that you are comfortable to use, statistical software or DBMS such as access or foxpro (my choice), so that you won't lose useful information/rows from FEATNAMES table. It worked for me in the end but I spent a whole weekend to do this. I think it's better if we can geocode addressed using relational geodatabase in ArcGIS, not using single-table shapefiles (i.e. streets) so that we can keep table relationships without combining to create a large and un-normalized single table (i.e. shapefiles.) I don't know if I can do that without ArcSDE though, and I would love to know how to do it. Or, I can just wait until ESRI releases a new version of StreetMap N.America dataset based on TIGER 2008.. Or, as Edward pointed out, we can use third-party geocoding tools. I think the USC website is wonderful. I don't know if the USC geocoder is based on TIGER 2008, though. I was a fan of batchgeocoder but later, I found some inaccurate results. I also like to be able to select candidates for ambiguous addresses.. kind of.. One more thing: at the end, I wanted to know how good my result was - I tested my final TIGER2008 Chicago area roads data to geocode Chicago area addresses, and then compare the result with that of the ESRI 2008 StreetMap N. America streets dataset (from ESRI Data & Maps 9.3, TeleAtlas base. *Not* a premium version.) To be fair, I clipped ESRI 2008 StreetMap streets using the same 7 counties boundaries. Then I geocoded the addresses under same conditions (i.e. same method: US Streets with City, State and ZIP, same thresholds, no alias tables, etc.) The results: Unmatched cases(%) without interactive matching, TIGER2008: 5,574 (23%) vs. ESRI2008: 5,650 (23%), total N was 24,158. Happily, TIGER2008 did slightly better than ESRI 2008 StreetMap, though the difference was minuscule.. (The reason for the high unmatched cases is because my addresses contained out-of-the-extent addresses, such as outside state addresses.) Just in case, here is what I did to create the Chicago metropolitan area (7 counties) geocodable streets. Please feel free to correct me if I am doing wrong. Also, if anybody wants the final data, you can download the Chicago metropolitan area TIGER2008 street file from here (no metadata..) http://www.library.northwestern.edu/map/TIGER2008_NE7CO_ROADS.zip (64MB) [Note: I didn't bother to add a extra address-ranges table (ADDR) since dealing with just three tables was enough for me. Well, I guess FACES is optional too, but it's good to include additional left/right side polygon attributes for those who like to aggregate data based on boundaries, i.e. tracts, blocks.] (1) In ArcGIS : append 6 counties EDGES shapefiles to the main county (cook county.) Select road edges only ([roadflg]='Y') and also get rid of miscellaneous road types we don't want to use, i.e. alleys and misc. trails ((MID([MMTFCC],1,3)='S17'OR (MID([MMTFCC],1,3)='S18') and then finally add a new identifier field, FID2 (copy value from FID), to keep a unique ID key for each polyline. (2) In DBMS software: append all 7 counties' FEATNAMES tables. Do the same for the 7 counties' FACES tables. Create a query, [EDGES left outer join FEATNAMES on EDGES.TLID=FEATNAMES.TLID]. From FEATNAMES, you will get parsed road information (dir, pretype, name, surfix, type) with variations of street names. (i.e. "E. Main St" in Cook County is also known as "W County Line Rd", "Main St", "Lake-Cook Rd", etc. We want all these variations/rows for better geocoding results.) After that, I got left and right sides face/polygon information by joining EDGE-FEATNAMES table and FACES, twice (left side, and then right side) - this part is straightforward since it's all one-to-one relationship. (3) Optional - In DBMS software: You can further continue to query (join) to get textual place and state abbreviation names, i.e. "Chicago", and "IL". Get the information from other TIGER tables (i.e. COUNTY or PLACE.) Also, you may want to rename fields so that ArcGIS can recognize geocodable fields (i.e. instead of LFROMADD, change it to L_F_ADD). (4) In DBMS software: find duplicate FID2 (unique identifier for EDGE shapefile polylines), and separate the duplicate sets by the number of duplicate (i.e. group1: duplicate 1, group2: duplicate 2, group3: duplicate 3, etc.. I found up to 9 duplicate name groups as a result of EDGES-FEATNAMES one-to-many joining.. I am not sure if this part makes sense to anybody..) (5) In ArcGIS: join EDGES polylines from (1) and the first group of the EDGE-FEATNAMES-FACES joined table from (4) using a common field, EDGES.FID and EDGES-FEATNAMES-FACES.FID2. Export as EDGES_1. Remove the previous join in EDGES polylines and join EDGES and the second group of EDGE-FEATNAMES-FACE (duplicate group 2) and then export only the joined rows (i.e. only rows with matching join fields) as EDGES_2. Repeat this for all duplicate group tables. Lastly, merge all EDGES groups (EDGES_1, EDGES_2, EDGES_3, etc.) by appending others to one. (6) Optional: I added a spatial index in ArcCatalog for faster rendering in ArcGIS. TIGER files don't come with these index files, .sbn & .sbx. I didn't add any attribute indexes since "create a geocode locator" will create indexes for geocoding. Sincerely, Chieko -- Chieko Maene, MS, MLIS Maps & State Documents Librarian Government and Geographic Information and Data Services University Library Northwestern University 1970 Campus Drive Evanston, IL 60208-2300 Phone: (847) 467-3679 Fax: (847) 491-7603 [log in to unmask] http://www.library.northwestern.edu/map/ http://geospatial.edublogs.org/ Maps-L Moderator wrote: > -------- Original Message -------- > Subject: RE: Phoenix street network data > Date: Sat, 14 Feb 2009 12:28:52 -0800 > From: Edward Sullivan <[log in to unmask]> > To: <[log in to unmask]>, <[log in to unmask]> > References: A<[log in to unmask]> > > > > This is a bit "outside the box", but rather than going to the trouble of > locating, standardizing, and performing quality checks on 'free' road > network and address layers, I've lately taken to using the Batch Geocode > site to do many of my smaller (< 5,000 addresses) geocoding tasks - > > http://www.batchgeocode.com/ > > I believe the Yahoo! Geocoding API this 'mashup' page utilizes is using > the very good GDT/TeleAtlas road network base maps, so the quality of > the results are typically as good or better than what I can accomplish > with free data and desktop GIS geocoders. > > There are more advanced batch geocoding services available to academic > users without charge from the USC GIS Research Laboratory: > > https://webgis.usc.edu/ > > > > Edward A. Sullivan, III > Senior Technical Associate > Economic & Planning Systems, Inc. > 2501 9th Street, Suite 200, Berkeley, CA, 94710-2515 > Voice: 510-841-9190 FAX: 510-841-9208 > Email: [log in to unmask] > Web site: www.epsys.com > > Due to the potential that information exchanged by electronic media can > deteriorate, be damaged, lost or modified, intentionally or otherwise, > use of this electronic data by anyone other than Economic & Planning > Systems, Inc. shall be at the sole risk of such user and without > liability or legal exposure to Economic & Planning Systems, Inc. > > The recipient is responsible for verifying the accuracy of data against > governing hard copy documentation. If there is a discrepancy between > the hard copy and the electronic copy, the hard copy will govern. > > Recipient assumes all risks in the changing or modification of data and > revisions or updating of hard copy documents. > > -----Original Message----- > From: Maps, Air Photo & Geospatial Systems Forum > [mailto:[log in to unmask]] On Behalf Of Maps-L Moderator > Sent: Friday, February 13, 2009 11:43 AM > To: [log in to unmask] > Subject: Phoenix street network data > > -------- Original Message -------- > Date: Fri, 13 Feb 2009 11:38:48 -0800 > From: andrew nicholson <[log in to unmask]> > To: <[log in to unmask]>, <[log in to unmask]>, tanya > <[log in to unmask]> > > > > > Hi everyone, > > My apologies for the cross posting. > > We are looking for a good quality road network for Phoenix Arizona to > allow students to geocode too. We have downloaded the Tigerline files, > but they are not very good for this purpose. Can anyone provide us with > suggestions for a source or two for high quality and free Phoenix street > network data? > > thanks for your help, > Andrew > > > -- > > Andrew J.P. Nicholson > > GIS/Data Librarian > > Hazel McCallion Academic Learning Centre > > Room 360A > > University of Toronto Mississauga > > 3359 Mississauga Road North > > Mississauga, Ontario > > CANADA > > L5L 1C6 > > Phone:(905)828-3886 > > Fax:(905)569-4320 > > Email: [log in to unmask] > <mailto:[log in to unmask]> > > Web: http://www.utm.utoronto.ca/library/ > > > > > ------------------------------------------------------------------------ > Windows Live(tm): Keep your life in sync. See how it works. > <http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks > _022009>