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
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,
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(
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
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
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`
: 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
[EMAIL PROTECTED]
To: Scott Haneda [EMAIL PROTECTED]; MySql mysql@lists.mysql.com
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
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) *
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
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
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
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
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
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:
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
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
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
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
: 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
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
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
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
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-generalm=110547634827453w=2
--
Greg Donald
Zend
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
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
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
27 matches
Mail list logo
