ORDER BY Zip Code Distance

**Doug Hutcheson** · Jul 17 '05, 03:19 AM

Re: ORDER BY Zip Code Distance

"Jochen Daum" <jochen.daum@ca ns.co.nz> wrote in message
news:erjo20pems vi0dkshvmav7l9j gu1u6rqlh@4ax.c om...[color=blue]
> Hi!
>
>
>
>
> On Fri, 13 Feb 2004 03:32:45 GMT, "Doug Hutcheson"
> <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote:
>[color=green]
> >Jochen,
> >That is interesting. Can you give more details? As I read the op, the
> >problem was to find how many points from his database lay within x miles[/color][/color]
of[color=blue][color=green]
> >given point y. I am not a GIS expert by any menas, but I can't see how to
> >avoid doing the trig. each time? What am I missing? I love to learn new
> >stuff like this.[/color]
>
> Made a calculation mistake there, but it still goes with less area and
> accuracy:
>
> Store a table with fields
>
> srclong tinyint
> srclongmin tinyint
> srclat tinyint
> srclatmin tinyint
> distance float
> trglong tinyint
> trglongmin tinyint
> trglat tinyint
> trglatmin tinyint
>
> with a clustered index on the first 5 fields.
>
> Then save all possible combinations with their distance into the
> database. A calculation example:
>
> All of US is less than 30 by 60 degrees latitude/longitude (sorry if I
> mix it up). If we store 0 and 30 minutes positions only, thats
>
> ((30*2*60*2)^2) *20 bytes = 1036800000 bytes
>
> Thats for 7200 locations. Good thing is, it performs always the same
> for the same number of locations, no matter what actual value you
> store for minutes. The other performance factor is only the number of
> records returned, but you would want to limit that anyway for a web
> page.
>
> HTH, Jochen
>
>[color=green]
> >Cheers,
> >Doug[/color]
>
> --
> Jochen Daum - Cabletalk Group Ltd.
> PHP DB Edit Toolkit -- PHP scripts for building
> database editing interfaces.
> http://sourceforge.net/projects/phpdbedittk/[/color]

Jochen,
That looks like a great idea.
Thank you very much for the insight.
Cheers,
Doug
--
Remove the blots from my address to reply

**Xenophobe** · Jul 17 '05, 03:21 AM

Re: ORDER BY Zip Code Distance

Jochen,

You're right, "huge" is relative.

The zip code table consists of over 52K records. The company supplier lists
it's joined with is under 3K.

By "indexing", do you mean MySQL indexing? If so, I've didn't have much luck
with that. It didn't seem to speed things up although it's quite possible
the wrong columns were indexed or not enough space was allocated.

Here's the schema for the two tables. Can you recommend which tables to
index and what sizes to define?

Any suggestions would be appreciated.

Thanks!

----------------------------------------

TABLE: Locations

LocationID
ZipCode
City
State
StatusCode
AreaCode
TimeZone
Latitude
Longitude

TABLE: Suppliers

SupplierID
SupplierName1
SupplierName2
Address1
Address2
City
State
Zip
Phone

"Jochen Daum" <jochen.daum@ca ns.co.nz> wrote in message
news:lbco20pjvn 847b6153d9od19v hf1j38ocq@4ax.c om...[color=blue]
> Hi!
>
> On Fri, 13 Feb 2004 01:34:00 GMT, "Xenophobe" <xenophobe@plan etx.com>
> wrote:
>[color=green]
> >Doug,
> >
> >This is just a quick follow-up. The zip sort by distance works great
> >except... the performance is pokey for searches of 20+ miles. I could[/color][/color]
simply[color=blue][color=green]
> >sort the results by company name (ditching the beautiful syntax you help[/color][/color]
me[color=blue][color=green]
> >with!), but even this won't save enough cycles for broader searches.
> >
> >The options (in my view) are either break out the results in multiple[/color][/color]
pages[color=blue][color=green]
> >or pre-calculate every possible lat and long and store them in the[/color][/color]
database[color=blue][color=green]
> >(creating one huge table!) Can you think of anything that might help?[/color]
>
> Well, depends what you think is huge. It might still perform very well
> with proper indexing. How many rows are we talking?
>
> Eg. half the world fits in around 4 GB of data for going down to
> minutes, without even thinking about a good encoding for your
> latitude/ longitude index. So this would mean around 4 disk accesses,
> after that you read payload data.
>
> Of course I expect you are measuring only one country, so you'll have
> much less data.
>
> HTH, Jochen
> --
> Jochen Daum - Cabletalk Group Ltd.
> PHP DB Edit Toolkit -- PHP scripts for building
> database editing interfaces.
> http://sourceforge.net/projects/phpdbedittk/[/color]

