Creating a Geofence API Using the Haversine Formula, PHP, and DreamFactory’s Scripted API Services

As a followup to the recently published post, “Creating a Geocoder Service Using Dreamfactory and the Google Maps Geocoding API”, I thought it would be fun to continue expanding upon this theme and demonstrate how to create a geofence API using the Haversine equation and a DreamFactory PHP Scripted Service API. A geofence is a virtual perimeter surrounding a location defined by a specific pair of latitudinal and longitudinal coordinates. For example, the map overlay found in the following screenshot simulates what a 500′ radius geofence placed around the iconic Empire State Building would look like:

Displaying a geofence

These days, geofences are commonly used for a wide variety of applications, including providing customers with localized coupons once they enter a store, wildlife management, and security. In this article you’ll learn how to insert the Haversine Equation into a scripted DreamFactory service. Once done you’ll be able to issue a simple HTTP call to your DreamFactory instance, passing along two sets of coordinates in order to determine how far away a subject is from the target destination. Before doing so though, it’s worth talking about the mathematical formula that makes this all possible, known as the Haversine formula.

Introducing the Haversine Formula

Wikipedia defines defines the Haversine formula as being used to “determine the great-circle distance between two points on a sphere given their longitudes and latitudes”1. The “great-circle distance” is the shortest distance between two points as measured on the surface of a sphere, which makes it a relatively accurate solution for measuring distance between two points on Earth. This formula involves a fairly heavy dose of geometry, and so I’ll leave it to the aforementioned Wikipedia page to walk you through the mathematical details. We’ll instead focus on converting this formula into its’ PHP equivalent, and making this functionality available via a secure DreamFactory service.

Creating the Geofence API

To create the Geofence API in DreamFactory, we’ll begin by creating a new PHP Scripted Service. This is accomplished by logging into your DreamFactory instance, clicking on Services, clicking Create, and selecting Script and finally PHP (see below screenshot).

Creating a PHP Scripted Service

Assign a name, label, and description to the new service. I selected geofence, Geofence API, and Geofence API, respectively. Next, click on the Config tab, and in the text area add the following PHP code:
if ($event['request']['method'] == "GET") {

    $lat1 = (float) $event['request']['parameters']['lat1'];
    $lng1 = (float) $event['request']['parameters']['lng1'];

    $lat2 = (float) $event['request']['parameters']['lat2'];
    $lng2 = (float) $event['request']['parameters']['lng2'];

    $radius = 3959;

    $dLat = deg2rad($lat2 - $lat1);
    $dLon = deg2rad($lng2 - $lng1);

    $a = sin($dLat/2) * sin($dLat/2) + cos(deg2rad($lat1)) 
             * cos(deg2rad($lat2)) * sin($dLon/2) * sin($dLon/2);
    $c = 2 * asin(sqrt($a));
    $d = $radius * $c;

    return $d;

}
This is the Haversine formula implemented in PHP and wrapped around a GET HTTP call listener. If you want to return the distance between points in kilometers instead of miles, replace 3959 with 6371. Once saved, and after configuring an associated DreamFactory role and API key, you’ll be able to call your API like so:
/api/v2/geofence?lat1=40.748667&lng1=-73.985667&lat2=40.753706&lng2=-73.982341
The 40.748667,-73.985667 coordinates identify the location of the Empire State Building, and the -73.985667,40.753706 coordinates identify the location of the New York Public Library (5th avenue branch). The service returns a distance of just 0.3892 miles; a short walk between these two famous landmarks!

Where to From Here?

Managing these sorts of API-based services inside DreamFactory is great for many reasons. Firstly, DreamFactory-managed services are reusable, meaning you won’t have to bother with repeatedly coding the Haversine formula every time the need arises to insert this sort of functionality into an application. You can instead just quickly connect to the existing service! Second, you can optionally incorporate various advanced DreamFactory features to suit application-specific needs:
  • Use DreamFactory’s limiting feature to restrict the number of hourly/daily/monthly calls a particular user can make, opening up the opportunity to monetize your data.
  • Integrate the geofence API into another API’s workflow, allowing you to return the distance between two points alongside other response data.

Conclusion

Would you like to see this example demonstrated in person? Contact our team to arrange a demo. Or download the OSS version of DreamFactory and experiment with this feature for yourself!

  1. https://en.wikipedia.org/wiki/Haversine_formula 

Merging Multiple Database API Calls with DreamFactory Data Mesh

