Solved

Create geoJSON from MySQL with PHP

Posted on 2013-11-26
9
2,611 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
  • 5
  • 2
9 Comments
 
LVL 108

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 108

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 108

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 108

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 108

Accepted Solution

by:
Ray Paseur earned 500 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
php mail headers 2 12
Wordpress Body Class 5 12
Uncaught TypeError: can't read property 1 18
Strip leading 0 from a var 3 13
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now