Loading points from Mysql database
7,162 views
Skip to first unread message
John Lo
May 6, 2012, 2:49:55 AM5/6/12
to Leaflet
Hi,
Pretty new to this and was wondering how one could load points from a
mysql database as opposed to a geojson file using leaflet? There
doesn't seem to be a tutorial to do this on the Leaflet website.
Google Maps advises to convert the mysql data into an xml file, which
is then parsed...would a similar approach be necessary for Leaflet?
Sorry to be a bother, but thanks for any help!
Pretty new to this and was wondering how one could load points from a
mysql database as opposed to a geojson file using leaflet? There
doesn't seem to be a tutorial to do this on the Leaflet website.
Google Maps advises to convert the mysql data into an xml file, which
is then parsed...would a similar approach be necessary for Leaflet?
Sorry to be a bother, but thanks for any help!
Elliott
May 6, 2012, 4:03:31 AM5/6/12
to leafl...@googlegroups.com
Hi there,
You can do this by hand, but I'd encourage you to use geoPHP. The following assumes that you're storing stuff as a spatial field in mysql...
Grab your result from the database as wkb, by using the following in your mysql statement
ASBINARY(fieldname)
then load that data into geophp:
$polygon = geoPHP::load($row['ASBINARY(fieldname)'],'wkb');
Then you can use geoPHP's adapters to output it as geojson:
$geojson_writer = new geojson()
$geojson = $geojson_writer ->write($geometry);
From there you want to follow leaflet's geojson example:
var geojsonFeature = "<?php echo $geojson; ?>"; //this is untested and may need tweaking.
var geojsonLayer = new L.GeoJSON();
map.addLayer(geojsonLayer);
geojsonLayer.addGeoJSON(geojsonFeature);
var geojsonLayer = new L.GeoJSON();
map.addLayer(geojsonLayer);
geojsonLayer.addGeoJSON(geojsonFeature);
G'luck.
Elliott
John Lo
May 7, 2012, 10:59:30 AM5/7/12
to Leaflet
Thanks for the example,
Would something like this work?
// Change this depending on the name of your PHP file
downloadUrl("phpsqlajax_genxml.php", function(data) {
var xml = data.responseXML;
var markers =
xml.documentElement.getElementsByTagName("marker");
for (var i = 0; i < markers.length; i++) {
var name = markers[[]i].getAttribute("name");
var address = markers[[]i].getAttribute("address");
var type = markers[[]i].getAttribute("type");
var point = new L.LatLng(
parseFloat(markers[[]i].getAttribute("lat")),
parseFloat(markers[[]i].getAttribute("lng")));
var html = "<b>" + name + "</b> <br/>" + address;
var icon = customIcons[[]type] || {};
var marker = new L.Marker({
map: map,
position: point,
icon: icon.icon,
shadow: icon.shadow
});
bindInfoWindow(marker, map, infoWindow, html);
}
});
}
Where phpsqlajax_genxml.php is...
<?php
// Database, Password, Username
require("phpsqlajax_dbinfo.php");
// Start XML file, create parent node
$doc = domxml_new_doc("1.0");
$node = $doc->create_element("markers");
$parnode = $doc->append_child($node);
// Opens a connection to a MySQL server
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) {
die('Not connected : ' . mysql_error());
}
// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die ('Can\'t use db : ' . mysql_error());
}
// Select all the rows in the markers table
$query = "SELECT * FROM markers WHERE 1";
$result = mysql_query($query);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
header("Content-type: text/xml");
// Iterate through the rows, adding XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
// ADD TO XML DOCUMENT NODE
$node = $doc->create_element("marker");
$newnode = $parnode->append_child($node);
$newnode->set_attribute("name", $row['name']);
$newnode->set_attribute("address", $row['address']);
$newnode->set_attribute("lat", $row['lat']);
$newnode->set_attribute("lng", $row['lng']);
$newnode->set_attribute("type", $row['type']);
}
$xmlfile = $doc->dump_mem();
echo $xmlfile;
?>
Thanks for all the help!
> You can do this by hand, but I'd encourage you to use geoPHP<https://github.com/phayes/geoPHP>.
> :
> *
Would something like this work?
// Change this depending on the name of your PHP file
downloadUrl("phpsqlajax_genxml.php", function(data) {
var xml = data.responseXML;
var markers =
xml.documentElement.getElementsByTagName("marker");
for (var i = 0; i < markers.length; i++) {
var name = markers[[]i].getAttribute("name");
var address = markers[[]i].getAttribute("address");
var type = markers[[]i].getAttribute("type");
var point = new L.LatLng(
parseFloat(markers[[]i].getAttribute("lat")),
parseFloat(markers[[]i].getAttribute("lng")));
var html = "<b>" + name + "</b> <br/>" + address;
var icon = customIcons[[]type] || {};
var marker = new L.Marker({
map: map,
position: point,
icon: icon.icon,
shadow: icon.shadow
});
bindInfoWindow(marker, map, infoWindow, html);
}
});
}
Where phpsqlajax_genxml.php is...
<?php
// Database, Password, Username
require("phpsqlajax_dbinfo.php");
// Start XML file, create parent node
$doc = domxml_new_doc("1.0");
$node = $doc->create_element("markers");
$parnode = $doc->append_child($node);
// Opens a connection to a MySQL server
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) {
die('Not connected : ' . mysql_error());
}
// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die ('Can\'t use db : ' . mysql_error());
}
// Select all the rows in the markers table
$query = "SELECT * FROM markers WHERE 1";
$result = mysql_query($query);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
header("Content-type: text/xml");
// Iterate through the rows, adding XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
// ADD TO XML DOCUMENT NODE
$node = $doc->create_element("marker");
$newnode = $parnode->append_child($node);
$newnode->set_attribute("name", $row['name']);
$newnode->set_attribute("address", $row['address']);
$newnode->set_attribute("lat", $row['lat']);
$newnode->set_attribute("lng", $row['lng']);
$newnode->set_attribute("type", $row['type']);
}
$xmlfile = $doc->dump_mem();
echo $xmlfile;
?>
Thanks for all the help!
> You can do this by hand, but I'd encourage you to use geoPHP<https://github.com/phayes/geoPHP>.
> The following assumes that you're storing stuff as a spatial field in
> mysql...
>
> Grab your result from the database as wkb, by using the following in your
> mysql statement
> ASBINARY(fieldname)
>
> then load that data into geophp:
> $polygon = geoPHP::load($row['ASBINARY(fieldname)'],'wkb');
>
> Then you can use geoPHP's adapters to output it as geojson:
> $geojson_writer = new geojson()
> $geojson = $geojson_writer ->write($geometry);
>
> From there you want to follow leaflet's geojson example<http://leaflet.cloudmade.com/examples/geojson.html>
> mysql...
>
> Grab your result from the database as wkb, by using the following in your
> mysql statement
> ASBINARY(fieldname)
>
> then load that data into geophp:
> $polygon = geoPHP::load($row['ASBINARY(fieldname)'],'wkb');
>
> Then you can use geoPHP's adapters to output it as geojson:
> $geojson_writer = new geojson()
> $geojson = $geojson_writer ->write($geometry);
>
> :
> var geojsonFeature = "<?php echo $geojson; ?>"; //this is untested and
> may need tweaking.
> var geojsonLayer = new L.GeoJSON();
> map.addLayer(geojsonLayer);
> geojsonLayer.addGeoJSON(geojsonFeature);
> *
> may need tweaking.
> var geojsonLayer = new L.GeoJSON();
> map.addLayer(geojsonLayer);
> geojsonLayer.addGeoJSON(geojsonFeature);
> *
Elliott
May 7, 2012, 11:23:59 AM5/7/12
to leafl...@googlegroups.com
Pfft, I don't know. First things first, I think that downloadUrl function is a g00ble maps tool, non?
Secondly, I'd strongly encourage you not going via xml/kml file to get your data into leaflet. Leaflet doesn't have built in support for it, whereas it does have support for geojson, which is far less verbose, so a bit easier. geoPHP isn't going to help that much if you're not using spatial fields in mysql.
So when you do your while($row=mysql_fetch...){} loop...
(All that follows is untested.) Before your while loop, say:
$coordsArray = new array();
Then in the loop, say:
$coordsArray[] = array($row['lng'], $row['lat']); //pushes onto array
Then after your while loop, bang it all into a geojson-esque object, and json_encode() it. I tend to not bother with headers (pfft), but you definitely don't want text/xml being served.
$result = array(
"type":"MultiPoint",
"coordinates": $coordsArray);
"type":"MultiPoint",
"coordinates": $coordsArray);
echo json_encode($result);
//compare to http://geojson.org/geojson-spec.html#id5 to check you've got it right
Then you can use that geojson straight in your page by following this example:
Then you can use that geojson straight in your page by following this example:
Does anyone else have any thoughts on this, and does this seem to be a fair approach?
Cheers
Elliott
Nicolas Esteban
May 7, 2012, 11:33:10 AM5/7/12
to leafl...@googlegroups.com
I use Postgres + Postgis (wich can convert sql results to geojson).
With this, I use a Play server (but you can use what you want) to get my geojson.
And finally, from Leaflet point of view, I use a JsonLayer.
John Lo
May 7, 2012, 10:51:56 PM5/7/12
to Leaflet
Hmmm using getJSON and sqlite database...
function getUsers() {
$.getJSON("get_users.php", function (data) {
for (var i = 0; i < data.length; i++) {
var location = new L.LatLng(data[i].lat, data[i].lng);
// random username, could be any info that is placed
within
var name = data[i].name;
// random website name, could be any info that is placed
within
var website = data[i].website;
if (data[i].website.length > 7) {
var title = "<div style='font-size: 18px; color:
#0078A8;'><a href='"+ data[i].website +"' target='_blank'>"+
data[i].name + "</a></div>";
}
else {
var title = "<div style='font-size: 18px; color:
#0078A8;'>"+ data[i].name +"</div>";
}
if (data[i].city.length > 0) {
var city = "<div style='font-size: 14px;'>"+
data[i].city +"</div>";
}
else {
var city = "";
}
var marker = new L.Marker(location);
marker.bindPopup("<div style='text-align: center; margin-
left: auto; margin-right: auto;'>"+ title + city +"</div>", {maxWidth:
'400'});
users.addLayer(marker);
}
}).complete(function() {
if (firstLoad == true) {
map.fitBounds(users.getBounds());
firstLoad = false;
};
});
}
Where get_users.php is...
<?php
$db = new PDO('sqlite:leaflet.sqlite');
$sql = "SELECT id, name, website, city, lat, lng FROM users;";
$rs = $db->query($sql);
if (!$rs) {
echo "An SQL error occured.\n";
exit;
}
$rows = array();
while($r = $rs->fetch(PDO::FETCH_ASSOC)) {
$rows[] = $r;
}
print json_encode($rows);
$db = NULL;
?>
On May 7, 11:33 pm, Nicolas Esteban <n.esteba...@gmail.com> wrote:
> I use Postgres + Postgis (wich can convert sql results to geojson).
> With this, I use a Play server (but you can use what you want) to get my
> geojson.
> And finally, from Leaflet point of view, I use a JsonLayer.
>
> I don't know if Mysql has a Postgis like module.
>
> 2012/5/7 Elliott <elliotthuns...@gmail.com>
> >> var address = markers[[]i].getAttribute("**address");
> >> var type = markers[[]i].getAttribute("**type");
> >> parseFloat(markers[[]i].**getAttribute("lng")));
> >> $newnode = $parnode->append_child($node);
>
> >> $newnode->set_attribute("name"**, $row['name']);
> >> $newnode->set_attribute("**address", $row['address']);
function getUsers() {
$.getJSON("get_users.php", function (data) {
for (var i = 0; i < data.length; i++) {
var location = new L.LatLng(data[i].lat, data[i].lng);
// random username, could be any info that is placed
within
var name = data[i].name;
// random website name, could be any info that is placed
within
var website = data[i].website;
if (data[i].website.length > 7) {
var title = "<div style='font-size: 18px; color:
#0078A8;'><a href='"+ data[i].website +"' target='_blank'>"+
data[i].name + "</a></div>";
}
else {
var title = "<div style='font-size: 18px; color:
#0078A8;'>"+ data[i].name +"</div>";
}
if (data[i].city.length > 0) {
var city = "<div style='font-size: 14px;'>"+
data[i].city +"</div>";
}
else {
var city = "";
}
var marker = new L.Marker(location);
marker.bindPopup("<div style='text-align: center; margin-
left: auto; margin-right: auto;'>"+ title + city +"</div>", {maxWidth:
'400'});
users.addLayer(marker);
}
}).complete(function() {
if (firstLoad == true) {
map.fitBounds(users.getBounds());
firstLoad = false;
};
});
}
Where get_users.php is...
<?php
$db = new PDO('sqlite:leaflet.sqlite');
$sql = "SELECT id, name, website, city, lat, lng FROM users;";
$rs = $db->query($sql);
if (!$rs) {
echo "An SQL error occured.\n";
exit;
}
$rows = array();
while($r = $rs->fetch(PDO::FETCH_ASSOC)) {
$rows[] = $r;
}
print json_encode($rows);
$db = NULL;
?>
On May 7, 11:33 pm, Nicolas Esteban <n.esteba...@gmail.com> wrote:
> I use Postgres + Postgis (wich can convert sql results to geojson).
> With this, I use a Play server (but you can use what you want) to get my
> geojson.
> And finally, from Leaflet point of view, I use a JsonLayer.
>
> I don't know if Mysql has a Postgis like module.
>
>
>
>
>
>
>
>
> > Pfft, I don't know. First things first, I think that downloadUrl function
> > is a g00ble maps tool, non?
>
> > Secondly, I'd strongly encourage you not going via xml/kml file to get
> > your data into leaflet. Leaflet doesn't have built in support for it,
> > whereas it does have support for geojson, which is far less verbose, so a
> > bit easier. geoPHP isn't going to help that much if you're not using
> > spatial fields in mysql.
>
> > So when you do your while($row=mysql_fetch...){} loop...
> > (All that follows is untested.) Before your while loop, say:
> > $coordsArray = new array();
>
> > Then in the loop, say:
> > $coordsArray[] = array($row['lng'], $row['lat']); //pushes onto array
>
> > Then after your while loop, bang it all into a geojson-esque object, and
> > json_encode() it. I tend to not bother with headers (pfft), but you
> > definitely don't want text/xml being served.
> > $result = array(
> > "type":"MultiPoint",
> > "coordinates": $coordsArray);
> > echo json_encode($result); //compare to
> >http://geojson.org/geojson-spec.html#id5to check you've got it right
>
>
>
>
>
>
> > Pfft, I don't know. First things first, I think that downloadUrl function
> > is a g00ble maps tool, non?
>
> > Secondly, I'd strongly encourage you not going via xml/kml file to get
> > your data into leaflet. Leaflet doesn't have built in support for it,
> > whereas it does have support for geojson, which is far less verbose, so a
> > bit easier. geoPHP isn't going to help that much if you're not using
> > spatial fields in mysql.
>
> > So when you do your while($row=mysql_fetch...){} loop...
> > (All that follows is untested.) Before your while loop, say:
> > $coordsArray = new array();
>
> > Then in the loop, say:
> > $coordsArray[] = array($row['lng'], $row['lat']); //pushes onto array
>
> > Then after your while loop, bang it all into a geojson-esque object, and
> > json_encode() it. I tend to not bother with headers (pfft), but you
> > definitely don't want text/xml being served.
> > $result = array(
> > "type":"MultiPoint",
> > "coordinates": $coordsArray);
> > echo json_encode($result); //compare to
>
> > Then you can use that geojson straight in your page by following this
> > example:
> >http://leaflet.cloudmade.com/examples/geojson.html
>
> > Does anyone else have any thoughts on this, and does this seem to be a
> > fair approach?
> > Cheers
> > Elliott
>
> > On Monday, 7 May 2012 15:59:30 UTC+1, John Lo wrote:
>
> >> Thanks for the example,
>
> >> Would something like this work?
>
> >> // Change this depending on the name of your PHP file
> >> downloadUrl("phpsqlajax_**genxml.php", function(data) {
> > Then you can use that geojson straight in your page by following this
> > example:
> >http://leaflet.cloudmade.com/examples/geojson.html
>
> > Does anyone else have any thoughts on this, and does this seem to be a
> > fair approach?
> > Cheers
> > Elliott
>
> > On Monday, 7 May 2012 15:59:30 UTC+1, John Lo wrote:
>
> >> Thanks for the example,
>
> >> Would something like this work?
>
> >> // Change this depending on the name of your PHP file
> >> var xml = data.responseXML;
> >> var markers =
> >> xml.documentElement.**getElementsByTagName("marker")**;
> >> var markers =
> >> for (var i = 0; i < markers.length; i++) {
> >> var name = markers[[]i].getAttribute("**name");
> >> var address = markers[[]i].getAttribute("**address");
> >> var type = markers[[]i].getAttribute("**type");
> >> var point = new L.LatLng(
> >> parseFloat(markers[[]i].**getAttribute("lat")),
> >> parseFloat(markers[[]i].**getAttribute("lng")));
> >> var html = "<b>" + name + "</b> <br/>" + address;
> >> var icon = customIcons[[]type] || {};
> >> var marker = new L.Marker({
> >> map: map,
> >> position: point,
> >> icon: icon.icon,
> >> shadow: icon.shadow
> >> });
> >> bindInfoWindow(marker, map, infoWindow, html);
> >> }
> >> });
> >> }
>
> >> Where phpsqlajax_genxml.php is...
>
> >> <?php
> >> // Database, Password, Username
> >> require("phpsqlajax_dbinfo.**php");
> >> var icon = customIcons[[]type] || {};
> >> var marker = new L.Marker({
> >> map: map,
> >> position: point,
> >> icon: icon.icon,
> >> shadow: icon.shadow
> >> });
> >> bindInfoWindow(marker, map, infoWindow, html);
> >> }
> >> });
> >> }
>
> >> Where phpsqlajax_genxml.php is...
>
> >> <?php
> >> // Database, Password, Username
>
> >> // Start XML file, create parent node
> >> $doc = domxml_new_doc("1.0");
> >> $node = $doc->create_element("markers"**);
> >> // Start XML file, create parent node
> >> $doc = domxml_new_doc("1.0");
> >> $parnode = $doc->append_child($node);
>
> >> // Opens a connection to a MySQL server
> >> $connection=mysql_connect (localhost, $username, $password);
> >> if (!$connection) {
> >> die('Not connected : ' . mysql_error());
> >> }
>
> >> // Set the active MySQL database
> >> $db_selected = mysql_select_db($database, $connection);
> >> if (!$db_selected) {
> >> die ('Can\'t use db : ' . mysql_error());
> >> }
>
> >> // Select all the rows in the markers table
> >> $query = "SELECT * FROM markers WHERE 1";
> >> $result = mysql_query($query);
> >> if (!$result) {
> >> die('Invalid query: ' . mysql_error());
> >> }
>
> >> header("Content-type: text/xml");
>
> >> // Iterate through the rows, adding XML nodes for each
> >> while ($row = @mysql_fetch_assoc($result)){
> >> // ADD TO XML DOCUMENT NODE
> >> $node = $doc->create_element("marker")**;
>
> >> // Opens a connection to a MySQL server
> >> $connection=mysql_connect (localhost, $username, $password);
> >> if (!$connection) {
> >> die('Not connected : ' . mysql_error());
> >> }
>
> >> // Set the active MySQL database
> >> $db_selected = mysql_select_db($database, $connection);
> >> if (!$db_selected) {
> >> die ('Can\'t use db : ' . mysql_error());
> >> }
>
> >> // Select all the rows in the markers table
> >> $query = "SELECT * FROM markers WHERE 1";
> >> $result = mysql_query($query);
> >> if (!$result) {
> >> die('Invalid query: ' . mysql_error());
> >> }
>
> >> header("Content-type: text/xml");
>
> >> // Iterate through the rows, adding XML nodes for each
> >> while ($row = @mysql_fetch_assoc($result)){
> >> // ADD TO XML DOCUMENT NODE
> >> $newnode = $parnode->append_child($node);
>
> >> $newnode->set_attribute("name"**, $row['name']);
> >> $newnode->set_attribute("**address", $row['address']);
> >> $newnode->set_attribute("lat", $row['lat']);
> >> $newnode->set_attribute("lng", $row['lng']);
> >> $newnode->set_attribute("type"**, $row['type']);
> >> $newnode->set_attribute("lng", $row['lng']);
> >> }
>
> >> $xmlfile = $doc->dump_mem();
> >> echo $xmlfile;
>
> >> ?>
>
> >> Thanks for all the help!
>
> >> On May 6, 4:03 pm, Elliott <elliotthuns...@gmail.com> wrote:
> >> > Hi there,
>
> >> > You can do this by hand, but I'd encourage you to use geoPHP<
> >>https://github.com/**phayes/geoPHP<https://github.com/phayes/geoPHP>>.
>
> >> $xmlfile = $doc->dump_mem();
> >> echo $xmlfile;
>
> >> ?>
>
> >> Thanks for all the help!
>
> >> On May 6, 4:03 pm, Elliott <elliotthuns...@gmail.com> wrote:
> >> > Hi there,
>
> >> > You can do this by hand, but I'd encourage you to use geoPHP<
> >> > The following assumes that you're storing stuff as a spatial field in
> >> > mysql...
>
> >> > Grab your result from the database as wkb, by using the following in
> >> your
> >> > mysql statement
> >> > ASBINARY(fieldname)
>
> >> > then load that data into geophp:
> >> > $polygon = geoPHP::load($row['ASBINARY(**fieldname)'],'wkb');
> >> > mysql...
>
> >> > Grab your result from the database as wkb, by using the following in
> >> your
> >> > mysql statement
> >> > ASBINARY(fieldname)
>
> >> > then load that data into geophp:
>
> >> > Then you can use geoPHP's adapters to output it as geojson:
> >> > $geojson_writer = new geojson()
> >> > $geojson = $geojson_writer ->write($geometry);
>
> >> > From there you want to follow leaflet's geojson example<http://leaflet.
> >> **cloudmade.com/examples/**geojson.html<http://leaflet.cloudmade.com/examples/geojson.html>>
> >> > Then you can use geoPHP's adapters to output it as geojson:
> >> > $geojson_writer = new geojson()
> >> > $geojson = $geojson_writer ->write($geometry);
>
> >> > From there you want to follow leaflet's geojson example<http://leaflet.
>
> >> > :
> >> > var geojsonFeature = "<?php echo $geojson; ?>"; //this is untested
> >> and
> >> > may need tweaking.
> >> > var geojsonLayer = new L.GeoJSON();
> >> > map.addLayer(geojsonLayer);
> >> > geojsonLayer.addGeoJSON(**geojsonFeature);
> >> > :
> >> > var geojsonFeature = "<?php echo $geojson; ?>"; //this is untested
> >> and
> >> > may need tweaking.
> >> > var geojsonLayer = new L.GeoJSON();
> >> > map.addLayer(geojsonLayer);
Elliott
May 8, 2012, 3:41:17 AM5/8/12
to leafl...@googlegroups.com
So, err, does it work? ;) You're definitely a lot closer to a working answer.
Elliott
Charles Bedrosian
May 8, 2012, 4:12:11 AM5/8/12
to leafl...@googlegroups.com
2 parts to this problem and I am not sure which is the problem or both. I found it helpful to break it down to (1) generating the geojson and (2) loading the geojson (making sure it was valid). I am not using but rather rails. However I am hand building the geojson and it was not always clear the right format. My suggestion is get the geojson generated in a text editor and load it using the geojson sample. Once you have your own geojson rendering properly use php to generate it. Keep it simple if possible - you can start by echoing and concatenation of strings even. No libraries are needed and really i do t think you are going to be able to rely on the built in json serializers.
HTH!
Charles
Sent from my iPhone
Sent from my iPhone
John Lo
May 8, 2012, 5:09:16 AM5/8/12
to Leaflet
Hey Everyone,
Here's some code that loads points and popup info stored in a mysql
database, i've run it a couple times and seems to be working fairly
well! Cheers:
1) Create example database 'poi', with table 'users' with rows id
(INT), name (VARCHAR), lat(DOUBLE), lng(DOUBLE)
create_db.php:
<?php
$sql = "CREATE TABLE `poi`.`users` (`id` INT(11) NOT NULL
AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(60) CHARACTER SET utf8
COLLATE utf8_unicode_ci NOT NULL, `lat` DOUBLE(10,6) NOT NULL, `lng`
DOUBLE(10,6) NOT NULL) ENGINE = MyISAM;";
?>
2) Get stored data from database 'poi' (replace 'localhost'(host)
'poi'(database name) 'root'(username) and ''(database password) with
your relevant database information)...json_encode comes in handy
get_info.php:
<?php
$db = new PDO('mysql:host=localhost;dbname=poi', 'root', '');
javascript function on your index.html:
function getInfo() {
$.getJSON("get_info.php", function (data) {
'200'});
find this useful!
On May 8, 4:12 pm, Charles Bedrosian <char...@bedrosian.com> wrote:
> 2 parts to this problem and I am not sure which is the problem or both. I found it helpful to break it down to (1) generating the geojson and (2) loading the geojson (making sure it was valid). I am not using but rather rails. However I am hand building the geojson and it was not always clear the right format. My suggestion is get the geojson generated in a text editor and load it using the geojson sample. Once you have your own geojson rendering properly use php to generate it. Keep it simple if possible - you can start by echoing and concatenation of strings even. No libraries are needed and really i do t think you are going to be able to rely on the built in json serializers.
>
> HTH!
> Charles
>
> Sent from my iPhone
>
> > >http://geojson.org/geojson-spec.html#id5tocheck you've got it right
> > >> **cloudmade.com/examples/**geojson.html<http://cloudmade.com/examples/**geojson.html><http://leaflet.cloudmade.com/examples/geojson.html>>
Here's some code that loads points and popup info stored in a mysql
database, i've run it a couple times and seems to be working fairly
well! Cheers:
1) Create example database 'poi', with table 'users' with rows id
(INT), name (VARCHAR), lat(DOUBLE), lng(DOUBLE)
create_db.php:
<?php
$sql = "CREATE TABLE `poi`.`users` (`id` INT(11) NOT NULL
AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(60) CHARACTER SET utf8
COLLATE utf8_unicode_ci NOT NULL, `lat` DOUBLE(10,6) NOT NULL, `lng`
DOUBLE(10,6) NOT NULL) ENGINE = MyISAM;";
?>
2) Get stored data from database 'poi' (replace 'localhost'(host)
'poi'(database name) 'root'(username) and ''(database password) with
your relevant database information)...json_encode comes in handy
get_info.php:
<?php
$db = new PDO('mysql:host=localhost;dbname=poi', 'root', '');
$sql = "SELECT id, name, website, city, lat, lng FROM users;";
$rs = $db->query($sql);
if (!$rs) {
echo "An SQL error occured.\n";
exit;
}
$rows = array();
while($r = $rs->fetch(PDO::FETCH_ASSOC)) {
$rows[] = $r;
}
print json_encode($rows);
$db = NULL;
?>
3) Displaying the points from the database using the getInfo()
$rs = $db->query($sql);
if (!$rs) {
echo "An SQL error occured.\n";
exit;
}
$rows = array();
while($r = $rs->fetch(PDO::FETCH_ASSOC)) {
$rows[] = $r;
}
print json_encode($rows);
$db = NULL;
?>
javascript function on your index.html:
function getInfo() {
$.getJSON("get_info.php", function (data) {
for (var i = 0; i < data.length; i++) {
var location = new L.LatLng(data[i].lat, data[i].lng);
var location = new L.LatLng(data[i].lat, data[i].lng);
var name = data[i].name;
var marker = new L.Marker(location);
marker.bindPopup("<div style='text-align: center; margin-
left: auto; margin-right: auto;'>"+ title +"</div>", {maxWidth:
marker.bindPopup("<div style='text-align: center; margin-
'200'});
users.addLayer(marker);
}
}).complete(function() {
if (firstLoad == true) {
map.fitBounds(users.getBounds());
firstLoad = false;
};
});
}
Thanks for all the suggestions and help thus far and hope you guys
}
}).complete(function() {
if (firstLoad == true) {
map.fitBounds(users.getBounds());
firstLoad = false;
};
});
}
find this useful!
On May 8, 4:12 pm, Charles Bedrosian <char...@bedrosian.com> wrote:
> 2 parts to this problem and I am not sure which is the problem or both. I found it helpful to break it down to (1) generating the geojson and (2) loading the geojson (making sure it was valid). I am not using but rather rails. However I am hand building the geojson and it was not always clear the right format. My suggestion is get the geojson generated in a text editor and load it using the geojson sample. Once you have your own geojson rendering properly use php to generate it. Keep it simple if possible - you can start by echoing and concatenation of strings even. No libraries are needed and really i do t think you are going to be able to rely on the built in json serializers.
>
> HTH!
> Charles
>
> Sent from my iPhone
>
tr4ding
May 9, 2012, 1:20:45 AM5/9/12
to leafl...@googlegroups.com
Hi there,
when you say: "Grab your result from the database as wkb, by using the following in your mysql statement ASBINARY(fieldname)"......I don't know how to do this....I'd like to learn how to integrate GEOPHP and use GEOJSON on Leaflet.
I have POIs stored on a Mysql db with coordinates such these: 15.932155, 35.568129 and I'd like to load on a Leaflet Map.
Thanks in advance!!
Noli Sicad
May 9, 2012, 1:36:51 AM5/9/12
to leafl...@googlegroups.com
I think MySQL users should develop PHP MySQL to GeoJSON using 2 PHP scripts.
PHP SQLite to GeoJSON
https://gist.github.com/1920796
For MySQL connection have a look at this.
PostGIS to GeoJSON
https://gist.github.com/1913855
This is posted by Bryan McBride.
https://groups.google.com/forum/?fromgroups#!topic/leaflet-js/x_TO4dgvygQ
Noli
Noli
On 5/9/12, tr4ding <alessandr...@gmail.com> wrote:
> Hi there,
>
> when you say: "Grab your result from the database as wkb, by using the
> following in your mysql statement ASBINARY(fieldname)"......I don't know
> how to do this....I'd like to learn how to integrate GEOPHP and use GEOJSON
> on Leaflet.
>
> I have POIs stored on a Mysql db with coordinates such
> these: 15.932155, 35.568129 and I'd like to load on a Leaflet Map.
>
> Thanks in advance!!
>
> Il giorno domenica 6 maggio 2012 10:03:31 UTC+2, Elliott ha scritto:
>>
>> Hi there,
>>
>> You can do this by hand, but I'd encourage you to use
>> geoPHP<https://github.com/phayes/geoPHP>.
>> :
>> *
PHP SQLite to GeoJSON
https://gist.github.com/1920796
For MySQL connection have a look at this.
PostGIS to GeoJSON
https://gist.github.com/1913855
This is posted by Bryan McBride.
https://groups.google.com/forum/?fromgroups#!topic/leaflet-js/x_TO4dgvygQ
Noli
Noli
On 5/9/12, tr4ding <alessandr...@gmail.com> wrote:
> Hi there,
>
> when you say: "Grab your result from the database as wkb, by using the
> following in your mysql statement ASBINARY(fieldname)"......I don't know
> how to do this....I'd like to learn how to integrate GEOPHP and use GEOJSON
> on Leaflet.
>
> I have POIs stored on a Mysql db with coordinates such
> these: 15.932155, 35.568129 and I'd like to load on a Leaflet Map.
>
> Thanks in advance!!
>
> Il giorno domenica 6 maggio 2012 10:03:31 UTC+2, Elliott ha scritto:
>>
>> Hi there,
>>
>> You can do this by hand, but I'd encourage you to use
>> The following assumes that you're storing stuff as a spatial field in
>> mysql...
>>
>> Grab your result from the database as wkb, by using the following in your
>> mysql statement
>> ASBINARY(fieldname)
>>
>> then load that data into geophp:
>> $polygon = geoPHP::load($row['ASBINARY(fieldname)'],'wkb');
>>
>> Then you can use geoPHP's adapters to output it as geojson:
>> $geojson_writer = new geojson()
>> $geojson = $geojson_writer ->write($geometry);
>>
>> From there you want to follow leaflet's geojson
>> example<http://leaflet.cloudmade.com/examples/geojson.html>
>> mysql...
>>
>> Grab your result from the database as wkb, by using the following in your
>> mysql statement
>> ASBINARY(fieldname)
>>
>> then load that data into geophp:
>> $polygon = geoPHP::load($row['ASBINARY(fieldname)'],'wkb');
>>
>> Then you can use geoPHP's adapters to output it as geojson:
>> $geojson_writer = new geojson()
>> $geojson = $geojson_writer ->write($geometry);
>>
>> From there you want to follow leaflet's geojson
>> :
>> var geojsonFeature = "<?php echo $geojson; ?>"; //this is untested and
>> may need tweaking.
>> var geojsonLayer = new L.GeoJSON();
>> map.addLayer(geojsonLayer);
>> geojsonLayer.addGeoJSON(geojsonFeature);
>> *
>> may need tweaking.
>> var geojsonLayer = new L.GeoJSON();
>> map.addLayer(geojsonLayer);
>> geojsonLayer.addGeoJSON(geojsonFeature);
>> *
Reply all
Reply to author
Forward
Message has been deleted
0 new messages