We’ve recently launched a new site for beginner-level videos about the DreamFactory platform, and yesterday published our second video in the series. It’s titled “Merging Multiple Database API Calls with DreamFactory Data Mesh”. In this video you’ll learn how to use data mesh to merge data residing in IBM DB2 and MySQL databases and present the data within a single API response.

Head on over to DreamFactory Academy to watch the video now!

https://academy.dreamfactory.com/

Easier Data Marts with DreamFactory Data Mesh

Today’s IT teams are struggling to make sense of organizational data that has been compiled piecemeal and often stored within disparate storage solutions. Often this information needs to be aggregated and presented in a unified format, yet pulling data from multiple data sources and displaying it in a coherent way can be onerous and error-prone. The challenge is compounded when the data resides in different databases, and possibly within different clouds.

To remedy this, companies often embark upon costly and time consuming data lake, data mart, and data warehouse projects. In many cases though, the IT team is simply looking for an effective solution to combine data within a single unified interface!

In this tutorial I’ll introduce you to a powerful and very popular feature of the DreamFactory platform called Data Mesh. Using Data Mesh you can create virtual relationships between two databases much in the same way you can create foreign key relationships between two database tables. We’ll walk through an example in which a MySQL database running on Amazon RDS is meshed with an IBM DB2 database running on IBM Cloud, merging the data together so it can be retrieved via a single API endpoint.

Data Mesh

Continue reading “Easier Data Marts with DreamFactory Data Mesh”

Creating a Geocoder Service Using DreamFactory and the Google Maps Geocoding API

There is no question DreamFactory’s native connectors have saved IT teams countless hours of development time. Yet these are almost incidental when one takes into consideration the platform’s ability to integrate with thousands of third-party REST and SOAP services, not to mention create entirely new APIs through the scripted service interface. Further, thanks to DreamFactory’s ability to leverage third-party libraries, new APIs can often be created in just a few dozen lines of code. In this tutorial you’ll learn how to create a DreamFactory-managed geocoding service using Spatie’s popular open source geocoder package. Using this service, you’ll be able to easily convert addresses into latitudinal and longitudinal coordinates, as well as perform reverse geocoding (convert coordinates into an address). Once complete, you’ll be able to use a JavaScript library such as Axios or a PHP library like Guzzle to add geocoding to your application with no additional coding required! Continue reading “Creating a Geocoder Service Using DreamFactory and the Google Maps Geocoding API”

DreamFactory News – Training, a New Guide, SaaS, and More

DreamFactory Jeanie
Our New Mascot. Say Hello to Jeanie. Stay Tuned For More

DreamFactory Happenings

It’s hard to believe the year’s end is almost upon us! This has been a pretty transformative year for the company. We’ve seen record demand for the DreamFactory Platform, and have additionally been working around the clock on a number of new initiatives: Continue reading “DreamFactory News – Training, a New Guide, SaaS, and More”

Improved Data Security with MySQL Privileges and DreamFactory

DreamFactory and MySQL

All MySQL installations naturally include a root account and offer the ability to create restricted user accounts. However, otherwise sane developers will often use these root accounts for application-level communication, dramatically raising the likelihood of data theft, data exfiltration, and other security issues. For that reason the DreamFactory team always recommends users take care to create restricted MySQL users before using the platform to generate APIs.

In this tutorial, you’ll learn how to create a non-root MySQL user and then further restrict this user’s privileges to a specific database and even table subset. You’ll also learn how to subsequently revoke a user’s privileges to reflect changing requirements.

Continue reading “Improved Data Security with MySQL Privileges and DreamFactory”

Filtering Related Columns within DreamFactory REST API Queries

Consider a query which joins employee records found in an employees table with information about their assigned department, the latter of which resides in a table named departments. The relationship is formalized using a key named emp_no. When DreamFactory parses the schema it will create aliases for each relationship, including one for the above-described named something like dept_emp_by_emp_no. The join query will therefore look like this:
/api/v2/mysql/_table/employees?related=dept_emp_by_emp_no
This would yield a JSON response containing records that look like this:
{
  "emp_no": 10001,
  "birth_date": "1953-09-02",
  "first_name": "Georgi",
  "last_name": "Facello",
  "gender": "M",
  "hire_date": "1986-06-26",
  "birth_year": "1953",
  "dept_emp_by_emp_no": [
    {
      "emp_no": 10001,
      "dept_no": "d005",
      "from_date": "1986-06-26",
      "to_date": "9999-01-01"
    }
  ]
},
If you wanted to limit the related fields to just dept_no and from_date, you would add dept_emp_by_emp_no.fields to the parameter list:
/api/v2/mysql/_table/employees?related=dept_emp_by_emp_no&dept_emp_by_emp_no.fields=dept_no,from_date
This query would yield records with the following structure:
{
  "emp_no": 10001,
  "birth_date": "1953-09-02",
  "first_name": "Georgi",
  "last_name": "Facello",
  "gender": "M",
  "hire_date": "1986-06-26",
  "birth_year": "1953",
  "dept_emp_by_emp_no": [
    {
      "dept_no": "d005",
      "from_date": "1986-06-26"
    }
  ]
},
You can learn more about working with related data inside DreamFactory on our wiki: http://wiki.dreamfactory.com/DreamFactory/Features/Database/Related_Data#Getting_the_Related_Data.

