Is there a way to query the list of near by accounts based on geolocation?

Hello Ganesh, 

Unfortunately, there is no basic option to display the list of accounts that are located near to the selected company based on its location.

I will register the idea for our R&D team to check the possibility of implementing such kind of filtration in further releases. 

Thank you for your idea!

Best regards, 

Olga. 

Thanks Olga

Hello Ganesh,

For my part, I created a process which launches a C# function. in this function a SQL procedure which find neareast accounts.

 
	using System.Collections.Generic;
	using System.Globalization;
	using System.IO;
	using System.Linq;
	using System.Net.Sockets;
	using System.Net;
	using System.Security.Cryptography;
	using System.ServiceModel.Activation;
	using System.ServiceModel.Web;
	using System.ServiceModel;
	using System.Text;
	using System.Threading.Tasks;
	using System.Threading;
	using System.Web;
	using System.Collections;
	using System;
	using Terrasoft.Common;
	using Terrasoft.Configuration.RightsService;
	using Terrasoft.Configuration;
	using Terrasoft.Core.DB;
	using Terrasoft.Core.Entities;
	using Terrasoft.Core.Factories;
	using Terrasoft.Core.Scheduler;
	using Terrasoft.Core.Store;
	using Terrasoft.Core;
 
	public class NearClientsGPS
	{
//GetAndSetNearestClient function, fills the UsrNearestAccounts object with the IDs of nearby accounts 
		//accountId: Id of the account whose nearby customers we want to find
		//addressId: Id of the main address of the account whose GPS coordinates are GPSE and GPSN
		//GPSN: Latitude of the account address whose customers we want to find nearby
		//GPSE: Longitude of the address of the account whose customers we want to find nearby
		//userId: Contact ID of the user who launches the procedure
		//Distance: search radius in kilometer(s)
		public static void GetAndSetNearestClient(string accountId, string addressId, string GPSN, string GPSE, Guid userId, string Distance, UserConnection userConnection)
		{
			//On vide la table des clients à proximité pour le compte
			var DeleteUserData = string.Format(@"
			DELETE 
			FROM UsrNearestAccounts 
			WHERE accountId ='{0}'", accountId);
			var resultDelete = new CustomQuery(userConnection, DeleteUserData);
			resultDelete.Execute();
 
			var SQLLocation = string.Format(@"
				DECLARE
					@GEO1 GEOGRAPHY,
					@LAT VARCHAR(10),
					@LONG VARCHAR(10)
 
					SET @LAT='{0}'
					SET @LONG='{1}'
					SET @geo1= geography::Point(@LAT, @LONG, 4326)
 
					SELECT top(50) '{3}' as accountId, a.Id as nearAccountId, ad.Id as nearAddressId, '{2}' as userId, (@geo1.STDistance(geography::Point(ISNULL(ad.GPSN,0), ISNULL(ad.GPSE,0), 4326)))/1000 as Distance
					FROM accountAddress ad, account a
					WHERE ad.accountId = a.Id
					AND a.Id != '{3}'
					AND (@geo1.STDistance(geography::Point(ISNULL(ad.GPSN,0), ISNULL(ad.GPSE,0), 4326)))/1000 < '{4}'
					ORDER BY (@geo1.STDistance(geography::Point(ISNULL(ad.GPSN,0), ISNULL(ad.GPSE,0), 4326)))/1000 asc
				",  GPSN, GPSE, userId, accountId, Distance);
			var query = new CustomQuery(userConnection, SQLLocation);
			using (var dbExecutor = userConnection.EnsureDBConnection())
			{
			    using (var reader = query.ExecuteReader(dbExecutor))
			    {
					var ListAccounts = new List<string>();
 
			        while (reader.Read())
			        {
						if (!ListAccounts.Contains((string)reader["nearAccountId"].ToString()))
						{
							ListAccounts.Add((string)reader["nearAccountId"].ToString());
 
							var schema = userConnection.EntitySchemaManager.GetInstanceByName("UsrNearestAccounts");
							var entity = schema.CreateEntity(userConnection);
 
							entity.SetColumnValue("AccountId", 	   reader["accountId"]);
							entity.SetColumnValue("NearAccountId", reader["nearAccountId"]);
							entity.SetColumnValue("NearAddressId", reader["nearAddressId"]);
							entity.SetColumnValue("UserId", 	   reader["userId"]);
							entity.SetColumnValue("Distance", 	   reader["Distance"]);
							entity.SetDefColumnValues();
							entity.Save(false);
						}
					}
				}
			}
			return;
		}	
	}