Re: [nyphp-talk] zipcode lookup

[Donald J Organ IV]

Well it all depends on what you are trying to do and what a resource code is, my recomendation is to get the long/lat for the zip codes and build a radius search using the following query: replace the follwing fields with value: [origin-lat] [origin-long] [dest-lat] [dest-long] [radius] SELECT distinct zipcode, ROUND((ACOS((SIN([origin-lat]/57.2958) * SIN([dest-latitude]/57.2958)) + (COS([origin-lat]/57.2958) * COS([dest-lat]/57.2958) * COS([dest-long]/57.2958 - [origin-long]/57.2958)))) * 3963, 3) AS distance FROM zipcodes WHERE (latitude >= [origin-lat] - ([radius]/111)) AND (latitude <= [origin-lat] + ([radius]/111)) AND (longitude >= [origin-long] - ([radius]/111)) AND (longitude <= [origin-long] + ([radius]/111)) ORDER BY distance Aaron Deutsch wrote: sent on behalf of a co-worker.... I am building a zip code area UI. For instance, a resource is assigned a range of zip codes that they can recruit prospective clients from. The table fields are: RESOURCE_CODE | ZIPCODE Each available zip code is listed with the relative resource code. My problem is the query.  On the UI end I don't want to list all 1000 zips for a resource, I'd like to show ranges where possible (e.g. 07748; 07750-07790) in a delimited format. I can then do the inserts without any issues. Is there a way in Oracle to recognize row values that have an increment of one from the previous row in order to split them into a range? Therefore, if I wanted the zips from code M99 it would return any single zips and ranges (07750-07790) as opposed to listing them individually. Requested Row Output Example: Row1: 07748 Row2: 07750-07790 And so on..   I can do this with PHP, but I figure if Oracle can do it the application would be a lot faster. I believe another way to do this would be to change the chapter table to have low and high values, but I'd still have the problem of initially populating the new table with the ranges. Also, I'm sure there are other ways do this. If I'm off track please guide me.   Thanks for your help. _______________________________________________ New York PHP Community Talk Mailing List http://lists.nyphp.org/mailman/listinfo/talk NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php

_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com

Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php