How To Quickly Create a Simple REST API for SQL Server Database

Would you like to access SQL data from your mobile, web or IOT apps?

To have an easy and secure way to add a REST API to any SQL database in minutes, we are going to walkthrough using DreamFactory to create create just that. All you have to do is use the DreamFactory platform to connect your database, then use it to auto-generate a REST API for your database – it’s that simple!

In this blog post we’ll show how to REST-enable any SQL database, which is free forever for the databases and other services covered by our open source software. Then we’ll show some simple examples of how to use the REST API to manage your SQL schema and data.

Interested in a live demo with one of our engineers? We’ll be happy to show you how it’s done for your particular use case! If you’d rather watch a video, check out DreamFactory Academy. Alternatively, you might want to check out our new guide, Getting Started with DreamFactory. It offers a comprehensive walkthrough of generating a database-backed API. You’ll learn how to generate the API, define a role and API key, and then interact with the API using a variety of queries.

Continue reading “How To Quickly Create a Simple REST API for SQL Server Database”

MySQL REST API Creation in Minutes – A Complete Tutorial

With 52% of professional developers rating it as the most popular database, wouldn’t it be ideal if you could create a MySQL REST API in minutes? Karl Hughes recently penned a blog post titled “The Bulk of Software Engineering in 2018 is Just Plumbing“. Notably he stated, “Just like plumbers, we are paid to know our tools and understand how they work together to make a usable piece of equipment, not to reinvent working technology…”. As programmers we should not be bothered with repeatedly writing code which is otherwise readily available, robust, and well-tested. 

Yet this problem remains persistent in the REST API space, despite the implementation process being by this point in time rote, repetitive, and prone to error and oversight. This oversight is costly for several reasons:

  • End users just *do not care* how the API was implemented, meaning there is no competitive advantage to be had by hand-crafting a new API for each project.
  • Error and oversight in the API implementation and deployment phase can come at a very steep price due to security lapses and performance issues.
  • Repeatedly building one-off APIs means they can’t be managed via a single platform or interface; unless the team decides to devote even more time and effort to building a custom management solution.

Fortunately, the DreamFactory platform can easily absolve your team from all of these hassles and much more by offering a centralized solution for the API generation, documentation, and security. In this tutorial you’ll learn just how easy it is to build, secure, and deploy a REST API for your MySQL database.

 

Did you know you can generate a full-featured, documented, and secure MySQL REST API in minutes using DreamFactory? Sign up for our free 14 day hosted trial to learn how! Our guided tour will show you how to create an API using an example MySQL database provided to you as part of the trial!

Create Your MySQL API Now

Generating the MySQL REST API

DreamFactory can generate REST APIs for a multitude of databases, among them MySQL, Microsoft SQL Server, Oracle, PostgreSQL, and MongoDB. To do so, you’ll login to the DreamFactory administration interface, navigate to Services and then enter the service creation interface by clicking on the Create button located to the left of the screen. From there you’ll select the MySQL service type by navigating to Database > MySQL (see below screenshot).

Services available within DreamFactory including MySQL

 

Next you’ll be prompted to provide a name, label, and description (below screenshot). The latter two are used just for reference purposes within the administration interface, however the name value is particularly important because as you’ll soon see it will comprise part of the API URL.

Naming your MySQL API

 

Finally, click on the Config tab. Here you’ll be prompted to provide the database connection credentials (see below screenshot). This should really be nothing new; you’ll supply a host name, username, password, and database. Additionally, you can optionally specify other configuration characteristics such as driver options, the timezone, and caching preferences. For the purpose of this tutorial I’ll stick to the required fields and leave the optional features untouched.

MySQL Database connection credentials

 