**Xenophobe** · Jul 17 '05, 03:21 AM

Re: ORDER BY Zip Code Distance

Doug,

As always, thanks for your detailed reply.

I understand the reasons why the query takes time. It's probably working as
fast as possible and can't really be tweaked much more. I will probably have
to break up the results in multiple pages. Pre-calulating every combination
of lat and long seems like overkill for this particular project.

To be continued...

"Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote
in message news:CcWWb.668$ KW.40486@news.o ptus.net.au...[color=blue]
> Xenophobe,
> Glad it runs, but I'm not the really clever one who gave you the[/color]
algorithm,[color=blue]
> so
> I can't help much with that side of things.
>
> In general, I would suggest you try:
>
> 1. Writing the algorithm as a tiny executable in something which executes
> fast: eg C and call it from your script, to see if that works faster. I
> expect it will not be any better, because of the overhead of context
> switching from PHP to shell and back.
>
> 2. Buy, beg, borrow or steal appropriate code from a geographic[/color]
information[color=blue]
> system. I did a Google on "php gis" and came up with oodles of hits, so[/color]
that[color=blue]
> might be profitable to pursue.
>
> 3. Offload the calculation thread to a Cray II or better <grin>.
>
> 4. Tell your user that "this might take a while..." when they kick it off
> and run the process as an asynchronous thread, which writes its results to[/color]
a[color=blue]
> file. Give the user a link to a page which will tell him when his results
> are available and, in turn, link him to them when they are ready.
>
> The problem is you are asking for a complex triginometric calculation to[/color]
be[color=blue]
> performed on every row you select from the database. There is just no easy
> way around the issue, as long as you have an arbitrary geographic[/color]
coordinate[color=blue]
> to start from and look for results within an arbitrary radius.
>
> Don't forget that the select statement has to run over ALL your rows which
> fall inside the bounding box defined by your high/low Lat/Long values, in
> order to decide whether they meet the radius condition. You might try
> reducing the bounding box in order to reduce the number of records[/color]
selected,[color=blue]
> but that might defeat your object.
>
> HTH
> Doug
>
> --
> Remove the blots from my address to reply
> "Xenophobe" <xenophobe@plan etx.com> wrote in message
> news:cQVWb.2975 27$xy6.1461753@ attbi_s02...[color=green]
> > Doug,
> >
> > This is just a quick follow-up. The zip sort by distance works great
> > except... the performance is pokey for searches of 20+ miles. I could[/color]
> simply[color=green]
> > sort the results by company name (ditching the beautiful syntax you help[/color]
> me[color=green]
> > with!), but even this won't save enough cycles for broader searches.
> >
> > The options (in my view) are either break out the results in multiple[/color]
> pages[color=green]
> > or pre-calculate every possible lat and long and store them in the[/color]
> database[color=green]
> > (creating one huge table!) Can you think of anything that might help?
> >
> > Thanks again for all your help!
> >
> > p.s. the handle "Xenophobe" comes from the arcade game, specifically the
> > excellent Atari Lynx port. I still link several systems together and[/color][/color]
play[color=blue][color=green]
> > from time to time. :-)
> >
> > "Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au>[/color][/color]
wrote[color=blue][color=green]
> > in message news:WSxWb.632$ KW.38860@news.o ptus.net.au...[color=darkred]
> > > Xenophobe,
> > >
> > > Glad to help. Just proves how easily we can get engrossed in the[/color]
> > interesting[color=darkred]
> > > bits of complexity and overlook simple things like punctuation.
> > >
> > > Anyway, perhaps all this help from around the globe will go some way[/color]
> > toward[color=darkred]
> > > curing your xenophobia.
> > > <grin>
> > >
> > > Cheers,
> > > Doug (Brisbane, Australia)
> > > --
> > > Remove the blots from my address to reply
> > > "Xenophobe" <xenophobe@plan etx.com> wrote in message
> > > news:QHsWb.2728 19$I06.2925114@ attbi_s01...
> > > > Doug,
> > > >
> > > > I missed that and failed the test. The last item in the SELECT[/color][/color][/color]
clause[color=blue][color=green][color=darkred]
> > > > doesn't need a comma, but "l.longitud e" isn't the last item with the
> > > > addition of "distance". <excuse>I've been pounding my head for too[/color][/color]
> long[color=green][color=darkred]
> > > > trying to resolve this problem.</excuse> In addition to learning[/color][/color][/color]
some[color=blue][color=green][color=darkred]
> > > handy
> > > > SQL syntax, it's also reminded me that things are not always what[/color][/color][/color]
they[color=blue][color=green][color=darkred]
> > > > appear to be.
> > > >
> > > > ANWAY, I sincerely appreciate your assistance--thanks!
> > > >
> > > > "Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au>[/color]
> > wrote[color=darkred]
> > > > in message news:kciWb.612$ KW.37661@news.o ptus.net.au...
> > > > > Top posting corrected...see below
> > > > > > "Doug Hutcheson"[/color][/color]
> <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au>[color=green][color=darkred]
> > > > wrote
> > > > > > in message news:DDDVb.512$ KW.33452@news.o ptus.net.au...
> > > > > > > --
> > > > > > > Remove the blots from my address to reply
> > > > > > > "Xenophobe" <xenophobe@plan etx.com> wrote in message
> > > > > > > news:9cDVb.2580 63$na.418618@at tbi_s04...
> > > > > > > > Tom, thanks for your response.
> > > > > > > >
> > > > > > > > I modified my query and added the lines you provided as
> > > illustrated
> > > > in
> > > > > > > your
> > > > > > > > example.
> > > > > > > >
> > > > > > > > $sql = "SELECT s.suppliername, s.address1, s.address2,[/color][/color][/color]
s.city,[color=blue][color=green][color=darkred]
> > > > > s.state,
> > > > > > > > s.zip, s.phone, l.latitude, l.longitude ";
> > > > > > > > $sql .= "FROM Suppliers s ";
> > > > > > > > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > > > > > > > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > > > > > > > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > > > > > > > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > > > > > > > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > > > > > > > $sql .=
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
"(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) *SIN(RADIANS($l atitude))+COS(R A[color=blue][color=green][color=darkred]
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
DIANS(l.latitud e))*COS(RADIANS ($latitude))*CO S(RADIANS(l.lon gitude-$longitud[color=blue][color=green][color=darkred]
> > > > > > > > e))))) AS distance ";
> > > > > > > > $sql .= "ORDER BY distance";
> > > > > > > >
> > > > > > > > I get the following error message:
> > > > > > > >
> > > > > > > > Warning: Supplied argument is not a valid MySQL result[/color][/color]
> resource[color=green][color=darkred]
> > > > > > > >
> > > > > > > > Commenting the last two lines makes the query servicable[/color][/color]
> again.[color=green][color=darkred]
> > > > > > > >
> > > > > > > > Any thoughts?
> > > > > > > >
> > > > > > > > "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> > > > > > > > news:CWAVb.2283 2$VW4.3380@news svr25.news.prod igy.com...
> > > > > > > > >
> > > > > > > > > On 8-Feb-2004, "Xenophobe" <xenophobe@plan etx.com> wrote:
> > > > > > > > >
> > > > > > > > > > As described the high and low latitudes and longitudes[/color][/color][/color]
are[color=blue][color=green][color=darkred]
> > > > > > > > pre-calculated
> > > > > > > > > > and passed to the query.
> > > > > > > > > >
> > > > > > > > > > SELECT *
> > > > > > > > > > FROM Locations
> > > > > > > > > > WHERE Latitude <= $HighLatitude
> > > > > > > > > > AND Latitude >= $LowLatitude
> > > > > > > > > > AND Longitude >= $LowLongitude
> > > > > > > > > > AND Longitude <= $HighLongitude
> > > > > > > > > >
> > > > > > > > > > I then calculate the actual distance using the latitude[/color][/color]
> and[color=green][color=darkred]
> > > > > > longitude
> > > > > > > > for
> > > > > > > > > > each zip returned.
> > > > > > > > > >
> > > > > > > > > > This works great and saves lots of cycles. The downside[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > this
> > > > > > > > technique
> > > > > > > > > > is
> > > > > > > > > > the inability to sort by distance. Can someone suggest a
> > > simple
> > > > > way
> > > > > > to
> > > > > > > > > > calculate and ORDER BY distance within the query?
> > > > > > > > >
> > > > > > > > > Put the following in your SQL ($lat and $long are the zero
> > > > distance
> > > > > > > point)
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(Latitude))*SI N(RADIANS($lat) )+COS(RADIANS(L a[color=blue][color=green][color=darkred]
> > > > > > > > titude))*
> > > > > > > > > COS(RADIANS($la t))*COS(RADIANS (Longitude-$long))))) AS[/color]
> > Distance[color=darkred]
> > > > > > > > >
> > > > > > > > > will create the Distance 'column'
> > > > > > > > >
> > > > > > > > > ORDER BY Distance
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Tom Thackrey
> > > > > > > > > www.creative-light.com
> > > > > > > > > tom (at) creative (dash) light (dot) com
> > > > > > > > > do NOT send email to jamesbutler@wil lglen.net (it's[/color][/color][/color]
reserved[color=blue][color=green]
> > for[color=darkred]
> > > > > > > spammers)
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > > Xenophobe,
> > > > > > >
> > > > > > > You need to put the calculated value selection in the 'SELECT'
> > > clause,
> > > > > not
> > > > > > > the 'WHERE' clause. What you are trying to do is 'select' a
> > > calculated
> > > > > > value
> > > > > > > as if it was just another field in the underlying database.
> > > > > > >
> > > > > > > I have reformatted the following to avoid odd line breaks, but[/color][/color]
> the[color=green][color=darkred]
> > > > > > > SQLstatement is exactly the same as you posted, except that[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > clauses
> > > > > > have
> > > > > > > been rearranged as required:
> > > > > > >
> > > > > > > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city,[/color][/color]
> ";[color=green][color=darkred]
> > > > > > > $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude ";
> > > > > > > $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> > > > > > > $sql .= "*SIN(RADIANS($ latitude))+COS( RADIANS(l.latit ude))";
> > > > > > > $sql .=
> > > > > >[/color][/color]
> "*COS(RADIANS($ latitude))*COS( RADIANS(l.longi tude-$longitude))))) ";[color=green][color=darkred]
> > > > > > > $sql .= " AS distance ";
> > > > > > > $sql .= "FROM Suppliers s ";
> > > > > > > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > > > > > > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > > > > > > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > > > > > > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > > > > > > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > > > > > > $sql .= "ORDER BY distance";
> > > > > > >
> > > > > > > HTH
> > > > > > > Doug
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > "Xenophobe" <xenophobe@plan etx.com> wrote in message
> > > > > news:a%gWb.2687 22$I06.2878665@ attbi_s01...
> > > > > > Doug,
> > > > > >
> > > > > > I cut & pasted the query, but alas it still returns the same[/color][/color][/color]
error[color=blue][color=green]
> > as[color=darkred]
> > > > > > before.
> > > > > >
> > > > > > Perhaps it's the MySQL version. I'm currently running[/color][/color]
> 3.23.29a-gamma[color=green][color=darkred]
> > > on
> > > > > > Linux.
> > > > > >
> > > > > > I don't have any problems with a variety of other queries,[/color][/color]
> although[color=green][color=darkred]
> > > most
> > > > > do
> > > > > > not have the math.
> > > > > >
> > > > > > Here's an example of a populated query:
> > > > > >
> > > > > > SELECT DISTINCT s.suppliername, s.address1, s.address2, s.city,
> > > s.state,
> > > > > > s.zip, s.phone, l.latitude, l.longitude
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(l.latitude))* SIN(RADIANS(41. 386533))+COS(RA D[color=blue][color=green][color=darkred]
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
IANS(l.latitude ))*COS(RADIANS( 41.386533))*COS (RADIANS(l.long itude--82.234199[color=blue][color=green][color=darkred]
> > > > > > ))))) AS distance FROM Suppliers s INNER JOIN Locations l ON[/color]
> > l.zipcode[color=darkred]
> > > =
> > > > > > s.zip WHERE l.latitude <= 41.531365126399 AND l.latitude >=
> > > > > 41.241700873601
> > > > > > AND l.longitude >= -82.427239911609 AND l.longitude
> > > <= -82.041158088391
> > > > > > ORDER BY distance
> > > > > >
> > > > > >
> > > > > > This generates the following error:
> > > > > >
> > > > > > Supplied argument is not a valid MySQL result resource
> > > > > >
> > > > > >
> > > > > > I would love to find a solution for this, as it's holding up the
> > > > > completion
> > > > > > of the project.
> > > > > >
> > > > > > Thanks.
> > > > > >
> > > > >
> > > > > Xenophobe,
> > > > > Hmmmm....must get the upgrade to my finger driver....!
> > > > > We are missing a comma between " l.longitude" and "(69.09..."
> > > > >
> > > > > The corrected lines are:
> > > > >
> > > > > <snip>
> > > > > $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude, ";
> > > > > $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> > > > > <snip>
> > > > >
> > > > > Of course, I will claim I deliberately omitted the comma to test[/color][/color]
> your[color=green][color=darkred]
> > > SQL
> > > > > skills ...<grin>
> > > > > Cheers,
> > > > > Doug
> > > > >
> > > > > --
> > > > > Remove the blots from my address to reply
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

