DISTANCE and MGeoLocation

customaware

Now that we have the new MGeoLocation functionality, can anyone advise how we use two MGeoLocation BO Instances with the DISTANCE Function?

aware_support

The DISTANCE function requires addresses, which can either be specified explicitly or as as a string where an address is represented by latitude and longitude separated by comma. So you could use something like this:

DISTANCE (ThisMGeoLocation.Latitude + ',' + ThisMGeoLocation.Longitude, ThatMGeoLocation.Latitude + ',' + ThatMGeoLocation.Longitude)

customaware

That generates an error....

"Internal error. Status returned is NOT_FOUND"

customaware

My exact Action is...

FIND Entity WHERE DISTANCE(Entity.Location.Latitude+','+Entity.Location.Longitude,Visitor.VisitorLocation.Latitude+','+Visitor.VisitorLocation.Longitude)<50

BenHayat

I'll be needing this exact functionality very soon, as a consumer wants to know the distance from a vendor's shop. 🙂

Gabbitas

It'll be good to see if you manage to get this to work. I tried to do something almost identical but could never get it to work.In my experience I don't think it's possible to use the FIND action and the DISTANCE function together, it just seems to throw errors.

My original posts are here if they are of any use:

https://forum.awareim.com/d/7070/3&hilit=DISTANCE#p28961

Gabbitas

Hi Mark,

To expand a bit further on my above post: My understanding is the DISTANCE function uses a google API call and when AIM sends parameters to it the distance is returned. I think the reason you cant use the FIND and DISTANCE functions together is that as AIM searches through objects it would need to hit google with a DISTANCE request for each object it iterates over. I'm pretty sure you are restricted to just making a few requests per second with google and if you hit it with lots of requests very quickly it just returns an error. I could be wrong, that's just a guess. It would be good if I was wrong altogether and the FIND and DISTANCE functions could be used together.

You got me thinking about how I managed to accomplish what you are trying to do. I've taken a look back at it and I'll do my best to try and explain what I did to make this work.

In essence I wanted to find clients that were close to where a workforce member was when that workforce member was out on the road and logged in on his mobile device.

I have the following business objects set up:

Workforce (This is a system user)
Property (This contains information about the clients premises)

The Property BO contains attributes that store the street address (postal address and postcode). The Property BO also contains attributes named 'Lat' and 'Lng'. I purchased a plugin from support that performs a ReverseGeocode Lookup with google. Using the plugin I am able to populate the Property.Lat and Propery.Lng attributes with the logitude and latitude returned by google based on the postal address and postcode stored in the Property BO. This is done via a process when a record of the 'Property' type is created.

