Home » SQL & PL/SQL » SQL & PL/SQL » create a view that has zip codes and nearby zip codes
create a view that has zip codes and nearby zip codes [message #436285] Tue, 22 December 2009 15:28 Go to next message
dpace32
Messages: 10
Registered: December 2009
Junior Member
Hi - I would like to create a list of all zip codes and the other zip codes near them, ideally the output would be

ZIP1 NEARBY1
ZIP1 NEARBY2
ZIP1 NEARBY3
ZIP2 NEARBY1
...

So far I have a table called unique_zip that has all of the unique zipcodes.

I have another table called location that has the following fields:
POSTAL_CODE, LAT, LONG

Is it possible to create a view that will bring back to me the ZIP and all of the nearby zips (where lat and long are between +.05 and -.05)?

Thank you!
Re: create a view that has zip codes and nearby zip codes [message #436286 is a reply to message #436285] Tue, 22 December 2009 15:31 Go to previous messageGo to next message
dpace32
Messages: 10
Registered: December 2009
Junior Member
Also it would be great for this to be in a view because it will be easier to implement in the application I was be using it with.

Thanks agani!
Re: create a view that has zip codes and nearby zip codes [message #436289 is a reply to message #436286] Tue, 22 December 2009 17:04 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
> it would be great for this to be in a view
Your pipe dream is a non-starter.
VIEW does not take input parameter upon which the results are returned.

It would be possible to create a fixed LOOKUP table.

CREATE ZIP_NEIGHBORS (
MY_ZIP VARCHAR2(5),
NEIGHBOR_ZIP VARCHAR2(5));

For each ZIP you determine which zipcodes meets your requirement & enter that pair into the table above.

Hope This Helps!
Re: create a view that has zip codes and nearby zip codes [message #436291 is a reply to message #436289] Tue, 22 December 2009 17:14 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Actually, it is probably doable in a view.

You could select on the table, and include a left outer join on the same table with the condition that lat and long a are in that range.
Re: create a view that has zip codes and nearby zip codes [message #436292 is a reply to message #436291] Tue, 22 December 2009 19:06 Go to previous messageGo to next message
dpace32
Messages: 10
Registered: December 2009
Junior Member
I tried creating a view and doing a join on zip to zip but it only brings back but the original zip code as nearby results.

I could also do a view with MANY unions but I feel like this will just kill performance.
Re: create a view that has zip codes and nearby zip codes [message #436293 is a reply to message #436292] Tue, 22 December 2009 19:10 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>I could also do a view with MANY unions but I feel like this will just kill performance.
A static LOOKUP table would provide desired results very quickly.
Re: create a view that has zip codes and nearby zip codes [message #436294 is a reply to message #436292] Tue, 22 December 2009 19:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You would have to join on the lat and long.

Somewhat like

select t1.POSTAL_CODE,
       t2.POSTAL_CODE
  from location t1
  left outer join location t2
    on t2.LAT  between (t1.LAT  - 0.05) and (t1.LAT  + 0.05)
   and t2.LONG between (t1.LONG - 0.05) and (t1.LONG + 0.05)

[Updated on: Tue, 22 December 2009 19:13]

Report message to a moderator

Re: create a view that has zip codes and nearby zip codes [message #436296 is a reply to message #436294] Tue, 22 December 2009 19:39 Go to previous messageGo to next message
dpace32
Messages: 10
Registered: December 2009
Junior Member
excellent idea, I will try this out first thing when I get to work tomorrow. Thank you all for the help, I will report back.
icon10.gif  Re: create a view that has zip codes and nearby zip codes [message #436404 is a reply to message #436294] Wed, 23 December 2009 07:17 Go to previous message
dpace32
Messages: 10
Registered: December 2009
Junior Member
THIS WORKED!!!!

Thank you very much and happy holidays!

This is awesome, performance is good too!
Previous Topic: need help for insert
Next Topic: delete with trigger (merged many same question)
Goto Forum:
  


Current Time: Sat Dec 03 18:04:13 CST 2016

Total time taken to generate the page: 0.08052 seconds