**Jochen Daum** · Jul 17 '05, 03:22 AM

Re: ORDER BY Zip Code Distance

Hi !

On Sat, 14 Feb 2004 06:09:13 GMT, "Xenophobe" <xenophobe@plan etx.com>
wrote:
[color=blue]
>Jochen,
>
>You're right, "huge" is relative.
>
>The zip code table consists of over 52K records. The company supplier lists
>it's joined with is under 3K.
>
>By "indexing", do you mean MySQL indexing? If so, I've didn't have much luck
>with that. It didn't seem to speed things up although it's quite possible
>the wrong columns were indexed or not enough space was allocated.
>
>Here's the schema for the two tables. Can you recommend which tables to
>index and what sizes to define?
>
>Any suggestions would be appreciated.[/color]

I suggest you implement the table schema I suggested in the other
thread, with no minute information and also put an index on
Suppliers.zipco de.

HTH, Jochen

[color=blue]
>
>Thanks!
>
>----------------------------------------
>
>TABLE: Locations
>
>LocationID
>ZipCode
>City
>State
>StatusCode
>AreaCode
>TimeZone
>Latitude
>Longitude
>
>TABLE: Suppliers
>
>SupplierID
>SupplierName 1
>SupplierName 2
>Address1
>Address2
>City
>State
>Zip
>Phone
>
>
>"Jochen Daum" <jochen.daum@ca ns.co.nz> wrote in message
>news:lbco20pjv n847b6153d9od19 vhf1j38ocq@4ax. com...[color=green]
>> Hi!
>>
>> On Fri, 13 Feb 2004 01:34:00 GMT, "Xenophobe" <xenophobe@plan etx.com>
>> wrote:
>>[color=darkred]
>> >Doug,
>> >
>> >This is just a quick follow-up. The zip sort by distance works great
>> >except... the performance is pokey for searches of 20+ miles. I could[/color][/color]
>simply[color=green][color=darkred]
>> >sort the results by company name (ditching the beautiful syntax you help[/color][/color]
>me[color=green][color=darkred]
>> >with!), but even this won't save enough cycles for broader searches.
>> >
>> >The options (in my view) are either break out the results in multiple[/color][/color]
>pages[color=green][color=darkred]
>> >or pre-calculate every possible lat and long and store them in the[/color][/color]
>database[color=green][color=darkred]
>> >(creating one huge table!) Can you think of anything that might help?[/color]
>>
>> Well, depends what you think is huge. It might still perform very well
>> with proper indexing. How many rows are we talking?
>>
>> Eg. half the world fits in around 4 GB of data for going down to
>> minutes, without even thinking about a good encoding for your
>> latitude/ longitude index. So this would mean around 4 disk accesses,
>> after that you read payload data.
>>
>> Of course I expect you are measuring only one country, so you'll have
>> much less data.
>>
>> HTH, Jochen
>> --
>> Jochen Daum - Cabletalk Group Ltd.
>> PHP DB Edit Toolkit -- PHP scripts for building
>> database editing interfaces.
>> http://sourceforge.net/projects/phpdbedittk/[/color]
>[/color]

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