The Workforce BO contains an attribute that stores the users current position, as co-ordinates, that were obtained during login (I'm not using V7 yet so I only get to capture their position at login). These co-ordinates are then broken down further to separate the latitude part from the longitude part. The Workforce BO also contains 4 'search parameter' attributes: LastKnownLat_SearchUpper, LastKnownLat_SearchLower, LastKnownLng_SearchUpper, LastKnownLng_SearchLower. Each time the Workforce members current position changes the following rule is executed:

If Workforce.CurrentPosition WAS CHANGED Then 
Workforce.LastKnownLat_SearchLower=Workforce.LastKnownLatitude-0.006 
Workforce.LastKnownLat_SearchUpper=Workforce.LastKnownLatitude+0.006 
Workforce.LastKnownLong_SearchLower=Workforce.LastKnownLongitude-0.006 
Workforce.LastKnownLong_SearchUpper=Workforce.LastKnownLongitude+0.006

The above rule populates the search parameters by adding aproximatley 1 mile (or maybe its 1Km? I can't remember) that's the +0.006 and -0.006. This creates what I believe is called a bounding box. Effectively an area of the earths surface for which we can then use to search. A true bounding box is actually circular whereas I think my method creates a square. The maths is way beyond me to create an imaginary circle. I believe it is somehow possible to use some sort of function inside MySQL to create a bounding box but again this is way beyond me. Simple isn't beyond me which is why I went with this method - it's close enough for my needs.

I digress. Stay with me. If you've managed to understand what I'm rambling on about this far then you'll know that we now have the users current location and we also have an imaginary area of the earths surface so we can now run the following rule/process:

FIND Property WHERE Property.Lat BETWEEN LoggedInWorkforce.LastKnownLat_SearchLower AND LoggedInWorkforce.LastKnownLat_SearchUpper AND Property.Lng BETWEEN LoggedInWorkforce.LastKnownLong_SearchLower AND LoggedInWorkforce.LastKnownLong_SearchUpper

This returns all instances of property that are within about 1 mile (or maybe 1Km) of the users current position. Well kind of if you remember that we created a square instead of a circle!

Now although this seems to work great for me I'm sure that this isn't the greatest way of doing it. If anyone can come up with anything better I would love to try it.

Mark, I hope this might help your visitors find those entities.

Ben, with an extra couple of attributes in my BO's I can use the following process to show a workforce member how far they are away from a selected property:

LoggedInWorkforce.Distance=DISTANCE(ThisProperty.MapAddress,LoggedInWorkforce.LastKnownLatLong)*0.000621371192 
DISPLAY MESSAGE 'The distance to this property is '+LoggedInSystemUser.Distance+' miles from your last known location.<br><br>This calculation is based on where the crow flies and not by road or another route.'

BenHayat

Gabbitas wrote

Ben, with an extra couple of attributes in my BO's I can use the following process to show a workforce member how far they are away from a selected property:

LoggedInWorkforce.Distance=DISTANCE(ThisProperty.MapAddress,LoggedInWorkforce.LastKnownLatLong)*0.000621371192 
DISPLAY MESSAGE 'The distance to this property is '+LoggedInSystemUser.Distance+' miles from your last known location.<br><br>This calculation is based on where the crow flies and not by road or another route.'

Excellent, Thanks!

aware_support

Confirming: you cannot use DISTANCE inside FIND

customaware

Damn. :-/

Now that we have MGeoLocation BO type , we need MGeoDISTANCE function.
Takes two MGeoLocations as parameters.
Uses Haversine calc rather than google
Returns distance between in kilometres.

Here's the code Support.
Please add the function. The DISTANCE function as it stands has a narrow band of usability.

This script [in Javascript] calculates great-circle distances between the two points – that is, the shortest distance over the earth’s surface – using the ‘Haversine’ formula.

function getDistanceFromLatLonInKm(lat1,lon1,lat2,lon2) {
var R = 6371; // Radius of the earth in km
var dLat = deg2rad(lat2-lat1); // deg2rad below
var dLon = deg2rad(lon2-lon1);
var a =
Math.sin(dLat/2) * Math.sin(dLat/2) +
Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2)) *
Math.sin(dLon/2) * Math.sin(dLon/2)
;
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
var d = R * c; // Distance in km
return d;
}

customaware

Ok.... While we are waiting for Support to add the function described above....

I am happy to settle for and approximation... which is all I need really.

Each Degree of Latitude is about 111 km or 111,000 metres. Let's say we want to be within 100 metres.

FIND Entity WHERE (Entity.Location.Latitude-Visitor.VisitorLocation.Latitude)111000<100 AND (Entity.Location.Longitude-Visitor.VisitorLocation.Longitude)111000<100

Seems to work close enough for my needs and works with FIND.

I just hope the math is right. Eeeek! 😉

UnionSystems

Hi Mark,

I found an SQL query that supposedly is a bit more precise and I'm calling it as a stored procedure with a EXEC_SP

The store procedure (as entered via mysql command line) is

mysql> DROP PROCEDURE IF EXISTS Workplace_Nearest; 
mysql> DELIMITER //
mysql> CREATE PROCEDURE Workplace_Nearest(IN Target_Lat INT,IN Target_Lon INT, IN Max_Dist INT, IN Max_Result INT) 
    -> BEGIN 
          -> SELECT ID, Name, 3956 * 2 * ASIN(SQRT( POWER(SIN((Target_Lat - LocationLat)*pi()/180/2),2) +COS(Target_Lat*pi()/180 )*COS(Target_Lat*pi()/180) *POWER(SIN((Target_Lon-LocationLon)*pi()/180/2),2))) as Distance FROM SSTUWAUNIONAWARE_WORKPLACE WHERE Target_Lon between (Target_Lon-Max_Dist/cos(radians(Target_Lat))*69) and (Target_Lon+Max_Dist/cos(radians(Target_Lat))*69) and Target_Lat between (Target_Lat-(Max_Dist/69)) and (Target_Lat+(Max_Dist/69)) having Distance < Max_Dist ORDER BY Distance limit Max_Result; 
    -> END //
mysql> DELIMITER ;

The parameters for the stored procedure above are :

Target_Lat Latitude for the target
Target_Lon Latitude for the target
Max_Dist Maximum distance from the target
Max_Result Maximum results to return

