All geek questions in one place

Creating kml file from mysql database with php

I hope there is a php genius here that can help me with this. I have a database table called baeir , it has the following fields:

b_id (primary key)
b_name
hrepparid (no need to display)
stjarna (no need to display)
Lat
Lng
comments

The table contains the name of the farm (b_name), its geographical coordinates (lat, lng) and comments, if any.

I need this data to be transferred to the kml file. I tried to go through the tutorial on google web page and I got it to work, but as soon as I try to edit it, it goes to h ***. As far as I understand, the kml file is basically an XML file, but unfortunately my PHP skills are not advanced enough to handle this.

Hope someone can help :-))

This is the code I have on google and it looks a bit ... detailed to my taste. Any ideas on how to simplify the code so that I can include my variables in it and nothing more?

<?php require('phpsqlajax_dbinfo.php'); // Opens a connection to a MySQL server. $connection = mysql_connect ($server, $username, $password); if (!$connection) { die('Not connected : ' . mysql_error()); } // Sets the active MySQL database. $db_selected = mysql_select_db($database, $connection); if (!$db_selected) { die('Can\'t use db : ' . mysql_error()); } // Selects all the rows in the markers table. $query = 'SELECT * FROM markers WHERE 1'; $result = mysql_query($query); if (!$result) { die('Invalid query: ' . mysql_error()); } // Creates the Document. $dom = new DOMDocument('1.0', 'UTF-8'); // Creates the root KML element and appends it to the root document. $node = $dom->createElementNS('http://earth.google.com/kml/2.1', 'kml'); $parNode = $dom->appendChild($node); // Creates a KML Document element and append it to the KML element. $dnode = $dom->createElement('Document'); $docNode = $parNode->appendChild($dnode); // Creates the two Style elements, one for restaurant and one for bar, and append the elements to the Document element. $restStyleNode = $dom->createElement('Style'); $restStyleNode->setAttribute('id', 'restaurantStyle'); $restIconstyleNode = $dom->createElement('IconStyle'); $restIconstyleNode->setAttribute('id', 'restaurantIcon'); $restIconNode = $dom->createElement('Icon'); $restHref = $dom->createElement('href', 'http://maps.google.com/mapfiles/kml/pal2/icon63.png'); $restIconNode->appendChild($restHref); $restIconstyleNode->appendChild($restIconNode); $restStyleNode->appendChild($restIconstyleNode); $docNode->appendChild($restStyleNode); $barStyleNode = $dom->createElement('Style'); $barStyleNode->setAttribute('id', 'barStyle'); $barIconstyleNode = $dom->createElement('IconStyle'); $barIconstyleNode->setAttribute('id', 'barIcon'); $barIconNode = $dom->createElement('Icon'); $barHref = $dom->createElement('href', 'http://maps.google.com/mapfiles/kml/pal2/icon27.png'); $barIconNode->appendChild($barHref); $barIconstyleNode->appendChild($barIconNode); $barStyleNode->appendChild($barIconstyleNode); $docNode->appendChild($barStyleNode); // Iterates through the MySQL results, creating one Placemark for each row. while ($row = @mysql_fetch_assoc($result)) { // Creates a Placemark and append it to the Document. $node = $dom->createElement('Placemark'); $placeNode = $docNode->appendChild($node); // Creates an id attribute and assign it the value of id column. $placeNode->setAttribute('id', 'placemark' . $row['id']); // Create name, and description elements and assigns them the values of the name and address columns from the results. $nameNode = $dom->createElement('name',htmlentities($row['name'])); $placeNode->appendChild($nameNode); $descNode = $dom->createElement('description', $row['address']); $placeNode->appendChild($descNode); $styleUrl = $dom->createElement('styleUrl', '#' . $row['type'] . 'Style'); $placeNode->appendChild($styleUrl); // Creates a Point element. $pointNode = $dom->createElement('Point'); $placeNode->appendChild($pointNode); // Creates a coordinates element and gives it the value of the lng and lat columns from the results. $coorStr = $row['lng'] . ',' . $row['lat']; $coorNode = $dom->createElement('coordinates', $coorStr); $pointNode->appendChild($coorNode); } $kmlOutput = $dom->saveXML(); while (@ob_end_clean()); header('content-type:text/xml;'); echo $kmlOutput; ?>
+6
source share
3 answers