**Jochen Daum** · Jul 17 '05, 03:22 AM

Re: ORDER BY Zip Code Distance

Hi Doug!
On Fri, 13 Feb 2004 04:33:56 GMT, "Doug Hutcheson"
<doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote:
[color=blue]
>"Jochen Daum" <jochen.daum@ca ns.co.nz> wrote in message
>news:erjo20pem svi0dkshvmav7l9 jgu1u6rqlh@4ax. com...[color=green]
>> On Fri, 13 Feb 2004 03:32:45 GMT, "Doug Hutcheson"
>> <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote:
>>[color=darkred]
>> >Jochen,
>> >That is interesting. Can you give more details? As I read the op, the
>> >problem was to find how many points from his database lay within x miles[/color][/color]
>of[color=green][color=darkred]
>> >given point y. I am not a GIS expert by any menas, but I can't see how to
>> >avoid doing the trig. each time? What am I missing? I love to learn new
>> >stuff like this.[/color]
>>
>> Made a calculation mistake there, but it still goes with less area and
>> accuracy:
>>
>> Store a table with fields
>>
>> srclong tinyint
>> srclongmin tinyint
>> srclat tinyint
>> srclatmin tinyint
>> distance float
>> trglong tinyint
>> trglongmin tinyint
>> trglat tinyint
>> trglatmin tinyint
>>
>> with a clustered index on the first 5 fields.
>>
>> Then save all possible combinations with their distance into the
>> database. A calculation example:
>>
>> All of US is less than 30 by 60 degrees latitude/longitude (sorry if I
>> mix it up). If we store 0 and 30 minutes positions only, thats
>>
>> ((30*2*60*2)^2) *20 bytes = 1036800000 bytes[/color][/color]