The process rule calling this in AwareIM is

EXEC_SP 'Workplace_Nearest' WITH 'Target_Lat'=LoggedInRegularUser.ps_MGeoLocation.Latitude, 'Target_Lon'=LoggedInRegularUser.ps_MGeoLocation.Longitude,'Max_Dist'=100,'Max_Results'=10 RETURN Workplace

BLOMASKY

Since we are sharing SPs for Distance Calc, here is one that I am using in MSSQL

ALTER PROCEDURE [dbo].[NearbyCustomers]
@prospectID integer

declare @MyLoc GEOGRAPHY;
declare @lat float;
declare @long float;
declare @state char(2);
declare @city varchar(100)
declare @minLat float, @maxLat float;
declare @LastSaleCutOff dateTime = dateAdd(dd, -273, getDate())

select @MyLoc = geoLocation, @lat = lat, @long = long,
@city = city, @state = state
from Prospects where prospectID = @ProspectID

set @minLat = @lat - 1;
set @maxlat = @lat + 1

select top 50 ID, BASVERSION, BASTIMESTAMP, name, add1, city, state, phone, dateOfLastSale,
@MyLoc.STDistance(geoLocation) / 1609 as miles
from Customers where state = @State
and DateOfLastSale > @LastSaleCutOff
and lat between @minLat and @maxLat
and (@MyLoc.STDistance(geoLocation) > 0 or @city = city)
order by @MyLoc.STDistance(geoLocation)

rbross

Bruce, I have a stored procedure that works if I only pass 1 parameter save the LIRU ID.

in the sp I added the "Is Null" syntax to manage blank search fields but I still get the error.
I get this strange error, see image.

The stored procedure looks like this:

Alter procedure dbo.sproc_FindCustomerMembers
@LIRUID int
, @CustomerNo int
, @CustomerName varchar(60)
, @MemberDisplayName varchar(60)
, @PrimaryPhone varchar(20)
, @CellPhone varchar(20)
, @OtherPhone varchar(20)

As
BEGIN
Set NoCount On ;
--data clean up delete LIRU previous records selected
Delete From dbo.TMP_CUSTOMERMEMBER Where LIRUID = @LIRUID
--get Max record from Temp_customerMember
Declare @MaxID int = (select IsNull(Max(ID),0) From dbo.TMP_CUSTOMERMEMBER)

--Insert result set into Tmp_CustomerMember
Insert INTO dbo.TMP_CUSTOMERMEMBER
  ( ID 
  , BASVERSION
  , BASTIMESTAMP
  , CellPhone
  , CustomerID
  , CustomerMemberID
  , LIRUID
  , MemberDisplayName
  , SeqNo
  , Type
  , PrimaryPhone
  , CustomerNo
  , Status
  , CustomerName)

select @MaxID+Row_Number() over(Order By c.CustomerName, cm.MemberDisplayName) as ID 
  , 1 as BASVERSION
  , GetDate() as BASTIMESTAMP
  , cm.CellPhone
  , c.ID as CustomerID
  , cm.ID as CustomerMemberID 
  , @LIRUID as LIRUID
  , cm.MemberDisplayName
  , 1 as SeqNo
  , c.CustomerType 
  , c.PrimaryPhone
  , c.CustomerNo
  , c.Status
  , c.CustomerName

from Customer c 
join CustomerMember cm on c.ID = cm.ob_Customer_RID
where c.CustomerName like '%'+ Is Null(@CustomerName, c.CustomerName) +'%' 
and cm.MemberDisplayName like '%'+ Is Null(@MemberDisplayName, cm.MemberDisplayName) +'%' 
and c.PrimaryPhone like '%'+ Is Null(@PrimaryPhone, c.PrimaryPhone) +'%'
and cm.CellPhone like '%'+ Is Null(@CellPhone, cm.CellPhone) +'%' 
and cm.OtherPhone like '%'+Is Null(@OtherPhone, cm.otherPhone) +'%'
order by c.CustomerName, cm.SeqNo

END
--END Of Stored Procedure

Jaymer

From my experience, MSSQL will get an error in the stored procedure, and then aware will display it in a box like this.
But, you can’t be 100% sure the error is actually coming from MSSQL.

To make sure of that, run the stored procedure directly from SMSS.
Along with your parameters, should get the error at the SQL level.
Then you can debug it there.

rbross

I did, it runs fine under ssms.
It's like it doesn't like one of the parameters?