Re: Querying distances between two coordinates

From: Jeremy <jeremy0505_at_gmail.com>
Date: Tue, 14 Jun 2011 10:53:58 +0100
Message-ID: <MPG.2861400ffc4aa8f3989912_at_News.Individual.NET>

In article <aff6098e-78f9-45e7-88a1-
70fafa76aa1e_at_b1g2000yql.googlegroups.com>, hooperc2001_at_gmail.com says...

> Have a look at the following article:
> http://hoopercharles.wordpress.com/2011/06/13/calculate-the-distance-between-two-latitudelongitude-points-using-plain-sql/
>
> An up-front warning - the method is CPU intensive if there are many
> rows in the database table.
>

Blimey, you really went to town on this one!!

With regard to the "real question" I was trying to answer (radiussearch)  one of my team came up with a solution storing the lat/long in a table column of type "mdsys.sdo_geometry" - I believe this does not require any Oracle options - available in SE.

Then insert data into the column using a call such as:

mdsys.sdo_geometry

(2001,               -- This is the SDO_GTYPE attribute and it is set to 
                     -- 2001 when storing a two-dimensional single point 
                     -- such as a customer's location.
 8307,               -- This is the spatial reference system ID 
                     -- (SRID), 8307 corresponds to "Longitude / 
                     --Latitude (WGS 84)

 mdsys.sdo_point_type (-0.771618, 51.579601, null), null,
null)

Then query using the function

"sdo_within_distance"

I hope this information is useful to others; if a fuller example is required we can provide.

Further comments / advice gratefully received.

-- 
jeremy

Received on Tue Jun 14 2011 - 04:53:58 CDT