I want to be able to match state-city-postal codes. So if a user enters a city\state, I can give them the correct zip codes for that area. Or the correct list of cities based on a zip.

I would also like to do radii matches. So if someone in a city-state-zip wants a list of others within a certain radius, I can give them a matching list.

Are there any ready-made schemas to do this?

You don't state what type of database you are using, but PostgreSQL has some nice spatial data types and operators pre-defined.

As far as pre-defined schemas, I'm not aware of any. You'll just need to "roll your own"

I have found downloadable csv files with city, state, zip5, latitude and longitude fields online.

If you're ising postgresql, the spatial operators include one which returns a boolean value if a point lies within a circle or other shape. Since, if you have the lat/long of a zip, you could easily create a query which returns all zip code data where the latitude and longitude fall within a certain radius of the target zip's lat/long...

You don't state what type of database you are using, but PostgreSQL has some nice spatial ddata types pre-defined.

As far as pre-defined schemas, I'm not aware of any. You'll just need to "roll your own"

I have found downloadable csv files with city, state, zip5, latitude and longitude fields online.

If you're using postgresql, the spatial operators include one which returns a boolean value if a point lies within a circle or other shape. Since, if you have the lat/long of a zip, you could easily create a query which returns all zip code data where the latitude and longitude fall within a certain radius of the target zip's lat/long...

Well, I have not chosen a database yet. There are two parts to it: One is the data that drives the web site itself (a dating service), and the other using a radius value to pinpoint matches.

If you have a suggestion on how to best handle this, please let me know.

assume that you have latitude and longitude of user zip code.

although you COULD get into spherical trigonometry, it's easier to assume parallel grids. (this has the effect of all "circles" become elipses, and the further away from the equator, the more pronounced the ellipse. Oh Well - it still works acceptably for many purposes)

At the equator, each degree of latitude/longitude represents approximately 70 miles (25000/360=69.44)

again, assuming planar trig, x^2 + y^2 = z^2, and z=sqrt(x^2 + y^2)

In our case, x is the difference in latitude between zip1 and zip2, and y is the delta longitude between zip1 and zip2.

With your user1 zip, extract the lat & long, then write a sql statement that includes these factors. Something along the lines of

(assuming you want to list all zip codes within a 20 mile radius)

Ok, that makes sense. Is there a table out there that will give me the lat-long associated with postal codes for US and Canada?

I've seen a couple of text files that you could import. They're based on 1999 US data though. Although most zip codes would be included, there may be a few that aren't.

Here's a site where you can download the csv file.

http://red.phoxes.com/red/Downloads/...3/Default.aspx

I haven't even looked for canadian postal codes.

BTW. You could come up with a decent approximation of distance, even at latitudes approaching the artic circle, by dividing the longitude delta by the cosine of the latitude, I believe.

In other words, I'm in the Phoenix, AZ area, at about 33 degrees north.

Instead of looking for anyone in a radius of x degrees east/west/north/south, "stretch" the east/west delta a bit (the cosine of 33 degrees is .839)

Try this

The extra trig functions may make the query too slow, though. Also note that you WILL get an error at the poles... (divide by zero)

canadian codes

Dumped from an existing db. It would be dead easy to use this script with any database server.

Keep in mind. These could get you started, but, since you're developing this for a business, you really should get a subscription, where you can download updated data every month - it's very reasonable. (both countries, on the order of a $100 a year, total.)

This is a huge help, I really appreciate your assitance. A am finding that a dating site is a complex undertaking. It requires much interaction between the system and user. I have already written a mountain of code, and I have not even gotten to the database layer yet.

Then, unfortunately, this probably means that a large part of your code will need to be scrapped or re-written.

You really need to nail down the software design, INCLUDING the database design, before a single line of code gets written.

I do have a software design. I also have a data dictionary and schema. I just did not have the spatial database part nailed down. I am still working on the View navigation and Controller code. I have not gotten to the Persistence layer. But I need to update the schema first with the info you gave me before I start the ORM process.