as stated above, the following google maps tutorial answers your question completely: http://code.google.com/apis/kml/articles/phpmysqlkml.html

Unfortunately, it does not offer code for parsing polygons (which you will need if you control the display of farm areas), but you can adapt the LinesString parsing method and achieve this. beware of correctly embedding the outerBoundaryIs tag in the Polygon tag And remember that you must duplicate the starting point for the polygon to draw correctly.

  $lineNode = $dom->createElement('Polygon'); $placeNode = $placeNode->appendChild($lineNode); $exnode = $dom->createElement('extrude', '1'); $lineNode->appendChild($exnode); $almodenode =$dom->createElement(altitudeMode,'relativeToGround'); $lineNode->appendChild($almodenode); $outerboundnode = $dom->createElement('outerBoundaryIs'); $placeNode = $placeNode->appendChild($outerboundnode); $ringnode =$dom->createElement('LinearRing'); $placeNode = $placeNode->appendChild($ringnode); // optional styletag colors the polygon //$stylenode =$dom->createElement(styleUrl,'#transYellowPoly'); //$lineNode->appendChild($stylenode); //Create a coordinates element and give it the value of the lng and lat columns from the results //$coorNode = $dom->createElement('coordinates',$row['coordinates']); $coorNode = $dom->createElement('coordinates',$coordinates); $placeNode = $placeNode->appendChild($coorNode);
+2
source

