Creatio maintains geocodes for address locations in the AccountAddress object. This geocoding happens automatically each time a user opens an address and updates any one of the address fields. Creatio uses open street maps to query each address and then saves the latitude and longitude of the address to the Account address record.

Instead of building a lot of UI logic to get a list of accounts near a specific location, it’s easier to build a Creatio Web service that does the work of finding those locations and displaying them on the UI page. For the purposes of this article, we’re going to build a Creatio Web service to display a vendor's locations close to a given address within a specified radius.

Step 1: Set up the C# Code

The first step to build this Creatio Web Service is to set up a C# code module by hitting the Add button and choosing the “Source code” module option. This will allow you to write a server-side C# snippet that performs back end system operations.

Give it an appropriate name and then proceed to adding some code to the module.

Start with defining some data contract classes. In the code below the AccountAddressLocation class keeps track of each location returned from the database. This data is then included in the NearByLocationResult class with a “success” flag indicating that the data was successfully processed.

Step 2: Set up the Logic to Query the Locations

Next, we need to set up the logic that will query for vendor locations close to a given location based on a specified radius. For this to work correctly, we must be able to query based on geocodes to only return accounts within a specified distance. The geocodes are stored in Account address table and are named as follows:

“GPSN” represents Latitude
“GPSE” represents Longitude

Step 3: Use a Query to Retrieve the Data

Next, perform a standard entity schema type query to retrieve the data based on the distance. Here is an example of how the code could look:

Notice that the radius could ideally be system setting so it can be changed as needed by the administrator. In the code above, the nradius variable is assigned the value of the system setting “SearchRadiusForMap”. The system setting itself can be easily configured in the “Settings” area of Creatio, and once in place will drive the distance value that is used in the distance calculation when retrieving account locations.

Note: Using Custom SQL Procedures

It’s important to note that the query in the code above used a couple of custom SQL procedures to find the distance based on GPS coordinates. There are various solutions already available, and you can use whatever feels like the best fit to you. In this example, we used the SQL solutions mentioned in this link and this link.

Step 3: Putting it All Together with the Creatio ServiceHelper Module

Assuming everything has been implemented correctly, we can use the Creatio ServiceHelper client-side module to get information from this service back to the UI page so it can either be displayed as a list or shown on a map. Here is how the client-side code could look:

Based on the debug comments above, you will see that the service has been called and 26 address locations have been retrieved.