Re: zip code search within x miles

[Jeremy Cole]

Hi,
known as "covering indexes".  The advantage to a covering index is that if 
your data is numeric and in the index, the engine can read the data 
All correct, except that the data does not have to be numeric.  It must, 
however, be completely indexed, not indexed by prefix.  That requirement 
usually excludes most BLOB/TEXT fields, so you wouldn't normally include 
a BLOB/TEXT in a covered index discussion.

Covered indexes work fine with CHAR/VARCHAR and are quite common.
Regards,
Jeremy
--
Jeremy Cole
Technical Yahoo - MySQL (Database) Geek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: zip code search within x miles

[gunmuse]

Http://www.gunmuse.com

Ok I use a storelocator.

First if you have 8000 + records it becomes an issue.  BUT  Lat and long is
in minutes and minutes can be used to estimate miles.  By Breaking down the
lat and long,  Breaking down the Zip to a two digit prefix 88254 becomes 88
for indexing (Because the post offices goes in order folks with some
exceptions) Then with a wide lasso you can rope your results to do your math
check with.  Break your lat and long fields up in hours minutes and seconds
and filtering down becomes very easy to do.

Learning to read a map before determining the key and distance calculation
would help better understand this problem.

Thanks
Donny Lairson
President
29 GunMuse Lane
P.O. box 166
Lakewood NM 88254
http://www.gunmuse.com
469 228 2183