Had been thinking in the car about this and here are some
optimisations:

Firstly, if you have a database which supports clustered indexes, its
only 12 bytes per row, as you don't need to store the data again.

Also, you could limit the maximum distance stored for most
applications, eg. I don't wanna walk 34km to my next busstop. This
should maybe save another two bytes on the distance field.

A minimal save in the index pages could be gained, if you remove
distance from the index. With the current setup, there'll be 10 bytes
per row, thats around 800 records per page. That means all resultsets
will probably be in exactly one page (just cannot imagine to have 800
busstops at hand, which I would want to walk to, similar for any type
of shop), so you don't need any range scanning for the distance.

HTH, Jochen

[color=blue][color=green]
>>
>> Thats for 7200 locations. Good thing is, it performs always the same
>> for the same number of locations, no matter what actual value you
>> store for minutes. The other performance factor is only the number of
>> records returned, but you would want to limit that anyway for a web
>> page.
>>
>> HTH, Jochen
>>
>>[color=darkred]
>> >Cheers,
>> >Doug[/color]
>>
>> --
>> Jochen Daum - Cabletalk Group Ltd.
>> PHP DB Edit Toolkit -- PHP scripts for building
>> database editing interfaces.
>> http://sourceforge.net/projects/phpdbedittk/[/color]
>
>Jochen,
>That looks like a great idea.
>Thank you very much for the insight.
>Cheers,
>Doug[/color]

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