Try the following:

 <?php require('phpsqlajax_dbinfo.php'); function xmlEntities($str) { $xml = array('&#34;','&#38;','&#38;','&#60;','&#62;','&#160;','&#161;','&#162;','&#163;','&#164;','&#165;','&#166;','&#167;','&#168;','&#169;','&#170;','&#171;','&#172;','&#173;','&#174;','&#175;','&#176;','&#177;','&#178;','&#179;','&#180;','&#181;','&#182;','&#183;','&#184;','&#185;','&#186;','&#187;','&#188;','&#189;','&#190;','&#191;','&#192;','&#193;','&#194;','&#195;','&#196;','&#197;','&#198;','&#199;','&#200;','&#201;','&#202;','&#203;','&#204;','&#205;','&#206;','&#207;','&#208;','&#209;','&#210;','&#211;','&#212;','&#213;','&#214;','&#215;','&#216;','&#217;','&#218;','&#219;','&#220;','&#221;','&#222;','&#223;','&#224;','&#225;','&#226;','&#227;','&#228;','&#229;','&#230;','&#231;','&#232;','&#233;','&#234;','&#235;','&#236;','&#237;','&#238;','&#239;','&#240;','&#241;','&#242;','&#243;','&#244;','&#245;','&#246;','&#247;','&#248;','&#249;','&#250;','&#251;','&#252;','&#253;','&#254;','&#255;'); $html = array('&quot;','&amp;','&amp;','&lt;','&gt;','&nbsp;','&iexcl;','&cent;','&pound;','&curren;','&yen;','&brvbar;','&sect;','&uml;','&copy;','&ordf;','&laquo;','&not;','&shy;','&reg;','&macr;','&deg;','&plusmn;','&sup2;','&sup3;','&acute;','&micro;','&para;','&middot;','&cedil;','&sup1;','&ordm;','&raquo;','&frac14;','&frac12;','&frac34;','&iquest;','&Agrave;','&Aacute;','&Acirc;','&Atilde;','&Auml;','&Aring;','&AElig;','&Ccedil;','&Egrave;','&Eacute;','&Ecirc;','&Euml;','&Igrave;','&Iacute;','&Icirc;','&Iuml;','&ETH;','&Ntilde;','&Ograve;','&Oacute;','&Ocirc;','&Otilde;','&Ouml;','&times;','&Oslash;','&Ugrave;','&Uacute;','&Ucirc;','&Uuml;','&Yacute;','&THORN;','&szlig;','&agrave;','&aacute;','&acirc;','&atilde;','&auml;','&aring;','&aelig;','&ccedil;','&egrave;','&eacute;','&ecirc;','&euml;','&igrave;','&iacute;','&icirc;','&iuml;','&eth;','&ntilde;','&ograve;','&oacute;','&ocirc;','&otilde;','&ouml;','&divide;','&oslash;','&ugrave;','&uacute;','&ucirc;','&uuml;','&yacute;','&thorn;','&yuml;'); $str = str_replace($html,$xml,$str); $str = str_ireplace($html,$xml,$str); return $str; } // Opens a connection to a MySQL server. $connection = mysql_connect ($server, $username, $password); if (!$connection) { die('Not connected : ' . mysql_error()); } // Sets the active MySQL database. $db_selected = mysql_select_db($database, $connection); if (!$db_selected) { die('Can\'t use db : ' . mysql_error()); } // Selects all the rows in the markers table. $query = 'SELECT * FROM baeir WHERE 1'; $result = mysql_query($query); if (!$result) { die('Invalid query: ' . mysql_error()); } // Creates the Document. $dom = new DOMDocument('1.0', 'UTF-8'); // Creates the root KML element and appends it to the root document. $node = $dom->createElementNS('http://earth.google.com/kml/2.1', 'kml'); $parNode = $dom->appendChild($node); // Creates a KML Document element and append it to the KML element. $dnode = $dom->createElement('Document'); $docNode = $parNode->appendChild($dnode); // Creates the two Style elements, one for restaurant and one for bar, and append the elements to the Document element. $restStyleNode = $dom->createElement('Style'); $restStyleNode->setAttribute('id', 'restaurantStyle'); $restIconstyleNode = $dom->createElement('IconStyle'); $restIconstyleNode->setAttribute('id', 'restaurantIcon'); $restIconNode = $dom->createElement('Icon'); $restHref = $dom->createElement('href', 'http://maps.google.com/mapfiles/kml/pal2/icon63.png'); $restIconNode->appendChild($restHref); $restIconstyleNode->appendChild($restIconNode); $restStyleNode->appendChild($restIconstyleNode); $docNode->appendChild($restStyleNode); $barStyleNode = $dom->createElement('Style'); $barStyleNode->setAttribute('id', 'barStyle'); $barIconstyleNode = $dom->createElement('IconStyle'); $barIconstyleNode->setAttribute('id', 'barIcon'); $barIconNode = $dom->createElement('Icon'); $barHref = $dom->createElement('href', 'http://maps.google.com/mapfiles/kml/pal2/icon27.png'); $barIconNode->appendChild($barHref); $barIconstyleNode->appendChild($barIconNode); $barStyleNode->appendChild($barIconstyleNode); $docNode->appendChild($barStyleNode); // Iterates through the MySQL results, creating one Placemark for each row. while ($row = @mysql_fetch_assoc($result)) { // Creates a Placemark and append it to the Document. $node = $dom->createElement('Placemark'); $placeNode = $docNode->appendChild($node); // Creates an id attribute and assign it the value of id column. $placeNode->setAttribute('id', 'placemark' . $row['b_id ']); // Create name, and description elements and assigns them the values of the name and address columns from the results. $nameNode = $dom->createElement('name',xmlEntities(htmlentities($row['b_name']))); $placeNode->appendChild($nameNode); $descNode = $dom->createElement('description', $row['comments']); $placeNode->appendChild($descNode); //$styleUrl = $dom->createElement('styleUrl', '#' . $row['type'] . 'Style'); //$placeNode->appendChild($styleUrl); // Creates a Point element. $pointNode = $dom->createElement('Point'); $placeNode->appendChild($pointNode); // Creates a coordinates element and gives it the value of the lng and lat columns from the results. $coorStr = $row['lng'] . ',' . $row['lat']; $coorNode = $dom->createElement('coordinates', $coorStr); $pointNode->appendChild($coorNode); } $kmlOutput = $dom->saveXML(); while (@ob_end_clean()); header('content-type:text/xml;'); echo $kmlOutput; ?>
+2
source

kml has nothing to do with your php. Your kml file is basically an xml file that is specifically used in google map applications. Your Kml file must pass a validation test for you to use. You can use the following link to check your kml file. Kml validator . Your kml file will contain predefined tags such as point coordinate description so that it can be parsed. If you want to see what the kml file looks like, here you go

Kml file example

Needless to say, your KML should contain information in the appropriate tags, and then you need to analyze it. eg lat and lng can enter the coordinate tag. The description tag may contain your text description, comment.

+1
source

Source: https://habr.com/ru/post/904108/

More articles:


All Articles