-Original Message-
From: Richard Lynch [mailto:[EMAIL PROTECTED]
Sent: Monday, April 25, 2005 12:05 AM
To: Hank
Cc: MySql
Subject: Re: zip code search within x miles


On Tue, April 19, 2005 8:55 am, Hank said:
> Talk about over complicating things... here's the above query simplifed.
>
> I can not figure out why they were self joining the table three times:
>
> SELECT b.zip_code, b.state,
>(3956 * (2 * ASIN(SQRT(
>POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
>COS(a.lat*0.017453293) *
>COS(b.lat*0.017453293) *
>POWER(SIN(((a.lng-b.lng)*0.017453293)/2),2) AS distance
> FROM zips a, zips b
> WHERE
>a.zip_code = '90210'
> GROUP BY distance
> having distance <= 5;

You'd have to time it, and *MAYBE* with enough indices this will all work
out, but you'd probably be better off doing two queries.

One to look up the long/lat for 90210, and another on just zips to
calculate the distance.

Benchmark on your own hardware and see for yourself.  I could be 100% wrong.

--
Like Music?
http://l-i-e.com/artists.htm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Richard Lynch]

On Mon, April 18, 2005 9:16 pm, Hank said:
>> Let's say you've got, oh, 2000 records to search through.
>> You're gonna end up doing a JOIN with:
>> 2,000 X 65,000 == 130,000,000 tuples (records/results).
>> 130 MILLION tuples is *way* too many for your basic $20/month site.
>
> I'd say take some easy shortcuts first... like limit the join to the
> zip code table by the target state (which of course is indexed), then

Might not have the target state, and not all countries have states at all,
and...

> add the one, two or at most three neighboring states, if you're near a
> border.

Well, now, THAT just complicated things all to hell...

How would I know if 60601 in IL is "near" a border or not?

> Or just limit the join to all the neighboring states in one
> shot).

So now I need to lookup the neighboring states, but depending on the
target distance, and/or the state size (Rhode Island, anyone?) maybe
neighboring states are *NOT* a good filter at all.  Take a look at the top
of Idaho some time.  Want to bet there are zips in the states on each side
that are closer than, say, two arbitrary zips in Idaho and a neighboring
state?

> One, two, or three short running queries all in SQL is a
> whole-lot better than adding un-normalized and redundant fields to the
> source data table and populating it in a cron job and triggers.  Talk
> about taking the long way around.

It works. :-)

It's incredibly efficient.

The "un-normalized" data never suffers from the problems which make
un-normalized data "bad" in the first place.

-- 
Like Music?
http://l-i-e.com/artists.htm


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Richard Lynch]

On Tue, April 19, 2005 8:55 am, Hank said:
> Talk about over complicating things... here's the above query simplifed.
>
> I can not figure out why they were self joining the table three times:
>
> SELECT b.zip_code, b.state,
>(3956 * (2 * ASIN(SQRT(
>POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
>COS(a.lat*0.017453293) *
>COS(b.lat*0.017453293) *
>POWER(SIN(((a.lng-b.lng)*0.017453293)/2),2) AS distance
> FROM zips a, zips b
> WHERE
>a.zip_code = '90210'
> GROUP BY distance
> having distance <= 5;

You'd have to time it, and *MAYBE* with enough indices this will all work
out, but you'd probably be better off doing two queries.

One to look up the long/lat for 90210, and another on just zips to
calculate the distance.

Benchmark on your own hardware and see for yourself.  I could be 100% wrong.

-- 
Like Music?
http://l-i-e.com/artists.htm


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Richard Lynch]

On Tue, April 19, 2005 11:55 am, Hank said:
> Except that the zip code field is not (and should not be) numeric, so
> the qualification test fails.

*IF* your zip codes are all US zip 5-digit, and
*IF* performance is really crucial, and
*IF* you are 100% certain you'll never need non-US nor zip+4, and
*IF* MySQL int optimization proved *much* faster...

I could see a valid argument for making the zip field int.

That's a hell of a lot of IFs, and I'm willing to bet that it will bite
you in the butt one day, because you were *wrong* about never needing
internation or zip+4, but there it is.

-- 
Like Music?
http://l-i-e.com/artists.htm


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Jeff Kolber]

On 4/16/05, Scott Gifford <[EMAIL PROTECTED]> wrote:
> Jeff Kolber <[EMAIL PROTECTED]> writes:
> 
> > How are sites doing the search by zip and coming up with results within x
> > miles?  Is there some OSS zip code download that has been created for this?
> 
> The ones I'm familiar with use this:
> 
> http://www.census.gov/geo/www/tiger/zip1999.html
> 
> [...]
> 
> > I would be interested, anyone have insights into doing  with Canadian
> > Zip Codes or otherwise optimizing a database of Canadian Zip Codes?
> 
> I looked into this a few months back (for a very similar project,
> actually) and found many places offering Canadian postal code
> databases, but none for free.  If you already have the database, I
> don't see why optimizing it would be any different than for American
> postal codes.
> 
> ScottG.
> 

Just that there are supposedly 650,000+ canadian codes. Makes you want
to do whatever your doing in an optimal way.

Jeff Kolber

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Scott Haneda]

on 4/19/05 9:32 AM, Keith Ivey at [EMAIL PROTECTED] wrote:

> Hank wrote:
> 
>> Talk about over complicating things... here's the above query simplifed.
>> 
>> I can not figure out why they were self joining the table three times:
> 
> Also, the index on zip_code, latitude, and longitude doesn't
> make sense.  Only the zip_code part of it was used, so it should
> have been on zip_code alone.  Latitude and longitude were only
> used in calculations, so indexing them is useless.
> 
> If additional conditions were added to the WHERE to limit the
> search to a square, then latitude and longitude indexes would be
> useful, but they would have to be separate from each other and
> from zip_code.

Anyone willing to put up a final tested and optimized sql query?
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Hank]

> No, those indexes were intentional.  If you read the section of the manual
> on optimizing queries, you will encounter a page that mentions what are
> known as "covering indexes".  The advantage to a covering index is that if
> your data is numeric and in the index, 

Except that the zip code field is not (and should not be) numeric, so
the qualification test fails.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[SGreen]

Hank <[EMAIL PROTECTED]> wrote on 04/19/2005 01:33:51 PM:

> On 4/19/05, Keith Ivey <[EMAIL PROTECTED]> wrote:
> > Also, the index on zip_code, latitude, and longitude doesn't
> > make sense. 
> 
> Yeah - I didn't even notice the indexes in the table def (I used my
> own existing zip code table).  That table def and query were obviously
> created by someone pretty green with SQL.
> 
> -Hank
> 
No, those indexes were intentional.  If you read the section of the manual 
on optimizing queries, you will encounter a page that mentions what are 
known as "covering indexes".  The advantage to a covering index is that if 
your data is numeric and in the index, the engine can read the data 
DIRECTLY from the index and completely skip all of the disk operations 
required to read the information from the data file.

That index is a covering index and will make any query looking for just 
lat and long against a zip code extremely fast because the engine will not 
need to read the data file to get at the lat and long value because they 
are already in the index.

from: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html
++
In some cases, a query can be optimized to retrieve values without 
consulting the data rows. If a query uses only columns from a table that 
are numeric and that form a leftmost prefix for some key, the selected 
values may be retrieved from the index tree for greater speed:

SELECT key_part3 FROM tbl_name WHERE key_part1=1
++

I guess the person who wrote that query wasn't so green after all, eh? 
(BTW, I was not the author of the OP's query although there is a lot of 
irony in this reply ;-D  )

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: zip code search within x miles

[Hank]

On 4/19/05, Keith Ivey <[EMAIL PROTECTED]> wrote:
> Also, the index on zip_code, latitude, and longitude doesn't
> make sense.  

Yeah - I didn't even notice the indexes in the table def (I used my
own existing zip code table).  That table def and query were obviously
created by someone pretty green with SQL.

