Integrating ArcGIS with MySQL, PHP, Google Maps API and Python

Introduction

This tutorial is intended for web developers who are familiar with web technologies, and want to learn how to integrate ArcGIS with them. The challenge here is how to integrate different technologies to serve a specific purpose.  We will develop a simple website that displays the upcoming events in Gaza Strip, as markers, on the Google basemap. The events could be political, economic, social, commercial, educational or whatever.  They will be geocoded as markers (point features).   The website consists of one single dynamic webpage. That means it is always updated with the upcoming events which are stored in MySQL database.

The pipeline of the whole process starts in ArcGIS. One creates events in ArcGIS, and stores them as a point shapefile or geodatbase feature class. Then, a Python script tool loads the feature class in MySQL database. In a next step, PHP is employed to extract the events data from MySQL database as XML file. Outputting the events data as XML is essential for for transferring them to the client side. Finally, the XML file is parsed and the events are geocoded as markers using Google Maps API.

This tutorial tries to demonstrate how one can link different technologies to serve a specific purpose. We will use several web technologies among many. The technologies used in this tutorial are listed below:

On server-side:

-          MySQL

-          PHP

-          Python

-          ArcGIS

On Client-side:

-          HTML

-          CSS

-          JavaScript

-          Google Maps API

You can come up with a different solution to this challenge and hence you may select different technologies. That depends on different factors; with which technology you are familiar is just one among others.

Figure 1 shows the different technologies used in this tutorial.

Figure 1

How?

The tutorial is broken up into following steps:

Step 1: Creating the events feature class

In this step, we will create a feature class to hold the events. You can create events directly in MySQL database, but you need to provide the lat and long coordinates. That is not probably suitable for fast production environment. Alternatively, you can use any GIS Software such as Google Earth, Quantum GIS, ArcGIS or whatever to geocode the events and then load them in MySQL database. In this tutorial, we will use ArcGIS in doing so. ArcGIS is one of the most common commercial software. Its capability in creating, storing, processing, and analysing spatial data is very powerful.

The feature class should be created inside a geodatabase. The geodatabase could be file, personal, or ArcSDE one. We will create a file geodatabase. For more information on Geodatabase, please refer to the following link:

http://resources.arcgis.com/en/help/main/10.1/index.html#/What_is_a_geodatabase/003n00000001000000/

The feature class is a table which stores spatial data along with attribute data. Therefore, we need to identify which pieces of data the feature class will store about events. The following table shows the data dictionary of the events feature class.

Table 1: Data Dictionary of the events feature class

data	description	Data type
Title	A brief description of the event (name)	Text
Description	A detailed description of the event	Text
Sponsor/Organizer	The institution/individual that organizes the event	Text
Start date	The date and time when the event starts	Date
End date	The date and time when the event ends	Date
Place	The place where the event will be hold	Text
Created date	The date when the event is entered into the database	Date
Sponsor website	The website of the organizer (if applicable)	Text
Contact name	The person whom one can contact for more information	Text
Tel	Telephone Number	Date
Mobile	Mobile Number	Text
Email	Email	Text
Lat	Latitude of the event	Float
Long	Longitude of the event	Float
Transferred	Is the event transferred to MySQL database?	Boolean

The following screenshot shows the events feature class created, added to ArcMap, and populated with events.

Figure 2

To learn how to create a feature class and edit spatial and attribute data in ArcGIS, please refer to the following links:

http://resources.arcgis.com/en/help/main/10.1/index.html#//002200000003000000

http://resources.arcgis.com/en/help/main/10.1/index.html#//01m500000003000000

Step 2: Create the MySQL Database

Having the feature class been created, one can go to a further step, that is, creating a table inside a MySQL database. The table is intended to hold the same data and schema as the feature class. Therefore, we need first to create a MySQL Database. Then, a MySQL table, with the same schema as feature class, is to be created.

To ease creating the MySQL table, phpMyAdmin tool can be used to perform that. The following screenshot shows the table created along with its schema. The database with a name of spatialdb is first created. In a next step, the table geoevents is constructed with the fields defined as shown in Figure 3. Notice that it is nearly the same schema as the feature class created previously in ArcGIS.

Some of you may wonder why we create the MySQL table manually. Is there a way to export the feature class as it is to a MySQL table? In other words, is there a method where one can export the feature class to MySQL table automatically? If so, this will save time and effort. In fact, one can do that, but since we create the events data in ArcGIS, we do not want to create a new table each time we load the events in MySQL database. Therefore, MySQL table will be created once and then its content can be updated whenever new events are created. 

Figure 3

Step 3: Develop the python Script tool

This step aims at developing a geoprocessing tool using Python to load the events data, created in ArcGIS and stored in the feature class, into the MySQL table. In other words, we need to transfer the events data from the ESRI File Geodatabase into MySQL database. In fact, ArcGIS can store the data directly in the database management systems such as SQL Server and Oracle through the ArcSDE Geodatabase. However, until the time of writing this tutorial, ArcGIS, particularly ArcGIS 10.1, does not support that capability for MySQL DBMS. That is the driving force behind developing this tool.

ArcGIS provides a mechanism in which you can develop geoprocessing tools working from inside ArcGIS using Python. We will make use of this feature and develop a tool that loads the events data directly into MySQL Database. The tool will do the following:

-          It will receive the inputs from the user. The inputs would be the events feature class, the connection details of MySQL database and a predefined template. Please have a look at Figure 4.

-          The tool first check whether the schema of input feature class conforms to a predefined template. That is necessary to ensure that all required fields and information does exist in the input feature class.

-          In a next step, the tool will read the events data from the relevant feature class and then insert them in the MySQL table. This step requires the tool to connect to the MySQL database. Therefore, all necessary credentials should be provided (cf. Figure 4).

-          Finally, the tool will update the value of the field "Transferred" to take the value of 1. The "Transferred" field contains the value 0 before the transferring process and is updated with the value 1 after the process to indicate that the record is transferred and does not need to be transferred again in a future process.

Figure 4: the interface of the developed tool

The following is the Python code which drives the tool.

The result of running the tool is that all events are inserted into the MySQL table as the following figure shows.

Figure 5

Step 4: output XML with PHP

Having the events data stored in MySQL database, we now need to expose this data to the client through a dynamic webpage which displays the events as markers together with Google basemap. That requires transferring the data from the server to client side. The most exchange format used in the web is the xml format. The events data will be retrieved through asynchronous JavaScript calls. The challenge now is how to translate the MySQL table into xml file. That can be easily done using PHP. The PHP is a web programming language that works on the server side. It has the functionality to talk with databases, particularly the MySQL database. I will not detail this step. I just want to show you the final script. You can go through more details by visiting this page:

https://developers.google.com/maps/articles/phpsqlajax_v3

The final script is the same as you will find on the aforementioned page except some little modifications to suit our needs.

Step 5: create the webpage (map)

It is the time to display the events along with their attributes on a Goggle map. This requires design a webpage which displays the google maps and the events a s markers. Having the events in xml format, we need to parse it and extract all the needed data of events. Then, the events will be geocoded using Google Maps API. Again, I will not go in more details in this step. You can find more information on the following link:

https://developers.google.com/maps/articles/phpsqlajax_v3

I just want to show you the final html file. Again The final script is the same as you will find on the aforementioned page except some little modifications to suit our needs.

Demo 

Here is a short video demonstrating the whole process from creating data in ArcGIS, until the events are loaded on the map.