An easier way would be to use the spatial option. This allows you to do =
distance queries, all locations within a specific area and many many =
other location type queries.
>>> "David Shockey" <davidshockey_at_zdnetonebox.com> 10/11/00 03:55PM >>>
I don't know a lot about lat/long either but I do know that if your points
are great distances apart then you have to use more than simple trigonometr=
y.
(Hint: the world is not flat.) :)
- jfedock_at_ixl.com wrote:
> thanks for the advice. I'll have to check out and see if function
> based
> indexes can help.
>=20
> John
>=20
> -----Original Message-----
> Sent: Friday, October 06, 2000 1:35 PM
> To: Multiple recipients of list ORACLE-L
>=20
>=20
> John,
>=20
> Since those strange numbers look static - why not create a function
> to
> return that value, and create a function based index on the function?
> If
> you're not in 8i, you could create additional columns (say, for the
> value
> and it's sqrt), populated and maintained by triggers, and index them.
> Sounds
> like a good idea to me.
>=20
> hth,
>=20
> Yosi
>=20
> > -----Original Message-----
> > From: jfedock_at_ixl.com [mailto:jfedock_at_ixl.com]=20
> > Sent: Friday, October 06, 2000 12:41 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: longitude, latitude
> >=20
> >=20
> > Hello all,
> >=20
> > Looking for some advice on the best way to use longitude and latitude.
> > These values are stored in a table, along with other info.
> >=20
> > In order to get within a certain distance, our developers are=20
> > using the
> > following query. It actually runs well, with a few hints. I=20
> > feel that we
> > need to narrow down the long/lat in the where clause by a=20
> > certain distance.
> >=20
> > I'll be the first to admit I don't know much about long/lat.=20
> > I'm just trying
> > to tune this query a bit.
> >=20
> > Anybody else out there deal with this before?
> >=20
> >=20
> > SELECT DISTINCT service_provider_id,=20
> > SQRT(((37.6715 - LATITUDE)*(37.6715 - LATITUDE) + (-77.5465 -
> > LONGITUDE)*(-77.5465 - LONGITUDE)) * 5329) DIST
> > FROM provider_search=20
> > WHERE SPECIALTY_CATEGORY_ID =3D 'PH'
> > AND ((37.6715 - LATITUDE)*(37.6715 - LATITUDE) + (-77.5465 -
> > LONGITUDE)*(-77.5465 -LONGITUDE)) * 5329 < 100*100
> > ORDER BY DIST
> >=20
> >=20
> > Thanks.
> >=20
> > John=20
> > jfedock_at_ixl.com=20
> > --=20
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
> > --=20
> > Author:=20
> > INET: jfedock_at_ixl.com=20
> >=20
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing
> Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
> in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >=20
> --=20
> Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
> --=20
> Author:=20
> INET: Yosi_at_comhill.com=20
>=20
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --=20
> Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
> --=20
> Author:=20
> INET: jfedock_at_ixl.com=20
>=20
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>=20
To get your own FREE ZDNet Onebox - FREE voicemail, email, and fax,
all in one place - sign up today at
http://www.zdnetonebox.com=20
--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
--=20
Author: David Shockey
INET: davidshockey_at_zdnetonebox.com=20
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
Received on Wed Oct 11 2000 - 14:55:20 CDT