Oracle: Zipcode to X/Y Coordinate

• david_j_darling_ / 400 / Fri, 05 Jun 2009 11:42:00 GMT / Comments (3)
• Does anyone know of any software that could convert a US Zipcode into an X/Y Coordinate that I could then load into Oracle Spatial? I'm looking at converting Zipcodes for Tennesse.
• Keywords:

  zipcode, coordinate, oracle

• http://database.itags.org/oracle/291706/«« Last Thread - Next Thread »»

• 1. I believe the term you look for is 'geocode'. If I understand correctly, the software is documented in the Oracle SPatial manual and referenced in the Pro Oracle Spatial book ... but you need to get a data source to meet your needs.

     hansforbrich | Sat, 23 Feb 2008 12:30:00 GMT |

  2. Hello David:
     At this US Census Bureau site:
     http://www.census.gov/geo/www/cob/z52000.html
     You can download the 5-digit zip code tabulation areas (ZCTA). I think they are for 1990.
     From there, you could use the shp2sdo.exe program to laod this into an Oracle table.
     After it is in Oracle, you could use SDO_GEOM.SDO_CENTROID to derive a point at the centroid of each polygon.
     Just a suggestion - I have not actually done all of the above.
     
     R Clement

     richardclement | Sat, 23 Feb 2008 12:31:00 GMT |

  3. Hi Richard,
     
     That is exactly what I did when I was going to respond to this note. The process is correct (I migrated the data after loading with the .exe version of the converter using sdo_migrate.to_current), got and stored the centroid and the name (zip code) for each polygon.
     
     Then I went to check the results to make sure the centroid was in the zip code (because Oracle's centroid function doesn't require the centroid to be inside the polygon), and that was when I found a lot of duplicate zips, and some funny zips too (378HH, 378XX) that I couldn't decode the meaning of.
     
     So I will hope someone else finds good zip code data...

     danabugov | Sat, 23 Feb 2008 12:31:00 GMT |