Re: Tuning SQL

I haven't performed your calculation to check the solutions are comparable, but given two co-ordinates
(x1,y1) and (x2,y2)
using pythagoras the distance between them is SQRT( POWER(x2-x1,2) + POWER(y2-y1,2) ) = RADIUS

for best possible performance, assuming your max radius is 20, avoid the time consuming SQRT by testing

( POWER(x2-x1,2) + POWER(y2-y1,2)) < 400.

If this solution is unsuitable you could try defining your own approximating functions for sin, cos etc, but using less precision in the infinite series or you could define a simple sin / cos etc table which you use as a lookup table instead of calling oracle math functions.

Hope this helps

Barry Jones
Logica UK Ltd

Andrew <andy_at_webworks.ca> wrote in article <3549CD74.C886842A_at_webworks.ca>...
> Hi Folks.
>
> I am currently doing a cross referencing bit in oracle where I
> have a postal code and obtaining the latitude and longitude from out
> Postal Code database of 750000 records. Then I am cross referencing a
> set of criteria with a list of people of 32000 records. Now if I don't
> bother with a radius calculation to narrow down the search (which
> incidenty turns up about 10000 matches) the query comes back in about 7
> seconds. However, if I include the radius calculation (ie. return the
> people that fall in a 20 km radius).. the query takes 90 seconds... This
>
> is too long. Oracle is running on a fairly strong Solaris box (166 Mhz
> 128 MB Ram) barely tasked at all
>
> I am wondering if anyone has some advise about performing the
> distance calculation within PL/SQL and its obvious degradation in
> performance.
>
> My block looks something like:
>
> CURSOR ranks_list (c_lat,c_long)
> (c_provinceid personinfo.provinceid%TYPE) IS
> SELECT personinfo.id, firstname, lastname, cityname,
> provinces.abbrev, latitude, longitude,
> FROM postalcodes, personinfo, provinces
> WHERE provinces.id = provinceid
> AND personinfo.provinceid = c_provinceid
> AND personinfo.postalcode = postalcodes.postalcode
> AND getDistance(latitude,longitude,c_lat, c_long) <=
> radius;
>
>
> Where getDistance takes in each of the lat and long for all of the
> matches and elimiates ones that are not within the radius. Now if I
> remove the [AND getDistance(latitude,longitude,c_lat, c_long) <=
> radius], this thing returns the list in 7 seconds... with it left in, it
>
> takes 90 seconds...
>
> The function is as follows: ( we already downgraded the calcs from the
> curve distance calcs to this one which is the linear calc)
>
> FUNCTION getDistance2 (
> p_latitude1 PostalCodes.Latitude%TYPE,
> p_longitude1 PostalCodes.Longitude%TYPE,
> p_latitude2 PostalCodes.Latitude%TYPE,
> p_longitude2 PostalCodes.Longitude%TYPE)
> RETURN NUMBER
> IS
> v_distance NUMBER(20,2);
> v_radA1 NUMBER(20,6);
> v_radA2 NUMBER(20,6);
> BEGIN
> v_radA1 := p_latitude1/180*3.14285;
> v_radA2 := p_latitude2/180*3.14285;
> v_distance := 5499.0*acos(sin(v_radA1)*sin(v_radA2) +
> cos(v_radA1)*cos(v_radA2)*cos((p_longitude1/180*3.14285) -
> (p_longitude2/180
> *3.1428)));
> RETURN v_distance;
> END getDistance2;
>
>
>
> Any insight would be appreciated.
>
> Thanks
> Andrew
>
>
Received on Fri May 01 1998 - 11:41:05 CDT