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
us
: 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.s
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 e
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.01745
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 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:
>
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
>
> 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 fie
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).
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 gree
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 zi
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) *
CO
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 ac
; 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 longitud
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',
TED]
> 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 ex
> 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 Gener
> 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
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 approa
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
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
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 wi
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 t
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
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 Cer
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
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
27 matches
Mail list logo
