Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create geoJSON from MySQL with PHP

Posted on 2013-11-26
9
Medium Priority
?
3,077 Views
Last Modified: 2013-12-27
I have a MySQL file that contains records with lat/long coords and I want to make it into a geoJSON file that I can then import into Leaflet. My problem is that the output is not formatting correct.

My code following the connection to the database is below.

The result looks like


{
  "0": {
    "type": "Feature",
    "properties": {
      "score": "370",
      "fid": "42"
    },
    "geometry": {
      "type": "Point",
      "coordinates": [
        -92.7340160253,
        41.7554453467
      ]
    }
  },
  "1": {
    "type": "Feature",
    "properties": {
      "score": "400",
      "fid": "43"
    },
    "geometry": {
      "type": "Point",
      "coordinates": [
        -92.7340136565,
        41.7534216347
      ]
    }
  },
  "type": "FeatureCollection",
  "features": null
}

Open in new window



but i need it to look more like this example where   "type": "FeatureCollection", is at the top and   "features": null becomes  "features": [


{ "type": "FeatureCollection",
  "features": [
    { "type": "Feature",
      "geometry": {"type": "Point", "coordinates": [-93.5, 44.25]},
      "properties": {"prop0": "value0"}
      },
       { "type": "Feature",
      "geometry": {"type": "Point", "coordinates": [-94.0, 44.5]},
      "properties": {"prop0": "value0"}
      }
     ]
   }

Open in new window






PHP Code

$geojson = array(
    'type'      => 'FeatureCollection',
    'features'  => $feature
 );
 
while($row = mysql_fetch_assoc($dbquery)) {
    $feature = array(
        'type' => 'Feature', 
		 'properties' => array(
			 'score' => $row['score'],
			 'fid' => $row['fid']
        //Other fields here, end without a comma
            ),
      'geometry' => array(
        'type' => 'Point',
        'coordinates' => array((float)$row['longitude'], (float)$row['latitude'])
            )
        );
    array_push($geojson, $feature);
};
mysql_close($connection);
 
// // Return routing result
    header("Content-Type:application/json",true);
    echo json_encode($geojson);

Open in new window

0
Comment
Question by:lvmllc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
9 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39680839
Please post the CREATE TABLE statement(s), the SQL query used to retrieve the data, and a few rows of test data.  I'll see if we can do this in the query, and if not, I'll try to show you how to reformat the query results set.  Thanks, ~Ray
0
 

Author Comment

by:lvmllc
ID: 39681011
I had never done this before so if there is a way to combine create and insert I don't know it. sorry, hope this works. I simplified this to have just the coords and a score and city attribute.

CREATE TABLE `nodes` (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(10) DEFAULT NULL,
  `latitude` varchar(50) DEFAULT NULL,
  `longitude` varchar(50) DEFAULT NULL,
  `score` int(4) DEFAULT NULL,
  PRIMARY KEY (`rid`)
) ENGINE=InnoDB AUTO_INCREMENT=335 DEFAULT CHARSET=latin1

Open in new window


The data

INSERT INTO `nodes` VALUES ('16', '15', '41.757233148600001', '-92.738275388999995', '140', 'Grinnell'), ('51', '50', '41.740346038900000', '-92.733205335099996', '740', 'Grinnell'), ('100', '99', '41.746167269600001', '-92.728819540200007', '890', 'Grinnell'), ('152', '151', '41.746163502300000', '-92.725776752399995', '920', 'Grinnell'), ('201', '200', '41.746121289100003', '-92.722513476000003', '540', 'Grinnell'), ('251', '250', '41.726907689900003', '-92.717662505899995', '310', 'Grinnell');
COMMIT;

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39681194
Thanks.  I'll try to set something up to test with and we can work through this.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39681237
Sometimes "simplifying" isn't the best option :-)

Fatal error:  QUERY FAILURE: ERRNO: 1136 ERROR: Column count doesn't match value count
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39681242
Suggestion... Why not store the lat/lon data as decimal(12,8)?  That will give you excellent precision and will avoid the rounding errors that come from float conversion.  You can compare two decimal values for equality with good results, but that's not always the case with float numbers.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 39681255
OK, I've gotten this far.  I'll look at reformatting the data next, but I have some pre-holiday things that may have to take precedence.  I'll post back as soon as I've had some time to look at this.  While I do that, you may want to familiarize yourself with the new data base extensions.  PHP is removing support for MySQL.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

See http://www.laprbass.com/RAY_temp_lvmllc.php

<?php // RAY_temp_lvmllc.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28304926.html


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// TEST DATA
$insert = <<<EOD
INSERT INTO `EE_nodes` VALUES ('16', '15', '41.757233148600001', '-92.738275388999995', '140', 'Grinnell'), ('51', '50', '41.740346038900000', '-92.733205335099996', '740', 'Grinnell'), ('100', '99', '41.746167269600001', '-92.728819540200007', '890', 'Grinnell'), ('152', '151', '41.746163502300000', '-92.725776752399995', '920', 'Grinnell'), ('201', '200', '41.746121289100003', '-92.722513476000003', '540', 'Grinnell'), ('251', '250', '41.726907689900003', '-92.717662505899995', '310', 'Grinnell');
EOD;

// TABLE CREATION QUERY
$create = <<<EOD
CREATE TEMPORARY TABLE `EE_nodes` (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(10) DEFAULT NULL,
  `latitude` varchar(50) DEFAULT NULL,
  `longitude` varchar(50) DEFAULT NULL,
  `score` int(4) DEFAULT NULL,
  thing VARCHAR(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`rid`)
) ENGINE=InnoDB AUTO_INCREMENT=335 DEFAULT CHARSET=latin1
EOD;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
require_once('RAY_live_data.php');
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$res = $mysqli->query('DROP TABLE EE_nodes');
$res = $mysqli->query($create);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $create
    ;
    trigger_error($err, E_USER_ERROR);
}

// LOADING THE TEST DATA
if (!$res = $mysqli->query($insert))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $insert
    ;
    trigger_error($err, E_USER_ERROR);
}

// INSPECT THE TABLE
$sql = "SELECT * FROM EE_nodes";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQLI FUNCTIONS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;

Open in new window

0
 

Author Comment

by:lvmllc
ID: 39681324
Thanks - I will take a look.
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question