admin管理员组文章数量:1202791
What I'm trying to do is very simple; get marker's data from a Mysql table with my PHP code, convert that that to a geoJson ( used by MapBox ), send that geoJson to javascript and then populate those marker into my map.
I've been reading very carefully the following two links, that apparently contains all the info I need to resolve this, but I'm not sure what I'm missing.
Here you can see an example on how to populate markers from a geoJson in MapBox;
Here you can see how to create a geoJson from a MySql table;
My sql table look like this, here is the creation code plus some dummy data;
-- phpMyAdmin SQL Dump
-- version 4.0.4.1
--
--
-- Host: localhost
-- Generation Time: Jul 21, 2013 at 03:26 PM
-- Server version: 5.5.32-cll-lve
-- PHP Version: 5.5.0
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `pgmq`
--
-- --------------------------------------------------------
--
-- Table structure for table `mapa`
--
CREATE TABLE IF NOT EXISTS `mapa` (
`contrato` int(11) NOT NULL,
`name` varchar(60) NOT NULL,
`address` varchar(80) NOT NULL,
`lat` float(10,6) NOT NULL,
`lng` float(10,6) NOT NULL,
`type` varchar(30) NOT NULL,
PRIMARY KEY (`contrato`),
UNIQUE KEY `contrato` (`contrato`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `mapa`
--
INSERT INTO `mapa` (`contrato`, `name`, `address`, `lat`, `lng`, `type`) VALUES
(217, 'DIVANIR BRASIL DA SILVA', '47 joao carolino da silva, guaruja - sao paulo, brazil', -23.950968, -46.289585, '11'),
(233, 'ADEMIR VIEIRA', '180 dois j, guaruja - sao paulo, brazil', -23.932041, -46.206879, '11'),
(241, 'MARIA CECILIA DOS SANTOS', '81 professor carvalho pinto, guaruja - sao paulo, brazil', -23.946516, -46.290428, '11'),
(252, 'ELIETE COSTA SANTOS', '387 maria de melo rodrigues, guaruja - sao paulo, brazil', -23.667521, -46.747810, '11'),
(271, 'ROBERTO SANTOS COUTO', '62 tapajos, guaruja - sao paulo, brazil', -23.949146, -46.284588, '11'),
(275, 'UMBERTO FERREIRA SOUZA NETO', '88 tapajos, guaruja - sao paulo, brazil', -23.949162, -46.284821, '11'),
(276, 'SERGIO RICARDO DOS SANTOS', '418 joana menezes de mello faro, guaruja - sao paulo, brazil', -23.994600, -46.256866, '11'),
(278, 'MARIA APARECIDA NUNES', '80 andre reboucas, guaruja - sao paulo, brazil', -23.945040, -46.297462, '11'),
(285, 'WALTECES SOUZA DA CONCEICAO', '298 maranhao, guaruja - sao paulo, brazil', -23.942638, -46.304131, '11'),
(286, 'ROBERTO AUGUSTO DE JESUS SOUZA', '38 dois c caic cinquenta e cinco , guaruja - sao paulo, brazil', -23.994600, -46.256866, '11');
So here is my php code, where I get the data from the DB and create a GeoJson.
<?php
$connect = mysql_connect("localhost","user","password");
$mapa = "SELECT * FROM pgmq.mapa ";
$dbquery = mysql_query($mapa,$connect);
$geojson = array( 'type' => 'FeatureCollection', 'features' => array());
while($row = mysql_fetch_assoc($dbquery)){
$marker = array(
'type' => 'Feature',
'features' => array(
'type' => 'Feature',
'properties' => array(
'title' => "".$row[name]."",
'marker-color' => '#f00',
'marker-size' => 'small'
//'url' =>
),
"geometry" => array(
'type' => 'Point',
'coordinates' => array(
$row[lat],
$row[lng]
)
)
)
);
array_push($geojson['features'], $marker['features']);
}
?>
Just to test this, if a "echo json_encode($marker)" here is a sample output;
{"type":"Feature","features":{"type":"Feature","properties":{"title":"DIVANIR BRASIL DA SILVA","marker-color":"#f00","marker-size":"small"},"geometry":{"type":"Point","coordinates":"[-23.950968, -46.289585]"}}};
Now on this is what the javascript code looks like;
<script>
var map = L.mapbox.map('map', 'examples.map-20v6611k').setView([-23.948714, -46.295508], 1);
// The GeoJSON representing the two point features
var geoJson = <?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?>;
// Pass features and a custom factory function to the map
map.markerLayer.setGeoJSON(geoJson);
map.markerLayer.on('click', function(e) {
e.layer.unbindPopup();
window.open(e.layer.feature.properties.url);
});
</script>
And finally the complete HTML that actually shows the map, but not the markers;
<?php
$connect = mysql_connect("localhost","user","pass");
$mapa = "SELECT * FROM pgmq.mapa ";
$dbquery = mysql_query($mapa,$connect);
$geojson = array( 'type' => 'FeatureCollection', 'features' => array());
while($row = mysql_fetch_assoc($dbquery)){
$marker = array(
'type' => 'Feature',
'features' => array(
'type' => 'Feature',
'properties' => array(
'title' => "".$row[name]."",
'marker-color' => '#f00',
'marker-size' => 'small'
//'url' =>
),
"geometry" => array(
'type' => 'Point',
'coordinates' => array(
$row[lat],
$row[lng]
)
)
)
);
array_push($geojson['features'], $marker['features']);
}
?>
<!DOCTYPE html>
<html>
<head>
<meta name='viewport' content='width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no' />
<script src='//api.tiles.mapbox/mapbox.js/v1.3.1/mapbox.js'></script>
<link href='//api.tiles.mapbox/mapbox.js/v1.3.1/mapbox.css' rel='stylesheet' />
<style>
body { margin:0; padding:0; }
#map { position:absolute; top:0; bottom:0; width:100%; }
</style>
</head>
<body>
<div id='map'></div>
<script>
var map = L.mapbox.map('map', 'examples.map-20v6611k').setView([-23.948714, -46.295508], 1);
// The GeoJSON representing the two point features
var geoJson = <?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?>;
// Pass features and a custom factory function to the map
map.markerLayer.setGeoJSON(geoJson);
map.markerLayer.on('click', function(e) {
e.layer.unbindPopup();
window.open(e.layer.feature.properties.url);
});
</script>
</body>
</html>
So, what am I missing? one thing I notice is that the output of my geoJson has " in the geometry array, while the one used in the MapBox example don't;
var geoJson = {
type: 'FeatureCollection',
features: [{
type: 'Feature',
properties: {
title: 'Washington, D.C.',
'marker-color': '#f00',
'marker-size': 'large',
url: ',_D.C.'
},
geometry: {
type: 'Point',
coordinates: [-77.03201, 38.90065]
}
},
{
type: 'Feature',
properties: {
title: 'Baltimore, MD',
'marker-color': '#f00',
'marker-size': 'large',
url: ''
},
geometry: {
type: 'Point',
coordinates: [-76.60767, 39.28755]
}
}]
};
Can anyone help me? there hasn't any real complication, i think it's just a data formatting problem or the way that I'm sending the GeoJson to the JS.
Thanks in advance!
I've just notice that this line;
var geoJson = <?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?>;
should be;
var geoJson = <?php echo json_encode($geojson,JSON_NUMERIC_CHECK); ?>;
and in that case the result is;
var map = L.mapbox.map('map', 'examples.map-20v6611k').setView([-23.948714, -46.295508], 1);
// The GeoJSON representing the two point features
var geoJson = ;
// Pass features and a custom factory function to the map
map.markerLayer.setGeoJSON(geoJson);
map.markerLayer.on('click', function(e) {
e.layer.unbindPopup();
window.open(e.layer.feature.properties.url);
});
What I'm trying to do is very simple; get marker's data from a Mysql table with my PHP code, convert that that to a geoJson ( used by MapBox ), send that geoJson to javascript and then populate those marker into my map.
I've been reading very carefully the following two links, that apparently contains all the info I need to resolve this, but I'm not sure what I'm missing.
Here you can see an example on how to populate markers from a geoJson in MapBox;
Here you can see how to create a geoJson from a MySql table;
My sql table look like this, here is the creation code plus some dummy data;
-- phpMyAdmin SQL Dump
-- version 4.0.4.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 21, 2013 at 03:26 PM
-- Server version: 5.5.32-cll-lve
-- PHP Version: 5.5.0
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `pgmq`
--
-- --------------------------------------------------------
--
-- Table structure for table `mapa`
--
CREATE TABLE IF NOT EXISTS `mapa` (
`contrato` int(11) NOT NULL,
`name` varchar(60) NOT NULL,
`address` varchar(80) NOT NULL,
`lat` float(10,6) NOT NULL,
`lng` float(10,6) NOT NULL,
`type` varchar(30) NOT NULL,
PRIMARY KEY (`contrato`),
UNIQUE KEY `contrato` (`contrato`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `mapa`
--
INSERT INTO `mapa` (`contrato`, `name`, `address`, `lat`, `lng`, `type`) VALUES
(217, 'DIVANIR BRASIL DA SILVA', '47 joao carolino da silva, guaruja - sao paulo, brazil', -23.950968, -46.289585, '11'),
(233, 'ADEMIR VIEIRA', '180 dois j, guaruja - sao paulo, brazil', -23.932041, -46.206879, '11'),
(241, 'MARIA CECILIA DOS SANTOS', '81 professor carvalho pinto, guaruja - sao paulo, brazil', -23.946516, -46.290428, '11'),
(252, 'ELIETE COSTA SANTOS', '387 maria de melo rodrigues, guaruja - sao paulo, brazil', -23.667521, -46.747810, '11'),
(271, 'ROBERTO SANTOS COUTO', '62 tapajos, guaruja - sao paulo, brazil', -23.949146, -46.284588, '11'),
(275, 'UMBERTO FERREIRA SOUZA NETO', '88 tapajos, guaruja - sao paulo, brazil', -23.949162, -46.284821, '11'),
(276, 'SERGIO RICARDO DOS SANTOS', '418 joana menezes de mello faro, guaruja - sao paulo, brazil', -23.994600, -46.256866, '11'),
(278, 'MARIA APARECIDA NUNES', '80 andre reboucas, guaruja - sao paulo, brazil', -23.945040, -46.297462, '11'),
(285, 'WALTECES SOUZA DA CONCEICAO', '298 maranhao, guaruja - sao paulo, brazil', -23.942638, -46.304131, '11'),
(286, 'ROBERTO AUGUSTO DE JESUS SOUZA', '38 dois c caic cinquenta e cinco , guaruja - sao paulo, brazil', -23.994600, -46.256866, '11');
So here is my php code, where I get the data from the DB and create a GeoJson.
<?php
$connect = mysql_connect("localhost","user","password");
$mapa = "SELECT * FROM pgmq.mapa ";
$dbquery = mysql_query($mapa,$connect);
$geojson = array( 'type' => 'FeatureCollection', 'features' => array());
while($row = mysql_fetch_assoc($dbquery)){
$marker = array(
'type' => 'Feature',
'features' => array(
'type' => 'Feature',
'properties' => array(
'title' => "".$row[name]."",
'marker-color' => '#f00',
'marker-size' => 'small'
//'url' =>
),
"geometry" => array(
'type' => 'Point',
'coordinates' => array(
$row[lat],
$row[lng]
)
)
)
);
array_push($geojson['features'], $marker['features']);
}
?>
Just to test this, if a "echo json_encode($marker)" here is a sample output;
{"type":"Feature","features":{"type":"Feature","properties":{"title":"DIVANIR BRASIL DA SILVA","marker-color":"#f00","marker-size":"small"},"geometry":{"type":"Point","coordinates":"[-23.950968, -46.289585]"}}};
Now on this is what the javascript code looks like;
<script>
var map = L.mapbox.map('map', 'examples.map-20v6611k').setView([-23.948714, -46.295508], 1);
// The GeoJSON representing the two point features
var geoJson = <?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?>;
// Pass features and a custom factory function to the map
map.markerLayer.setGeoJSON(geoJson);
map.markerLayer.on('click', function(e) {
e.layer.unbindPopup();
window.open(e.layer.feature.properties.url);
});
</script>
And finally the complete HTML that actually shows the map, but not the markers;
<?php
$connect = mysql_connect("localhost","user","pass");
$mapa = "SELECT * FROM pgmq.mapa ";
$dbquery = mysql_query($mapa,$connect);
$geojson = array( 'type' => 'FeatureCollection', 'features' => array());
while($row = mysql_fetch_assoc($dbquery)){
$marker = array(
'type' => 'Feature',
'features' => array(
'type' => 'Feature',
'properties' => array(
'title' => "".$row[name]."",
'marker-color' => '#f00',
'marker-size' => 'small'
//'url' =>
),
"geometry" => array(
'type' => 'Point',
'coordinates' => array(
$row[lat],
$row[lng]
)
)
)
);
array_push($geojson['features'], $marker['features']);
}
?>
<!DOCTYPE html>
<html>
<head>
<meta name='viewport' content='width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no' />
<script src='//api.tiles.mapbox.com/mapbox.js/v1.3.1/mapbox.js'></script>
<link href='//api.tiles.mapbox.com/mapbox.js/v1.3.1/mapbox.css' rel='stylesheet' />
<style>
body { margin:0; padding:0; }
#map { position:absolute; top:0; bottom:0; width:100%; }
</style>
</head>
<body>
<div id='map'></div>
<script>
var map = L.mapbox.map('map', 'examples.map-20v6611k').setView([-23.948714, -46.295508], 1);
// The GeoJSON representing the two point features
var geoJson = <?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?>;
// Pass features and a custom factory function to the map
map.markerLayer.setGeoJSON(geoJson);
map.markerLayer.on('click', function(e) {
e.layer.unbindPopup();
window.open(e.layer.feature.properties.url);
});
</script>
</body>
</html>
So, what am I missing? one thing I notice is that the output of my geoJson has " in the geometry array, while the one used in the MapBox example don't;
var geoJson = {
type: 'FeatureCollection',
features: [{
type: 'Feature',
properties: {
title: 'Washington, D.C.',
'marker-color': '#f00',
'marker-size': 'large',
url: 'http://en.wikipedia.org/wiki/Washington,_D.C.'
},
geometry: {
type: 'Point',
coordinates: [-77.03201, 38.90065]
}
},
{
type: 'Feature',
properties: {
title: 'Baltimore, MD',
'marker-color': '#f00',
'marker-size': 'large',
url: 'http://en.wikipedia.org/wiki/Baltimore'
},
geometry: {
type: 'Point',
coordinates: [-76.60767, 39.28755]
}
}]
};
Can anyone help me? there hasn't any real complication, i think it's just a data formatting problem or the way that I'm sending the GeoJson to the JS.
Thanks in advance!
I've just notice that this line;
var geoJson = <?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?>;
should be;
var geoJson = <?php echo json_encode($geojson,JSON_NUMERIC_CHECK); ?>;
and in that case the result is;
var map = L.mapbox.map('map', 'examples.map-20v6611k').setView([-23.948714, -46.295508], 1);
// The GeoJSON representing the two point features
var geoJson = ;
// Pass features and a custom factory function to the map
map.markerLayer.setGeoJSON(geoJson);
map.markerLayer.on('click', function(e) {
e.layer.unbindPopup();
window.open(e.layer.feature.properties.url);
});
- You are returning an array instead of Json data – Trying Tobemyself Commented Jul 21, 2013 at 19:01
- Ok, can you explain how to return json data instead? – Marcelo Grebois Commented Jul 21, 2013 at 21:05
5 Answers
Reset to default 6Have a look at this: https://github.com/bmcbride/PHP-Database-GeoJSON
You are returning array instead of Json data. This is what it should look like
<?php
/*
* Title: MySQL Points to GeoJSON
* Notes: Query a MySQL table or view of points with x and y columns and return the results in GeoJSON format, suitable for use in OpenLayers, Leaflet, etc.
* Author: Bryan R. McBride, GISP
* Contact: bryanmcbride.com
* GitHub: https://github.com/bmcbride/PHP-Database-GeoJSON
*/
# Connect to MySQL database
$conn = new PDO('pgsql:host=localhost;dbname=mypostgisdb','myusername','mypassword');
# Build SQL SELECT statement including x and y columns
$sql = 'SELECT *, x AS x, y AS y FROM mytable';
/*
* If bbox variable is set, only return records that are within the bounding box
* bbox should be a string in the form of 'southwest_lng,southwest_lat,northeast_lng,northeast_lat'
* Leaflet: map.getBounds().pad(0.05).toBBoxString()
*/
if (isset($_GET['bbox']) || isset($_POST['bbox'])) {
$bbox = explode(',', $_GET['bbox']);
$sql = $sql . ' WHERE x <= ' . $bbox[2] . ' AND x >= ' . $bbox[0] . ' AND y <= ' . $bbox[3] . ' AND y >= ' . $bbox[1];
}
# Try query or error
$rs = $conn->query($sql);
if (!$rs) {
echo 'An SQL error occured.\n';
exit;
}
# Build GeoJSON feature collection array
$geojson = array(
'type' => 'FeatureCollection',
'features' => array()
);
# Loop through rows to build feature arrays
while ($row = $rs->fetch(PDO::FETCH_ASSOC)) {
$properties = $row;
# Remove x and y fields from properties (optional)
unset($properties['x']);
unset($properties['y']);
$feature = array(
'type' => 'Feature',
'geometry' => array(
'type' => 'Point',
'coordinates' => array(
$row['x'],
$row['y']
)
),
'properties' => $properties
);
# Add feature arrays to feature collection array
array_push($geojson['features'], $feature);
}
header('Content-type: application/json');
echo json_encode($geojson, JSON_NUMERIC_CHECK);
$conn = NULL;
?>
You're close, and are on the right track here:
one thing I notice is that the output of my geoJson has " in the geometry array, while the one used in the MapBox example don't;
Yes, you need to make those quotes go away! Looking at your sample output you're getting a string rather than an array for the value of the 'coordinates' key in your array.
I'm not sure why this is the case based on your sample code, but something like this should work:
$geojson = array( 'type' => 'FeatureCollection', 'features' => array());
while($row = mysql_fetch_assoc($dbquery)){
$marker = array(
'type' => 'Feature',
'properties' => array(
'title' => $row['name'],
'marker-color' => '#f00',
'marker-size' => 'small'
),
'geometry' => array(
'type' => 'Point',
'coordinates' => array(
$row['lat'],
$row['lng']
)
)
);
array_push($geojson['features'], $marker);
}
Check this line
// The GeoJSON representing the two point features
var geoJson = ;
// Pass features and a custom factory function to the map
Try to add quotes around the php output
var geoJson = '';
$marker = array(
'type' => 'Feature',
//'features' => array(
//'type' => 'Feature',
'properties' => array(
'title' => $row->name,
'marker-color' =>'#0F8C29',
'marker-size' => 'small'
//'url' =>
),
"geometry" => array(
'type' => 'Point',
'coordinates' => array(
$row->lng,
$row->lat
)
)
//)
);
array_push($geojson['features'], $marker);Final answer:
<?php
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS' ,'');
define('DB_NAME', 'pgmq');
$connect = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);
$mapa = "SELECT * FROM mapa ";
$dbquery = mysqli_query($connect,$mapa);
$geojson = array( 'type' => 'FeatureCollection', 'features' => array());
while($row = mysqli_fetch_assoc($dbquery)){
$marker = array(
'type' => 'Feature',
'properties' => array(
'title' => $row['name'],
'marker-color' => '#f00',
'marker-size' => 'small'
),
'geometry' => array(
'type' => 'Point',
'coordinates' => array(
$row['lng'],
$row['lat']
)
)
);
array_push($geojson['features'], $marker);
}
header('Content-type: application/json');
echo json_encode($geojson, JSON_NUMERIC_CHECK);
?>
I believe, it will help others. You can verify the response at: https://geojsonlint.com/
本文标签： Creating a GeoJson in php from MySql to use with MapBox javascript APIStack Overflow
版权声明：本文标题：Creating a GeoJson in php from MySql to use with MapBox javascript API - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1738656761a2105186.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论