How to Translate MySQL Data into GeoJSON Using PHP

I’ve seen a couple StackExchange posts about taking data from a MySQL table and trying to display it on a Leaflet map, but none of the solutions seemed to be fully baked or applicable in my situation.  This example comes from my side project, a Midwest hiking website at www.nocoastbestcoast.com, a site I built from scratch using PHP and MySQL.

I wanted to build a map using LeafletJS to display a point for the trailhead of each hike I posted about.  When I first started, I would go through the arduous process of creating a new GeoJSON using QGIS and then replace the file on my server to update my map every time I posted a new hike.  After doing that 20 times, I decided I needed to improve my programming skills and finally figure out how to automate this process by extracting the data from my site’s MySQL database.

Each hike is stored as a row in the database.  While MySQL does support spatial fields, I was unfamiliar when I first built the site, and once I learned more I decided I didn’t want to deal with the conversion of coordinates from WKT format to the comma separated format used in GeoJSON.  So, I stored my point coordinates in separate fields, one for latitude and one for longitude.

The following code is placed in the map page’s <head>.  Start out by establishing your database connection and checking if it works:

$con = mysqli_connect("SERVER_NAME","USER","PASSWORD", "DATABASE_NAME");

// Check connection
 if (mysqli_connect_errno())
  {echo "Failed to connect to MySQL: " . mysqli_connect_error();

Next, execute a MySQL query to select the relevant data or display an error message if it fails:

$sql = "SELECT * FROM TABLE";
$result = mysqli_query($con, $sql) or print ("Can't select entries from table php_blog.<br>" . $sql . "<br>" . mysqli_error());

After the query, initialize a variable for your features as an empty array.  Then create a while loop that iterates through each entry in the table and pushes the data reformatted as GeoJSON to the features array.  Consider which fields other than latitude and longitude you may want in your map, especially if you want to display popups or filter the data.

$features = [];
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
 $feature = array(
   'type' => 'Feature',
   //Include the geometry information for each point by adding your 
   //lat and lon fields as an array of floats
   //*Remember that longitude comes first in the coordinate pair in GeoJSON*
   'geometry' => array(
   'type' => 'Point',
   'coordinates' => array((float)$row['Lon'], (float)$row['Lat'])
   ),
   
   //Add the other fields you want as properties
   //Copy the format of 'name' => $row['name'] to add other properties
   'properties' => array(
   'id' => $row['id'],
   'name' => $row['name']
   )
 );
 //Push each $feature to the empty array $features created earlier
 array_push($features, $feature);
}

Once you have your array of features, echo a with the standard GeoJSON heading (change the coordinate system if necessary), then use json_encode to add your features to the end.

echo 
'var hikes = 
 {"type": "FeatureCollection",
 "name": "hikes",
 "crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
 "features":';
echo json_encode($features);
echo'}';

Now you have a nice GeoJSON in the <head> of your web page that you can use and manipulate with your favorite mapping API (obviously LeafletJS).  You could probably follow the same method with some additional conversion if you use the WKT spatial fields in MySQL —  let me know if you have success with this.  Happy mapping!

Advertisements

3 thoughts on “How to Translate MySQL Data into GeoJSON Using PHP

  1. Do you have to use something after features?
    I get there only a “Null” value displayed
    The connection to the database is available

    echo
    ‘var
    hikes = {“type”: “FeatureCollection”,
    “name”: ”
    hikes “, “crs”: {“type”: “name”, “properties”: {“name”: “urn: ogc: def : crs: OGC: 1.3: CRS84 “}},
    ” features “: ‘; <——- ——————————————————————————-
    echo json_encode ($ features);
    Echo'}';

    Like

    1. Everything should work as written if you’ve copy and pasted each of the code blocks in the post — “echo json_encode($features);” should append all of the features and their properties. Check that your properties — e.g. ‘id’=>$row[‘ID’] — match the columns in your database, noting that the field names are case sensitive. In the past, I’ve also had problems with string fields using apostrophes and quotation marks, so a quick find and replace of those might help too.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s