Solved

Create geoJSON from MySQL with PHP

Posted on 2013-11-26
9
2,665 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how can display objects ? 3 22
PHP warning 4 30
jQuery - how can I set focus to first valid element in dropdown? 2 14
is this a cms? 8 34
jQuery is a JavaScript library that greatly simplifies JavaScript programming. AJAX is an acronym formed from "Asynchronous JavaScript and XML."  AJAX refers to any communication between client and server, when the human client does not observe a…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

912 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

17 Experts available now in Live!

Get 1:1 Help Now