-Hank

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Keith Ivey]

Hank wrote:
Talk about over complicating things... here's the above query simplifed.
I can not figure out why they were self joining the table three times:
Also, the index on zip_code, latitude, and longitude doesn't 
make sense.  Only the zip_code part of it was used, so it should 
have been on zip_code alone.  Latitude and longitude were only 
used in calculations, so indexing them is useless.

If additional conditions were added to the WHERE to limit the 
search to a square, then latitude and longitude indexes would be 
useful, but they would have to be separate from each other and 
from zip_code.

--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Hank]

Talk about over complicating things... here's the above query simplifed.

I can not figure out why they were self joining the table three times:

SELECT b.zip_code, b.state,
   (3956 * (2 * ASIN(SQRT(
   POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
   COS(a.lat*0.017453293) *
   COS(b.lat*0.017453293) *
   POWER(SIN(((a.lng-b.lng)*0.017453293)/2),2) AS distance
FROM zips a, zips b
WHERE
   a.zip_code = '90210'
GROUP BY distance
having distance <= 5;


-Hank

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Eamon Daly]

Interestingly enough, I found another great circle
routine here:
http://www.meridianworlddata.com/Distance-Calculation.asp
and adapted it for MySQL like so:
SELECT
b.zipcode, b.state,
3963.0 * acos(
sin(a.latitude/57.2958) * sin(b.latitude/57.2958) +
cos(a.latitude/57.2958) * cos(b.latitude/57.2958) * 
cos(b.longitude/57.2958 - a.longitude/57.2958)
) AS distance
FROM zipcodes a, zipcodes b, zipcodes c
WHERE
a.zipcode = '02134' AND # <-- Your starting zipcode
a.zipcode = c.zipcode AND
3963.0 * acos(
sin(a.latitude/57.2958) * sin(b.latitude/57.2958) +
cos(a.latitude/57.2958) * cos(b.latitude/57.2958) * 
cos(b.longitude/57.2958 - a.longitude/57.2958)
) <= 5 # <-- Your target radius
GROUP BY distance

They both achieve similar results; anyone have a feel for
which is "better"?

Eamon Daly

- Original Message - 
From: "Eamon Daly" <[EMAIL PROTECTED]>
To: "Scott Haneda" <[EMAIL PROTECTED]>; "MySql" 
Sent: Tuesday, April 19, 2005 10:20 AM
Subject: Re: zip code search within x miles


I don't think anyone's replied with an actual great circle
calculation. Here's our zipcode table, populated with data
from zipcodedownload.com (and note the index on zipcode,
latitude, and longitude:
CREATE TABLE `zipcodes` (
 `zipcode` char(5) NOT NULL default '',
 `zipcode_type` enum('S','P','U','M') NOT NULL default 'S',
 `city` char(45) NOT NULL default '',
 `city_type` enum('D','A','N') NOT NULL default 'D',
 `state` char(75) NOT NULL default '',
 `state_code` char(2) NOT NULL default '',
 `area_code` char(3) default NULL,
 `latitude` double(12,6) NOT NULL default '0.00',
 `longitude` double(12,6) NOT NULL default '0.00',
 KEY `city_state_code` (`zipcode`,`city`,`state_code`),
 KEY `position` (`zipcode`,`latitude`,`longitude`)
) TYPE=MyISAM
Here's the calculation to get zipcodes within 5 miles of
02134:
SELECT
b.zipcode, b.state,
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
COS(a.latitude*0.017453293) *
COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
 AS distance
FROM zipcodes a, zipcodes b, zipcodes c
WHERE
a.zipcode = '02134' AND # <-- Your starting zipcode
a.zipcode = c.zipcode AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
COS(a.latitude*0.017453293) *
COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
 <= 5 # <-- Your target radius
GROUP BY distance
Takes about half a second on our box. Here's the explain:
*** 1. row ***
   table: a
type: ref
possible_keys: city_state_code,position,default_city
 key: position
 key_len: 5
 ref: const
rows: 2
   Extra: Using where; Using index; Using temporary; Using filesort
*** 2. row ***
   table: c
type: ref
possible_keys: city_state_code,position,default_city
 key: position
 key_len: 5
 ref: const
rows: 2
   Extra: Using where; Using index
*** 3. row ***
   table: b
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: 70443
   Extra: Using where
In case you're curious about the number of zipcodes:
mysql> select count(*), count(distinct zipcode) from zipcodes;
+--+-+
| count(*) | count(distinct zipcode) |
+--+-+
|70443 |   42471 |
+--+-+
Can't take credit for the SQL, by the way; I'm pretty sure I
found it on Google.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: zip code search within x miles

[Scott Johnson]

Hi,

Just as a speed note! It's faster to calculate a square and find those
records and then drop the corners with the circle calculation.

Scott.

> -Original Message-
> From: Eamon Daly [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 19, 2005 11:20 AM
> To: Scott Haneda; MySql
> Subject: Re: zip code search within x miles
>
>
> I don't think anyone's replied with an actual great circle
> calculation. Here's our zipcode table, populated with data
> from zipcodedownload.com (and note the index on zipcode,
> latitude, and longitude:
>
> CREATE TABLE `zipcodes` (
>   `zipcode` char(5) NOT NULL default '',
>   `zipcode_type` enum('S','P','U','M') NOT NULL default 'S',
>   `city` char(45) NOT NULL default '',
>   `city_type` enum('D','A','N') NOT NULL default 'D',
>   `state` char(75) NOT NULL default '',
>   `state_code` char(2) NOT NULL default '',
>   `area_code` char(3) default NULL,
>   `latitude` double(12,6) NOT NULL default '0.00',
>   `longitude` double(12,6) NOT NULL default '0.00',
>   KEY `city_state_code` (`zipcode`,`city`,`state_code`),
>   KEY `position` (`zipcode`,`latitude`,`longitude`)
> ) TYPE=MyISAM
>
> Here's the calculation to get zipcodes within 5 miles of
> 02134:
>
> SELECT
> b.zipcode, b.state,
> (3956 * (2 * ASIN(SQRT(
>  POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
>  COS(a.latitude*0.017453293) *
>  COS(b.latitude*0.017453293) *
>  POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
>  AS distance
> FROM zipcodes a, zipcodes b, zipcodes c
> WHERE
> a.zipcode = '02134' AND # <-- Your starting zipcode
> a.zipcode = c.zipcode AND
> (3956 * (2 * ASIN(SQRT(
>  POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
>  COS(a.latitude*0.017453293) *
>  COS(b.latitude*0.017453293) *
>  POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
>  <= 5 # <-- Your target radius
> GROUP BY distance
>
> Takes about half a second on our box. Here's the explain:
>
> *** 1. row ***
> table: a
>  type: ref
> possible_keys: city_state_code,position,default_city
>   key: position
>   key_len: 5
>   ref: const
>  rows: 2
> Extra: Using where; Using index; Using temporary; Using filesort
> *** 2. row ***
> table: c
>  type: ref
> possible_keys: city_state_code,position,default_city
>   key: position
>   key_len: 5
>   ref: const
>  rows: 2
> Extra: Using where; Using index
> *** 3. row ***
> table: b
>  type: ALL
> possible_keys: NULL
>   key: NULL
>   key_len: NULL
>   ref: NULL
>  rows: 70443
> Extra: Using where
>
> In case you're curious about the number of zipcodes:
>
> mysql> select count(*), count(distinct zipcode) from zipcodes;
> +--+-+
> | count(*) | count(distinct zipcode) |
> +--+-+
> |70443 |   42471 |
> +--+-+
>
> Can't take credit for the SQL, by the way; I'm pretty sure I
> found it on Google.
>
> 
> Eamon Daly
>
>
>
> - Original Message -
> From: "Scott Haneda" <[EMAIL PROTECTED]>
> To: "MySql" 
> Sent: Friday, April 15, 2005 5:37 PM
> Subject: zip code search within x miles
>
>
> > How are sites doing the search by zip and coming up with
> results within x
> > miles?  Is there some OSS zip code download that has been created for
> > this?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Eamon Daly]

I don't think anyone's replied with an actual great circle
calculation. Here's our zipcode table, populated with data
from zipcodedownload.com (and note the index on zipcode,
latitude, and longitude:
CREATE TABLE `zipcodes` (
 `zipcode` char(5) NOT NULL default '',
 `zipcode_type` enum('S','P','U','M') NOT NULL default 'S',
 `city` char(45) NOT NULL default '',
 `city_type` enum('D','A','N') NOT NULL default 'D',
 `state` char(75) NOT NULL default '',
 `state_code` char(2) NOT NULL default '',
 `area_code` char(3) default NULL,
 `latitude` double(12,6) NOT NULL default '0.00',
 `longitude` double(12,6) NOT NULL default '0.00',
 KEY `city_state_code` (`zipcode`,`city`,`state_code`),
 KEY `position` (`zipcode`,`latitude`,`longitude`)
) TYPE=MyISAM
Here's the calculation to get zipcodes within 5 miles of
02134:
SELECT
b.zipcode, b.state,
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
COS(a.latitude*0.017453293) *
COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
 AS distance
FROM zipcodes a, zipcodes b, zipcodes c
WHERE
a.zipcode = '02134' AND # <-- Your starting zipcode
a.zipcode = c.zipcode AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
COS(a.latitude*0.017453293) *
COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
 <= 5 # <-- Your target radius
GROUP BY distance
Takes about half a second on our box. Here's the explain:
*** 1. row ***
   table: a
type: ref
possible_keys: city_state_code,position,default_city
 key: position
 key_len: 5
 ref: const
rows: 2
   Extra: Using where; Using index; Using temporary; Using filesort
*** 2. row ***
   table: c
type: ref
possible_keys: city_state_code,position,default_city
 key: position
 key_len: 5
 ref: const
rows: 2
   Extra: Using where; Using index
*** 3. row ***
   table: b
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: 70443
   Extra: Using where
In case you're curious about the number of zipcodes:
mysql> select count(*), count(distinct zipcode) from zipcodes;
+--+-+
| count(*) | count(distinct zipcode) |
+--+-+
|70443 |   42471 |
+--+-+
Can't take credit for the SQL, by the way; I'm pretty sure I
found it on Google.
____________
Eamon Daly

- Original Message - 
From: "Scott Haneda" <[EMAIL PROTECTED]>
To: "MySql" 
Sent: Friday, April 15, 2005 5:37 PM
Subject: zip code search within x miles


How are sites doing the search by zip and coming up with results within x
miles?  Is there some OSS zip code download that has been created for 
this?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: zip code search within x miles

[Scott Johnson]

Hi all,

I have done this type of setup before. There is a company that puts out a
zipcode file with the Latitude and longitude in it. You can then calculate
what's in the distance you are looking for.

Scott.


> -Original Message-
> From: Robert Dunlop [mailto:[EMAIL PROTECTED]
> Sent: Friday, April 15, 2005 6:51 PM
> To: Scott Haneda; MySql
> Subject: Re: zip code search within x miles
>
>
> I haven't looked into this recently, but as long as 15  years ago
> there were
> zip -> mile libraries available; I assume similar libraries still exist.
> The ones I worked with all used only the first 3 digits.  Full five digit
> and nine digit zips get to pretty specific geographic locations,
> a level of
> accuracy not needed when telling people what resource exist within "x"
> miles.   You might try google something like: (zipcode OR zip)  distance
> software library
> HTH
>
> Bob
> - Original Message -
> From: "Scott Haneda" <[EMAIL PROTECTED]>
> To: "MySql" 
> Sent: Friday, April 15, 2005 3:37 PM
> Subject: zip code search within x miles
>
>
> > How are sites doing the search by zip and coming up with
> results within x
> > miles?  Is there some OSS zip code download that has been created for
> this?
> > --
> > -
> > Scott HanedaTel: 415.898.2602
> > <http://www.newgeo.com> Novato, CA U.S.A.
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Hank]

> Applying this same thing to apply to the 80k estimated US zipcodes
> currently 

Just for the record, there are about 43,000 distinct US zip codes...
and 56,000 zip codes if you double count the zips with multiple city
names (when zip codes cross city limits).

-Hank

mysql, query

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Hank]

> Let's say you've got, oh, 2000 records to search through.
> You're gonna end up doing a JOIN with:
> 2,000 X 65,000 == 130,000,000 tuples (records/results).
> 130 MILLION tuples is *way* too many for your basic $20/month site.

I'd say take some easy shortcuts first... like limit the join to the
zip code table by the target state (which of course is indexed), then
add the one, two or at most three neighboring states, if you're near a
border.  Or just limit the join to all the neighboring states in one
shot). One, two, or three short running queries all in SQL is a
whole-lot better than adding un-normalized and redundant fields to the
source data table and populating it in a cron job and triggers.  Talk
about taking the long way around.

And if anyone is looking for a datafile with worldwide cities (about
2.7 million records) and their lat/longs (not zips, though), here's a
place to download it for free:

http://www.maxmind.com/app/worldcities

For $50, you can get the addition of population of each city.

-Hank

mysql, query

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[paris lundis]

I'll share my creative workaround for geographic searches within x miles.
This is a theory I developed years ago and have used in live production
systems for the last five years. This method to be described is used
to produce approximately 100-200k sets of live nearby data per day online.
My approach is necessary for ColdFusion application server display of
relative nearby geographic data on my very busy website: 
http://www.pubcrawler.com
I use MySQL for the database storage with some MS SQL.

First, there are several different formulas readily available as math by 
which to
re-engineer for your particular use. The formulas should be able to be 
ported to any
platform/language given the math functions needed are available.

My concept  is simple and I believe  users are willing to accept some 
small margin of
error. This meaning, a place say a mile or two over further in one 
direction.

Zipcodes are notoriously odd in their adoption in areas. They follow a 
North to South and East to West incrementation.
Certain inferences can simply be made about a high zipcode or a low one 
belonging to either coast.

Nearby zipcodes are minimally useful, other than to say here are other 
zipcodes (say for instance of a real estate
application). The wild goose chase.

By all means, you need to reduce the amount of math that goes into 
preparing a query and say a page in your
application.

What I do, to avoid pre-compilation - that is making all the possible or 
common possible results already plotted
and thereby pre-planned - is work on establishing the minimums and 
maximums at four values - these are a min and max for both lat and lon:

NW| NE
   |
--
   |
SW  |SE
I establish these as the max and mins based on converting the distance 
(fraction of mile to many miles) to degrees and doing the math to 
calculate each of the four points.

With these four points I can make a very simple and fast comparison 
where latitude =< maxlat and latitude => minlat and longitude =< maxlon 
and longitude >= minlon

This approach works on a square and gives you an error margin which is 
the difference of placing the distance exhibited as a circle within the 
same space exhibited as a square.
The error or overlap areas are minimal and for terms of consumer based 
services will suffice. These areas are triangles with the inward side 
being an arc.

I use this approach to relate okay sized sets of data to one another on 
the fly (500k restaurants, 6000 beer establishments, 13k bed and 
breakfasts, 50k hotels, etc.)

All the math is and can be done on the fly this way on commodity 
hardware (800Mhz server) on the application server level.  Bringing this 
to a pure
SQL level would increase the speed further.

A correlation for nearby hotels with this approach will run in a 
multi-use environment on average in about four seconds. With further 
optimization this can be
reduce to around 1 second..

This approach is a compromise to more exact math offered elsewhere on 
the internet.

Applying this same thing to apply to the 80k estimated US zipcodes 
currently would be pie and would run faster just due to the reduced dataset.

Zipcode databases are readily available with quarterly subscription 
updates available today. I believe the US Postal Service even sells a 
dataset now.

Richard Lynch wrote:
You can buy up-to-the-minute zips, or snag TIGER (or gazateer?) data
that's a bit old for free.
It's a 1-1 mapping of zip to long/lat.
The tricky bit is this.
There's about 65,000 zips, even in the out-dated list for free.
Let's say you've got, oh, 2000 records to search through.
You're gonna end up doing a JOIN with:
2,000 X 65,000 == 130,000,000 tuples (records/results).
130 MILLION tuples is *way* too many for your basic $20/month site.
So what you do is break the rules.
That's right, it's rule-breaking time.
First, add a longitude and latitude column to your 2000 record table,
default to NULL.
Next, add a trigger (in MySQL 4.1+ I think) so that any time you change
the country or zip, the latitude/longitude gets looked up in the zips
table, and filled in.
If you can't do triggers (in MySQL pre-4.1) you'll have to arrange your
Busicness Logic such that any time you UPDATE country or zip in your 2000
records table, you reset longitude/latitude to NULL.
Next, write a cron job to snag, say, 10 records from your table "WHERE
latitude is NULL" in random order.
Lookup the country and zip in your zips table, and get your $latitude and
$longitude for that country/zip then do:
update EXAMPLE set latitude = $latitude, longitude = $longitude where
country = '$country' and zip = '$zip'
Errr, I kinda slipped into PHP there, but you probably know what I mean. 
$x is a variable holding the values you looked up.

Run that cron job a whole lot at first, by hand, to get mostly all the
2000 records fixed up with the right long/lat.
The point of this is that *NOW* you only have to search thr

Re: zip code search within x miles

[Richard Lynch]

You can buy up-to-the-minute zips, or snag TIGER (or gazateer?) data
that's a bit old for free.

It's a 1-1 mapping of zip to long/lat.

The tricky bit is this.

There's about 65,000 zips, even in the out-dated list for free.

Let's say you've got, oh, 2000 records to search through.

You're gonna end up doing a JOIN with:
2,000 X 65,000 == 130,000,000 tuples (records/results).

130 MILLION tuples is *way* too many for your basic $20/month site.

So what you do is break the rules.

That's right, it's rule-breaking time.

First, add a longitude and latitude column to your 2000 record table,
default to NULL.

Next, add a trigger (in MySQL 4.1+ I think) so that any time you change
the country or zip, the latitude/longitude gets looked up in the zips
table, and filled in.

If you can't do triggers (in MySQL pre-4.1) you'll have to arrange your
Busicness Logic such that any time you UPDATE country or zip in your 2000
records table, you reset longitude/latitude to NULL.

Next, write a cron job to snag, say, 10 records from your table "WHERE
latitude is NULL" in random order.

Lookup the country and zip in your zips table, and get your $latitude and
$longitude for that country/zip then do:

update EXAMPLE set latitude = $latitude, longitude = $longitude where
country = '$country' and zip = '$zip'

Errr, I kinda slipped into PHP there, but you probably know what I mean. 
$x is a variable holding the values you looked up.

Run that cron job a whole lot at first, by hand, to get mostly all the
2000 records fixed up with the right long/lat.

The point of this is that *NOW* you only have to search through 2000
records for your distance function, which sure beats a join with 130
million records, eh?

Also, if you just want the NEAREST matches, forget all that complicated
crap about Great Circle distance and expensive trigonometric calculations.

You can just get the zip they want to search by, lookup $longitude and
$latitude for that zip, and then use:

select {application_data_here), abs(latitude - $latitude) + abs(longitude
- $longitude) as closest from ... order by closest

If MySQL won't let you use 'closest' in the where clause, just move the
calculations there.

I've done this before for clients who didn't want to muck with all this. 
It's a few hours' work.  Call it $200.

PS I'm working on a GLOBAL POSTAL system which will data-gather OSS
long/lat and country/zip for the entire world, rather than the current
mess of having only the US for free, and maybe Canada and Australia, but
not much else is free, and often not even available.

I guess I'd better post here when I launch that.  No time-line available yet.

On Fri, April 15, 2005 3:37 pm, Scott Haneda said:
> How are sites doing the search by zip and coming up with results within x
> miles?  Is there some OSS zip code download that has been created for
> this?
> --
> -
> Scott HanedaTel: 415.898.2602
>  Novato, CA U.S.A.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
Like Music?
http://l-i-e.com/artists.htm


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Scott Gifford]

Jeff Kolber <[EMAIL PROTECTED]> writes:

> How are sites doing the search by zip and coming up with results within x
> miles?  Is there some OSS zip code download that has been created for this?

The ones I'm familiar with use this:

http://www.census.gov/geo/www/tiger/zip1999.html

[...]

> I would be interested, anyone have insights into doing  with Canadian
> Zip Codes or otherwise optimizing a database of Canadian Zip Codes?

I looked into this a few months back (for a very similar project,
actually) and found many places offering Canadian postal code
databases, but none for free.  If you already have the database, I
don't see why optimizing it would be any different than for American
postal codes.

ScottG.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Jeff Kolber]

How are sites doing the search by zip and coming up with results within x
miles?  Is there some OSS zip code download that has been created for this?

I have to do this on a high volume realestate website - we use the
great circle concept to calculate the limits on longitudes and
latitudes to be within distance n. We then select the zip codes within
these limits and join on the zip code to the property info. We also
calculate the distance from the center of the search to the found
property.

As our traffic grew I started storing the longitude and latitude in
radians as well, which relieved the database from doing some math.

I have also seen that we've had to update the database about twice a
year to keep up with the USPS - mainly in terms of which counties are
in which zip codes - which may or may not change more frequently in
the period before an election.

I would be interested, anyone have insights into doing  with Canadian
Zip Codes or otherwise optimizing a database of Canadian Zip Codes?

Jeff Kolber
http://www.ForSaleByOwner.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: zip code search within x miles

[gunmuse]

I have a copy of the Zip Code db for MySql.  Its a few years old but should
be 99% accurate compared to new ones.


Thanks
Donny Lairson
President
29 GunMuse Lane
P.O. box 166
Lakewood NM 88254
http://www.gunmuse.com
469 228 2183


-Original Message-
From: Scott Gifford [mailto:[EMAIL PROTECTED]
Sent: Friday, April 15, 2005 6:01 PM
To: Scott Haneda
Cc: MySql
Subject: Re: zip code search within x miles


Scott Haneda <[EMAIL PROTECTED]> writes:

> How are sites doing the search by zip and coming up with results within x
> miles?  Is there some OSS zip code download that has been created for
this?

Zipdy does most of what you want; it needs to be modified to support
MySQL instead of PostgreSQL, but that shouldn't be too hard.  It also
has the great circle function you need to calculate the distances
correctly.  You can get it from:

http://www.cryptnet.net/fsp/zipdy/

If you're using Perl, Geo::PostalCode works very well, though it
doesn't use an SQL database at all:

http://search.cpan.org/~tjmather/Geo-PostalCode-0.06/lib/Geo/PostalCode.
pm

---ScottG.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Scott Gifford]

Scott Haneda <[EMAIL PROTECTED]> writes:

> How are sites doing the search by zip and coming up with results within x
> miles?  Is there some OSS zip code download that has been created for this?

Zipdy does most of what you want; it needs to be modified to support
MySQL instead of PostgreSQL, but that shouldn't be too hard.  It also
has the great circle function you need to calculate the distances
correctly.  You can get it from:

http://www.cryptnet.net/fsp/zipdy/

If you're using Perl, Geo::PostalCode works very well, though it
doesn't use an SQL database at all:

http://search.cpan.org/~tjmather/Geo-PostalCode-0.06/lib/Geo/PostalCode.pm

---ScottG.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Greg Donald]

On 4/15/05, Scott Haneda <[EMAIL PROTECTED]> wrote:
> How are sites doing the search by zip and coming up with results within x
> miles?  Is there some OSS zip code download that has been created for this?

http://marc.theaimsgroup.com/?l=php-general&m=110547634827453&w=2


-- 
Greg Donald
Zend Certified Engineer
http://destiney.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: zip code search within x miles

[Robert Dunlop]

I haven't looked into this recently, but as long as 15  years ago there were
zip -> mile libraries available; I assume similar libraries still exist.
The ones I worked with all used only the first 3 digits.  Full five digit
and nine digit zips get to pretty specific geographic locations, a level of
accuracy not needed when telling people what resource exist within "x"
miles.   You might try google something like: (zipcode OR zip)  distance
software library
HTH

Bob
- Original Message - 
From: "Scott Haneda" <[EMAIL PROTECTED]>
To: "MySql" 
Sent: Friday, April 15, 2005 3:37 PM
Subject: zip code search within x miles


> How are sites doing the search by zip and coming up with results within x
> miles?  Is there some OSS zip code download that has been created for
this?
> -- 
> -
> Scott HanedaTel: 415.898.2602
> <http://www.newgeo.com> Novato, CA U.S.A.
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: zip code search within x miles

[gunmuse]

We convert the zip code into a Lat and long.  Run the math looking for all
other zips in that area, then convert that back to lat long for a mileage
calculation of each.

I know there's a better way to do this we just haven't seen the benefit in
rewriting it now.

Watch PHP a lot of this in coming out in functions, and Perl already has
some functions to do this I believe you just need the db.

Thanks
Donny Lairson
President
29 GunMuse Lane
P.O. box 166
Lakewood NM 88254
http://www.gunmuse.com
469 228 2183


-Original Message-
From: Scott Haneda [mailto:[EMAIL PROTECTED]
Sent: Friday, April 15, 2005 4:38 PM
To: MySql
Subject: zip code search within x miles


How are sites doing the search by zip and coming up with results within x
miles?  Is there some OSS zip code download that has been created for this?
--
-
Scott HanedaTel: 415.898.2602
<http://www.newgeo.com> Novato, CA U.S.A.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

zip code search within x miles

[Scott Haneda]

How are sites doing the search by zip and coming up with results within x
miles?  Is there some OSS zip code download that has been created for this?
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]