Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3327
  • Last Modified:

Create geoJSON from MySQL with PHP

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
lvmllc
Asked:
lvmllc
  • 5
  • 2
1 Solution
 
Ray PaseurCommented:
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
 
lvmllcAuthor Commented:
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
 
Ray PaseurCommented:
Thanks.  I'll try to set something up to test with and we can work through this.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ray PaseurCommented:
Sometimes "simplifying" isn't the best option :-)

Fatal error:  QUERY FAILURE: ERRNO: 1136 ERROR: Column count doesn't match value count
0
 
Ray PaseurCommented:
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
 
Ray PaseurCommented:
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
 
lvmllcAuthor Commented:
Thanks - I will take a look.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now