i want to store some data that i have gained from a request at the planetfile of openstreetmap: so i have set up a database on my opensuse 13.1
test
CREATE TABLE `pois` (
`id` bigint(20) unsigned NOT NULL,
`lat` float(10,7) NOT NULL,
`lon` float(10,7) NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `pois_tag` (
`poisid` int(11) NOT NULL DEFAULT '0',
`tagname` varchar(45) NOT NULL DEFAULT '',
`tagvalue` varchar(255) DEFAULT NULL,
PRIMARY KEY (`poisid`,`tagname`)
Where each tagname/value pair is stored as a row in a separate table with the pois id and the subsequent processing would be like this
<?php
$db = new mysqli(localhost,root,my-passwd,'test'); // use my own credentials
$xmlstr = <<<XML
<data>
<node id="2064639440" lat="49.4873181" lon="8.4710548">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="turkish"/>
<tag k="email" v="info@lynso.de"/>
<tag k="name" v="Kilim - Café und Bar Restaurant"/>
<tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/>
<tag k="operator" v="Cengiz Kaya"/>
<tag k="phone" v="06 21 - 43 755 371"/>
<tag k="website" v="http://ift.tt/1nXyZdR"/>
</node>
<node id="2126473801" lat="49.4851170" lon="8.4756295">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="italian"/>
<tag k="email" v="mannheim1@vapiano.de"/>
<tag k="fax" v="+49 621 1259 779"/>
<tag k="name" v="Vapiano"/>
<tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/>
<tag k="operator" v="Vapiano"/>
<tag k="phone" v="+49 621 1259 777"/>
<tag k="website" v="http://ift.tt/1nXyXT5"/>
<tag k="wheelchair" v="yes"/>
</node>
<node id="667927886" lat="49.4909673" lon="8.4764904">
<tag k="addr:city" v="Mannheim"/>
<tag k="addr:country" v="DE"/>
<tag k="addr:housenumber" v="5"/>
<tag k="addr:postcode" v="68161"/>
<tag k="addr:street" v="Collinistraße"/>
<tag k="amenity" v="restaurant"/>
<tag k="name" v="Churrascaria Brasil Tropical"/>
<tag k="phone" v="+496211225596"/>
<tag k="wheelchair" v="limited"/>
</node>
<node id="689928440" lat="49.4798794" lon="8.4853418">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="greek"/>
<tag k="email" v="epirus70@hotmail.de"/>
<tag k="fax" v="0621/4407 762"/>
<tag k="name" v="Epirus"/>
<tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/>
<tag k="phone" v="0621/4407 761"/>
<tag k="smoking" v="separated"/>
<tag k="website" v="http://ift.tt/1zI0EmD"/>
<tag k="wheelchair" v="no"/>
</node>
<node id="689928445" lat="49.4799409" lon="8.4851357">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="italian"/>
<tag k="email" v="gianlucascurti@ristorante-augusta.de"/>
<tag k="name" v="Ristorante Augusta"/>
<tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/>
<tag k="phone" v="0621 449872"/>
<tag k="website" v="ristorante-augusta.com/"/>
<tag k="wheelchair" v="no"/>
</node>
</data>
XML;
$fields = array('id','name','lat','lon');
$xml = simplexml_load_string($xmlstr);
//
// PROCESS XML RECORDS
//
$poisdata = array();
$tagdata = array();
foreach ($xml->node as $node) {
$nodedata = array_fill_keys($fields,'');
$nodedata['id'] = intval($node['id']);
$nodedata['lat'] = isset($node['lat']) ? floatval($node['lat']) : 0;
$nodedata['lon'] = isset($node['lon']) ? floatval($node['lon']) : 0;
$poisdata[] = vsprintf("(%d, %10.7f, %10.7f)", $nodedata);
foreach ($node->tag as $tag) {
$k = (string)$tag['k'];
$v = (string)$tag['v'];
$tagdata[] = sprintf("(%d, '%s', '%s')"
, $nodedata['id']
, $db->real_escape_string($k)
, $db->real_escape_string($v));
}
}
//
// STORE THE DATA
//
$sql = "REPLACE INTO pois ('id','lat','lon') VALUES\n" . join(",\n", $poisdata);
$db->query($sql);
$sql = "REPLACE INTO pois_tag (poisid, tagname, tagvalue) VALUES\n" . join(",\n", $tagdata);
$db->query($sql);
//
// DISPLAY THE DATA
//
$currentTags = array();
$sql = "SELECT DISTINCT tagname
FROM pois_tag
ORDER BY tagname = 'name' DESC, tagname";
$res = $db->query($sql);
while (list($tn) = $res->fetch_row()) {
$currentTags[] = $tn;
}
$thead = "<tr><th>id</th><th>lat</th><th>lon</th><th>"
. join('</th><th>', $currentTags) . "</th></tr>\n";
$currid = $currlat = $currlon = 0;
$sql = "SELECT p.id, lat, lon, tagname, tagvalue
FROM pois p
LEFT JOIN pois_tag t ON t.poisid = p.id
ORDER BY p.id";
$res = $db->query($sql);
$tdata = '';
while (list($id, $lat, $lon, $t, $v) = $res->fetch_row()) {
if ($currid != $id) {
if ($currid) {
$tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>"
. join('</td><td>', $poisrow) . "</td></tr>\n";
}
$currid = $id;
$currlat = $lat;
$currlon = $lon;
$poisrow = array_fill_keys($currentTags,'');
}
$poisrow[$t] = $v;
}
$tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>"
. join('</td><td>', $poisrow) . "</td></tr>\n";
?>
<html>
<head>
<meta name="generator" content="PhpED 12.0 (Build 12010, 64bit)">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Tags</title>
<meta name="author" content="Barand">
<meta name="creation-date" content="06/04/2014">
<style type="text/css">
body, td, th {
font-family: arial, sans-serif;
font-size: 10pt;
}
table {
border-collapse: collapse;
}
th {
background-color: #369;
color: white;
padding: 5px 2px;
}
td {
background-color: #EEE;
padding: 2px;
}
</style>
</head>
<body>
<table border='1'>
<?php echo $thead, $tdata; ?>
</table>
</body>
</html>
get back following errors
PHP Fatal error: Call to a member function fetch_row() on a non-object in /home/martin/php/osm_200.php on line 121
martin@linux-70ce:~/php> php osm_200.php
PHP Notice: Use of undefined constant localhost - assumed 'localhost' in /home/martin/php/osm_200.php on line 3
PHP Notice: Use of undefined constant root - assumed 'root' in /home/martin/php/osm_200.php on line 3
PHP Notice: Use of undefined constant rimbaud - assumed 'rimbaud' in /home/martin/php/osm_200.php on line 3
PHP Fatal error: Call to a member function fetch_row() on a non-object in /home/martin/php/osm_200.php on line 121
martin@linux-70ce:~/php>
No comments:
Post a Comment