With the credentials in place, just press the Save button at the bottom of the screen, and believe it or not the REST API has been generated!

Viewing the Swagger Documentation

Along with the API, DreamFactory will also auto-generate an extensive set of interactive Swagger documentation. You can access it by clicking on the API Docs tab located at the top of the administration interface, and then selecting the newly generated service by name. You’ll be presented with 44 endpoints useful for executing stored procedures, carrying out CRUD operations, querying views, and much more. For instance the following screenshot presents just a small subset of newly generated MySQL REST API endpoints!

REST API Endpoints

 

Creating a Role and API Key

All DreamFactory-generated APIs are automatically protected by (at minimum) an API key. You can optionally authenticate users using basic authentication, SSO, or Directory Services (LDAP and Active Directory). Furthermore, you can associate each API key and/or user with a *role* which determines exactly what services the user is allowed to access. Not only that, you can restrict interactions to a specific database table or set of tables, a specific endpoint(s), and even restrict which HTTP methods are allowed.

Securing the users responsible for interacting with the MySQL database on behalf of your APIs is an incredibly important part of the API development and integration process. See our other recommendations on Improved Data Security with MySQL Privileges and DreamFactory

As an example, let’s create a new role which restricts the associated API key to interacting with a single table in a read-only fashion within the newly created MySQL API. To do so, navigate to the Roles tab, and click the Create button. You’ll be presented with the interface found in the below screenshot. In the screenshot you’ll see I’ve already assigned a name and description for the role, and made it active by selecting the Active checkbox.

Creating A Role

 

Next, click the Access tab. This is where you’ll define what the role can do. In the below screenshot you’ll see I’ve limited the role to interacting with the MySQL service, and within that service the role can only interact with the _table/employees* endpoint via the GET method. We’re on lockdown baby!

defining a role

 

Save the role by clicking the Save button. Now we’ll create a new API key and associate the key with this role. To do so, click on the Apps tab located at the top of the screen, and then click the Create button. Assign your new App a name and description, ensure it is set to Active, and then assign it the default role of MySQL just as I’ve done in the below screenshot. Regarding the App Location setting, presuming you plan on interacting with the API via a web or mobile application, or via another web service, then you’ll want to select “No storage required”.

create an API key

 

Press the Save button and you’ll be returned to the Apps index screen where the new API key can be copied! Copy the key into a text file for later reference.

Configuring CORS for your MySQL API

We have one final configuration step before being able to test the API from outside the DreamFactory administration interface. You’ll need to enable CORS (Cross-Origin Resource Sharing) for the new API. For purposes of demonstration, you can set the default CORS setting as I’ve done in the below screenshot, which will allow API-restricted traffic from all network addresses:

configuring CORS

 

Testing the MySQL REST API

With the API generated, API key and associated role created, and CORS configured, you’re ready to begin interacting with the API via a client! I like to use Insomnia for HTTP testing on MacOS, however another popular solution is Postman.

In the following screenshot I’m using Insomnia to contact the /api/v2/_table/employees endpoint using a GET request.

testing the API

 

Recall that we’ve locked down this API key to only interact with the /api/v2/_table/employees/* endpoints using the GET method. So what happens if we try to POST to this table? A 401 (Unauthorized) status code is returned, as depicted in the following screenshot:

error report

 

Alternative Approaches to MySQL API Creation

Obviously DreamFactory isn’t the only solution available. Check out these other popular tutorials for different perspectives on the topic:

Did you know you can generate a full-featured, documented, and secure MySQL REST API in minutes using DreamFactory? Sign up for our free 14 day hosted trial to learn how! Our guided tour will show you how to create an API using an example MySQL database provided to you as part of the trial!

Create Your MySQL API Now

Where to From Here?

Believe it or not, we’ve only scratched the surface in terms of what DreamFactory can do for you. If you’d like to see our SQL Server, Oracle, or MongoDB connectors in action, or would like to watch how easy it is to convert a SOAP service to REST without writing any code, why not schedule a demo with our engineering team! Head over to https://www.dreamfactory.com/products and schedule a demo today!

 

The importance of loose coupling in REST API design

One of the most important ideas in the world of software engineering is the concept of loose coupling. In a loosely coupled design, components are independent, and changes in one will not affect the operation of others. This approach offers optimal flexibility and reusability when components are added, replaced, or modified. Conversely, a tightly coupled design means that components tend to be interdependent. Changes in a single component can have a system wide impact, with unanticipated and undesirable effects.

Continue reading “The importance of loose coupling in REST API design”