**Doug Hutcheson** · Jul 17 '05, 03:26 AM

Re: ORDER BY Zip Code Distance

"Jochen Daum" <jochen.daum@ca ns.co.nz> wrote in message
news:kejv20lg8b 84trr2nlt3b6mva g68n59o9b@4ax.c om...[color=blue]
> Hi Doug!
> On Fri, 13 Feb 2004 04:33:56 GMT, "Doug Hutcheson"
> <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote:
>[color=green]
> >"Jochen Daum" <jochen.daum@ca ns.co.nz> wrote in message
> >news:erjo20pem svi0dkshvmav7l9 jgu1u6rqlh@4ax. com...[color=darkred]
> >> On Fri, 13 Feb 2004 03:32:45 GMT, "Doug Hutcheson"
> >> <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote:
> >>
> >> >Jochen,
> >> >That is interesting. Can you give more details? As I read the op, the
> >> >problem was to find how many points from his database lay within x[/color][/color][/color]
miles[color=blue][color=green]
> >of[color=darkred]
> >> >given point y. I am not a GIS expert by any menas, but I can't see how[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> >> >avoid doing the trig. each time? What am I missing? I love to learn[/color][/color][/color]
new[color=blue][color=green][color=darkred]
> >> >stuff like this.
> >>
> >> Made a calculation mistake there, but it still goes with less area and
> >> accuracy:
> >>
> >> Store a table with fields
> >>
> >> srclong tinyint
> >> srclongmin tinyint
> >> srclat tinyint
> >> srclatmin tinyint
> >> distance float
> >> trglong tinyint
> >> trglongmin tinyint
> >> trglat tinyint
> >> trglatmin tinyint
> >>
> >> with a clustered index on the first 5 fields.
> >>
> >> Then save all possible combinations with their distance into the
> >> database. A calculation example:
> >>
> >> All of US is less than 30 by 60 degrees latitude/longitude (sorry if I
> >> mix it up). If we store 0 and 30 minutes positions only, thats
> >>
> >> ((30*2*60*2)^2) *20 bytes = 1036800000 bytes[/color][/color]
>
> Had been thinking in the car about this and here are some
> optimisations:
>
> Firstly, if you have a database which supports clustered indexes, its
> only 12 bytes per row, as you don't need to store the data again.
>
> Also, you could limit the maximum distance stored for most
> applications, eg. I don't wanna walk 34km to my next busstop. This
> should maybe save another two bytes on the distance field.
>
> A minimal save in the index pages could be gained, if you remove
> distance from the index. With the current setup, there'll be 10 bytes
> per row, thats around 800 records per page. That means all resultsets
> will probably be in exactly one page (just cannot imagine to have 800
> busstops at hand, which I would want to walk to, similar for any type
> of shop), so you don't need any range scanning for the distance.
>
> HTH, Jochen
>
>
>[color=green][color=darkred]
> >>
> >> Thats for 7200 locations. Good thing is, it performs always the same
> >> for the same number of locations, no matter what actual value you
> >> store for minutes. The other performance factor is only the number of
> >> records returned, but you would want to limit that anyway for a web
> >> page.
> >>
> >> HTH, Jochen
> >>
> >>
> >> >Cheers,
> >> >Doug
> >>
> >> --
> >> Jochen Daum - Cabletalk Group Ltd.
> >> PHP DB Edit Toolkit -- PHP scripts for building
> >> database editing interfaces.
> >> http://sourceforge.net/projects/phpdbedittk/[/color]
> >
> >Jochen,
> >That looks like a great idea.
> >Thank you very much for the insight.
> >Cheers,
> >Doug[/color]
>
> --
> Jochen Daum - Cabletalk Group Ltd.
> PHP DB Edit Toolkit -- PHP scripts for building
> database editing interfaces.
> http://sourceforge.net/projects/phpdbedittk/[/color]

Jochen,
Another one for my 'keepers' file...thanks again!
Cheers,
Doug

--
Remove the blots from my address to reply