Oracle spatial search in the distance

Question

I have the following table:

ID(int),City(char),latitude(float),longitude(float). 

Now, based on longitude of longevity (for example: 44.8) and latitude (for example: 46.3), I want to look for all nearby cities within 100 miles / km.

I found some examples, but I don’t know how to adapt them to my case.

 select * from GEO.Cities a where SDO_WITHIN_DISTANCE([I don`t know], MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(44.8,46.3, NULL) ,NULL, NULL), 'distance = 1000') = 'TRUE'; 

Any help would be appreciated.

PS: If possible, have a distance and be sorted

PPS: I want to do it this way due to performance issues, I did it this way http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL , but it takes too long. ..

Answer 1

You have a nice link for finding mySQL distances.

Forget about Oracle Spatial. Too much code, too much complexity, adding value is not enough.

Here is a query that will do the trick. It uses charter miles. EDIT . This fixes the error mentioned by mdarwin at the cost of checking for separation if you try to use it for a location at the north or south pole.

  SELECT id, city, LATITUDE, LONGITUDE, distance FROM ( SELECT id, city, LATITUDE, LONGITUDE, (3959 * ACOS(COS(RADIANS(LATITUDE)) * COS(RADIANS(mylat)) * COS(RADIANS(LONGITUDE) - RADIANS(mylng)) + SIN(RADIANS(LATITUDE)) * SIN(RADIANS(mylat)) )) AS distance, b.mydst FROM Cities JOIN ( SELECT :LAT AS mylat, :LONG AS mylng, :RADIUS_LIMIT AS mydst FROM DUAL )b ON (1 = 1) WHERE LATITUDE >= mylat -(mydst/69) AND LATITUDE <= mylat +(mydst/69) AND LONGITUDE >= mylng -(mydst/(69 * COS(RADIANS(mylat)))) AND LONGITUDE <= mylng +(mydst/(69 * COS(RADIANS(mylat)))) )a WHERE distance <= mydst ORDER BY distance 

If you work in kilometers, change mydst / 69 to mydst / 111.045 and change 3959 to 6371.4. (1/69 converts miles to degrees, 3959 - the radius of the planet).

Now you are likely to be tempted to use this large query as a "magic black box." Do not do this! It is not very difficult to understand, and if you understand this, you can do a better job. This is what happens.

This section is the foundation of what makes the request fast. He is looking for a table of cities for nearby cities to the point you specify.

  WHERE LATITUDE >= mylat -(mydst/69) AND LATITUDE <= mylat +(mydst/69) AND LONGITUDE >= mylng -(mydst/(69 * COS(RADIANS(mylat)))) AND LONGITUDE <= mylng +(mydst/(69 * COS(RADIANS(mylat)))) 

For it to work, you definitely need an index in the LATITUDE column. The index of your LONGITUDE column will also help a bit. It performs an approximate search, looking for lines that are inside a quasi-rectangular patch on the surface of the earth near your point. He chooses too many cities, but not too many.

This section allows you to exclude additional cities from your result set:

  WHERE distance <= mydst 

This item is a haversine formula that calculates the distance between big cities and the point.

  (3959 * ACOS(COS(RADIANS(LATITUDE)) * COS(RADIANS(mylat)) * COS(RADIANS(LONGITUDE) - RADIANS(mylng)) + SIN(RADIANS(LATITUDE)) * SIN(RADIANS(mylat)) 

In this section, you can enter your point and your radius limiter only once as related variables in your query. This is useful because various formulas use these variables several times.

  SELECT :LAT AS mylat, :LONG AS mylng, :RADIUS_LIMIT AS mydst FROM DUAL 

The rest of the query simply arranges things, so you select and order by distance.

Here is a more complete explanation: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

Answer 2

If you decide to create your own formula, I think this function can be very useful for oracle users and can be slightly modified for other databases. This is a flat earthen formula that is much cheaper computationally more expensive than the more accurate haversin formula.

 CREATE OR REPLACE Function CIC3.F_FLATEARTHRAD ( latoriginrad IN number, longoriginrad IN number, latdestrad IN number, longdestrad IN number) RETURN number IS a number; b number; c number; u number; v number; HalfPi number:=1.5707963; R number:=3956; BEGIN if latoriginrad is null or latdestrad is null or longdestrad is null or longoriginrad is null then return null; end if; a := HalfPi - latoriginrad; b := HalfPi - latdestrad; u := a * a + b * b; v := - 2 * a * b * cos(longdestrad - longoriginrad); c := sqrt(abs(u + v)); return R * c; END; 

Then your request will be

 select * from GEO.Cities a where F_FLATEARTHRAD(44.8*0.0174,46.3*0.0174, latitude_radians,longitude_radians)<1000 

The factor 0.0174 is necessary because the formula uses radians not degrees. Therefore, you need to either store radians (possibly using a trigger). Or you will need to change the formula to take degrees. For query purposes, you can query thousands of records, and even one additional multiplication can affect the response time. In our case, some queries compare the distances between two tables of 4k records on one and 200k, so we have about billions of function calls.

The following is the equivalent of a haverin for people who do not need to worry about time.

 CREATE OR REPLACE Function CIC3.F_HAVERSINE ( latorigin IN number, longorigin IN number, latdest IN number, longdest IN number) RETURN number IS v_longoriginrad number; v_latoriginrad number; v_longdestrad number; v_latdestrad number; v_difflat number; v_difflong number; a number; c number; d number; z number; x number; e number; f number; g number; h number; i number; j number; k number; l number; m number; n number; o number; p number; q number; y number; BEGIN z := .017453293; x := 3956; y := 57.295780; v_longoriginrad:=longorigin*z; v_latoriginrad:=latorigin*z; v_longdestrad:=longdest*z; v_latdestrad:=latdest*z; v_difflong:=v_longdestrad-v_longoriginrad; v_difflat:=v_latdestrad-v_latoriginrad; j:=(v_difflat/2); k:=sin(j); l:=power(k,2); m:=cos(v_latoriginrad); n:=cos(v_latdestrad); o:=v_difflong/2; p:=sin(o); q:=power(p,2); a:=l+m*n*q; c := 2 * asin(sqrt(a)); d := x * c; return d; END; 

Answer 3

If you really want to use SDO_WITHIN_DISTANCE , you need to create a column of type SDO_GEOMETRY in the Cities table, fill in the spatial index metadata and create the spatial index:

• SDO_GEOMETRY :

   CREATE TABLE MYTABLE( ..., GEOLOC MDSYS.SDO_GEOMETRY, ... ); 

• Spatial Index Metadata:

   INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('MYTABLE' /*your table name*/, 'GEOLOC', /*your spatial column name*/ SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, 1), SDO_DIM_ELEMENT('Y', -90, 90, 1)), 8307); 

• Create a spatial index:

   CREATE INDEX MY_SPATIAL_IDX ON MYTABLE (GEOLOC) tablespace SomeTablespace; -- optional 

• Now replace GEOLOC where you said [I don't know].

That should answer your question. Others gave you a hint that using Oracle space for such a simple task comes from being redundant. In this case, I tend to agree, because you can make a simple box in the WHERE clause to cut cities not in a rectangular box with the center of your starting point and the size of your search distance; however, sometimes you need R-tree index intelligence. In any case, their solutions have two main problems:

a. They use the Great Circle method to calculate the distance between points. This is too rough, you need to use an ellipsoidal approach to get more accurate results. Googling gives an answer right away, like this .

b. If you program the ellipsoid distance algorithm in PL / SQL, you will find it too slow. The solution is to move this logic to Java or C ++ and make it callable from Oracle (there is a standard way to do this).

Answer 4

A few years after the accepted answer, you can add some improvements to the query: The Oracle database in version 11.1 added the calc_distance function ( http://psoug.org/reference/functions.html ), useful for accurate distance calculation.
About offers, in order to make a request faster, it uses a conversion constant from distance to radians, which changes with latitude ( http://www.longitudestore.com/how-big-is-one-gps-degree.html ) and adds an error that increases with search radius. Here, my changes, which use the average value of the radius of the earth, in my tests, seem to be more accurate for searches for a large radius, in latitudes in Europe:

 SELECT id, city, LATITUDE, LONGITUDE, distance FROM ( SELECT id, city, LATITUDE, LONGITUDE, calc_distance(LATITUDE, LONGITUDE, mylat, mylng) AS distance, b.mydst FROM Cities JOIN ( SELECT :LAT AS mylat, :LONG AS mylng, :RADIUS_LIMIT AS mydst, 3.1415926 AS pi, -- or use pi() function if available 6371.4 earthradius FROM DUAL )b ON (1 = 1) WHERE LATITUDE >= mylat - ((mydst / earthradius) * (180 / pi)) AND LATITUDE <= mylat + ((mydst / earthradius) * (180 / pi)) AND LONGITUDE >= mylng - ((mydst / earthradius) * (180 / pi) / cos(mylat * pi/180)) AND LONGITUDE <= mylng + ((mydst / earthradius) * (180 / pi) / cos(mylat * pi/180)) )a WHERE distance <= mydst ORDER BY distance