Has anyone worked on location based shipment search?

kabaliganesan · #1 (**permalink**) July 2nd, 2009, 12:24

Hi, we are working on a scenario to list all shipments originating within a preset distance radius from a location. i.e., All shipments origination 50 miles from location A has to be listed.

Has anyone worked on this scenario?

Srivathsana · #2 (**permalink**) July 3rd, 2009, 05:37

Hi,

If your Location A is fixed,you can make use of an shipment agent to populate a refnum in all the qualifying shipments and make use of that refnum while searching for these shipments.There is a seeded shipment agent action "EVALUATE PROXIMITY TO LOCATION".
I have not personally evaluated this option so far though.

rj5316 · #3 (**permalink**) July 3rd, 2009, 12:33

not found on the buy shipments > actions ... where have you seen this ...

rgds--

sujatha_g · #4 (**permalink**) July 3rd, 2009, 12:58

The action gets listed when event (agent event) is not given.

rj5316 · #5 (**permalink**) July 3rd, 2009, 16:06

not clear what you are conveying here .....can you explain the navigation to this .......

rgds--

Srivathsana · #6 (**permalink**) July 4th, 2009, 14:20

Hi rj,

This can be seen as part of the seeded actions present with buy shipment agent.

LourensGlog · #7 (**permalink**) July 14th, 2009, 10:48

Assuming you have the long/lats for all your locations in OTM, and you don't mind if the 50 mi is a crow flight distance, then you can take the mathematical route using the Spherical Law of Cosines: d = acos(sin(lat1).sin(lat2)+cos(lat1).cos(lat2).cos(l ong2−long1)).R.
It may be difficult though to convert this formula to sql within the 801 characters limit OTM allows for Saved Queries, but it's worth a try.

LourensGlog · #8 (**permalink**) July 14th, 2009, 15:14

I took a shot and found out it wasn't that bad after all:

Create a Shipment Saved Query and put this in Find All:

select s.shipment_gid from shipment s, location l where s.source_location_gid=l.location_gid and acos(sin(#LAT#/57.29578)*sin(l.lat/57.29578)+cos(#LAT#/57.29578)*cos(l.lat/57.29578)*cos((l.lon-#LON#)/57.29578))*3962<50

Notes:
- Just replace #LAT# (twice!) and #LON# with the lat/long of your location A.
- The 50 at the end represents the maximum distance (crow flight) - change this if needed.
- The 3962 at the end is the radius of the earth in miles
- The 57.29578 stands for 180/pi and is used to convert degrees to radians. A more accurate statement would have been 180/acos(-1), but if I used this I got 'out of range' errors.