Jump to content

from osm [openstreetmap] to mysql with PHP

Maze

By Maze
in PHP Coding Help

 Share

Recommended Posts

Maze Advanced Member

Maze

Posted
Posted (edited)

Hello dear php-experts - good day.

note; i run linux opensuse 13.1

the final goal is to get stored some xml-files in a mysql-database.
thats what i am looking for. - the xml-files are derived from a osm-request  -  at a OpenSteetpmap-api.

and thats why i am here. Guess that you are good php and mysql-experts

 

see the 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://www.kilim-mannheim.de/"/>
  </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://www.vapiano.de/newsroom/?store=29"/>
    <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://epirus-ma.blogspot.com/"/>
    <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>
 
 

 
 
 
  with the following fields in the db:
 
 

id     lat     lon     name     amenity     opening_hours  phone website wheelchair  .... and perhaps some more... vending
 

 


CREATE DATABASE `db123` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;
USE hans;
 
CREATE TABLE `pois` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `lat` FLOAT(10,7) NOT NULL,
  `lon` FLOAT(10,7) NOT NULL,
  `name` VARCHAR(255) COLLATE utf8_bin NOT NULL,
  `amenity` VARCHAR(255) COLLATE utf8_bin NOT NULL,
  `operator` VARCHAR(255) COLLATE utf8_bin NOT NULL,
  `vending` VARCHAR(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

EDITED BY MODERATOR - DB CREDENTIALS REMOVED

    
    
see the dataset; - which is stored in the file mysql.txt
the dataset - it is gathered from the request on the overpass-api which resides here http://www.overpass-turbo.eu

cf. http://overpass-turbo.eu/?q=PCEtLQpUaGlzIHF1ZXJ5IGxvb2vEiGZvciBub2Rlcywgd2F5xIhhbmQgcmVsYXRpb27EiAp3aXRoIMS0ZSBnaXZlbiBrZXkvdmFsxIsgY29tYmluxKvErW4uCkNoxJFzxLh5b3XEl8SoxLrEriDEpMSmxIZ0xLZoxLhSdcS-YnV0dMWfYWJvxLwhCsSCPgp7e8WAeT1hbcS9xLN5fX3FuHvFhMWGZT3EqHN0YcWbxKR0xoMKPG9zbS1zY3JpcMWkxZp0cMWsPSJ4bWwixbcgIDzFqcWQxqnGqsarxIrEjMSOdHlwxokixJnEm8aoCsawxrA8aGFzLWt2xL_Go8W5xbvGgyIgdseIxobFhcSLx4svxq_GvjxixbF4LcayxI0gxbnHmG94xoPHlMa9xr8vx5x5x5XGscSLx53Gtca3xqPEoHnGvMa-xqrHgMeCx4THhmvHj8eKfceMx44ixbnGh8eSx73HpMe0xqvHoMeax6jHnnvIiMejx6o8x6fHrMepx6XHtciKx67GuMSoxKrErMSux7PHtMe2x4PHhceHyIDFusWBx4vHjcePyIJlx5PHqse1yIjHm8iSyIvIjX3Ihcemx6jGr8iQxq3Ersi3cMabbsWkbcSaxrjFsWTHssi0PMSoY8WbxZfEtsa2xrhkb3duIsmGyLzFjci_yYHGo3PFgGxlxa7JksekyJDGlcaXxpnGm8adPg&c=BNJBWRBb1P


you see a request on the left part of the screen

note: to get the output - just press the button in the top-menu called "Ausführen"

after this you press the button called "DATEN" on the top-right -
just below the green button called "flatter this": after pressing this "DATEN"-button you see the data in the right window of the screen.

note - it has got various ids - that means that the osm-file does not give back constantly all the tags...

the last question; does this make any problems to our project - does this has any influence on our db-connection...!?!?

see the output here:
 

 
<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://www.kilim-mannheim.de/"/>
  </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://www.vapiano.de/newsroom/?store=29"/>
    <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://epirus-ma.blogspot.com/"/>
    <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>


well you see that i have some questions

the second one is regarding the variations in the mysql.txt - file - i.e. the different number of tags.; How to make the script robust so that it is able to work with this - and does not stopt to work....!?


I look forward to hear from you

many many greetings

    
    
    
 

Edited by Ch0cu3r
Barand Prolific Member

Barand

Posted
Posted

Do you mean like this?

$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://www.kilim-mannheim.de/"/>
  </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://www.vapiano.de/newsroom/?store=29"/>
    <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://epirus-ma.blogspot.com/"/>
    <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','lat','lon','name','amenity','operator','vending');
$xml = simplexml_load_string($xmlstr);

$dbdata = array();
foreach ($xml->node as $node) {
    $nodedata = array_fill_keys($fields,'');
    $nodedata['id']  = $node['id'];
    $nodedata['lat'] = isset($node['lat']) ? $node['lat'] : 0;
    $nodedata['lon'] = isset($node['lon']) ? $node['lon'] : 0;
    foreach ($node->tag as $tag) {
        $k = (string)$tag['k'];
        $v = (string)$tag['v'];
        if (isset($nodedata[$k])) {
            $nodedata[$k] = $v;
        }
    }
    $dbdata[] = vsprintf("(%d, %10.7f, %10.7f, '%s', '%s', '%s', '%s')", $nodedata);
}

$fieldlist = join(',', $fields);
$sql = "REPLACE INTO pois ($fieldlist) VALUES\n" . join(",\n", $dbdata);

echo "<pre>$sql</pre>";
$db->query($sql);
Maze Advanced Member

Maze

Posted
  • Author
Posted (edited)

hello dear guru

 

many many thanks - guess that this will work fine ....

 

well to create the db is no problem at all - but to put (port over the xml) seems to be a bit tricky.


i have managed to get the easier part -

eg the following_

create a db-connect:

 

?php
mysql_connect("mydb","user","passwd") or die ("no connecting possible");;
mysql_select_db("name of the db")  or die ("the db does not exist.");;
mysql_query("SET NAMES 'utf8'");
?>

last line is necessary for ensuring that the
UTF8-coded data are transferred correctly


and subsequently another part - see the file for the request

from db_to_csv.php

 

<?php
 
$bbx = $_GET["bbox"] ;
 
$array = explode(",",$bbx);
 
$ble = $array[0] ;
$bbo = $array[1] ;
$bri = $array[2] ;
$bto = $array[3] ;
 
include("dbconnect.php");
$ergebnis = mysql_query("SELECT lat, lon, name, amenity, operator, vending FROM pois  
WHERE vending LIKE 'excrement_bags'
AND lat BETWEEN $bbo AND $bto
AND lon BETWEEN $ble AND $bri")
OR die("Error: $abfrage <br>".mysql_error()
);
 
$header = "point\ttitle\tdescription\ticon\n" ;
 
echo $header ;
 
while($row = mysql_fetch_object($ergebnis))
{
$daten = $row->lat.",".$row->lon."\t".$row->name."\t"."amenity=".$row->amenity."<br>vending=".$row->vending."<br>operator=".$row->operator."\t"."http://www.openlayers.org/dev/img/marker.png\n" ;
echo $daten ;
    }
 
?>

 but i am currently not able to get a nice and propper way to put the xml-data into the database-

but i am very very happy - you did manage this part!

 

note - i need a robust one - while the xml-data sometimes contain

a. lots of tags and sometimes
b. not so much....


cf. head over to a see this link to the overpass-api-request - just click and see 




you see a request on the left part of the screen

note: to get the output - just press the button in the top-menu called "Ausführen"

after this you press the button called "DATEN" on the top-right -
just below the green button called "flatter this": after pressing this "DATEN"-button you see the data in the right window of the screen.

note - it has got various ids - that means that the osm-file does not give back constantly all the tags...

the last question; does this make any problems to our project - does this has any influence on our db-connection...!?!?

see the output here:

 

 
<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://www.kilim-mannheim.de/"/>
  </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://www.vapiano.de/newsroom/?store=29"/>
    <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://epirus-ma.blogspot.com/"/>
    <tag k="wheelchair" v="no"/>
  </node>


well i hope i was able to show you what i have -  and where i need a helpin hand



love to hear from you
yours metabo

ps i will dig deeper later the weekend -and then i show you what i also  have tried.
finally:  well i hope i was able to show you what i have -  and where i need a helpin hand

pps update:  .

after re-reading the thread i think i have to make clear what i want to do: well - some final ideas are the followings - that reflect what is aimed - but i guess you allready understand me very well. i see  that in your above mentioned code see the PHP-code - note: i am pretty new to PHP.

one question: so - in other words. if i need more fields in the database - for the POI (that is the dataset that i get from the planet-file) then i extend the above mentioned code-line!? is this correct!? Can i do so!? i guess

btw - all the ideas of getting the data out of the planet file are derived from two sites that inspired me.


A self-updating OpenStreetMap database of US bridges – a step-by-step guide. | oegeo


note;: this guy looks for the bridges in the US

There are about 125,000 of them – for now loosely defined as ‘ways that have the ‘bridge’ tag‘.
So on the scale of OpenStreetMap data it’s a really small subset. In terms of the tools and processes needed, the task seems easy enough, and as long as you are satisfied with a one-off solution, it really is. You would need only four things:

A planet file
A boundary polygon for the United States
A PostGIS database loaded with the osmosis snapshot schema and the linestring extension  osmosis, the OpenStreetMap ETL swiss army tool.

That, and a single well-placed osmosis command:... [...] end of cit.. source
A self-updating OpenStreetMap database of US bridges – a step-by-step guide. | oegeo

summary - simmilarly to the guy who wanted to create a map for the bridges in the us i want to fetch data from the openstreetmap-planet-file and store some POIs to a mysql-database

thats all.... note  at the beginning of my work i am focused on german files ranging form 10 mb to 390 mb

Edited by Maze
Barand Prolific Member

Barand

Posted
Posted

Instead of hard-coding the required column names as in

$fields = array('id','lat','lon','name','amenity','operator','vending');

you could use this to pick up any additional columns added to your pois  table

$sql = "SHOW COLUMNS FROM pois";
$fields = array();
$res = $db->query($sql);
while ($row = $res->fetch_row()) {
    $fields[] = $row[0];
}
Maze Advanced Member

Maze

Posted
  • Author
Posted (edited)

hello der Barand

 

many many thanks for the hints - great!

 

well your mentioned code does refer to the process of

 

a. getting the the data from the database - or

b. coming up with new data - derived from the  request of  the planet file - that

 

- in other words: different xml results should lead to the process of filling the db with all that comes along...

... and subesquently there shuld be added new columns if they are IN The xml file

 

 

 

if i got you right - your code snippet is meant for the - db-request and not for the

 

a. creation of the db or

b. the filling the db with INSERT or REPLACE - Statements...

 

 

cf  - see the creation of the db - ...

CREATE DATABASE `db123` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;
USE hans;
 
CREATE TABLE `pois` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`lat` FLOAT(10,7) NOT NULL,
`lon` FLOAT(10,7) NOT NULL,
`name` VARCHAR(255) COLLATE utf8_bin NOT NULL,
`amenity` VARCHAR(255) COLLATE utf8_bin NOT NULL,
`operator` VARCHAR(255) COLLATE utf8_bin NOT NULL,
`vending` VARCHAR(255) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

 

 

Instead of hard-coding the required column names as in

$fields = array('id','lat','lon','name','amenity','operator','vending');

you could use this to pick up any additional columns added to your pois  table

$sql = "SHOW COLUMNS FROM pois";
$fields = array();
$res = $db->query($sql);
while ($row = $res->fetch_row()) {
    $fields[] = $row[0];
}

 

 

that loooks amazing

 

shuld this be used -at the

 

- db request to read out the db or - to

- make a robust creation of the db.

 

 

hope i was able to make clear what i actually do not understand . -

just to avoid any misunderstanding,...

 

 

love to hear from you

many many greetings

Edited by Maze
gizmola Prolific Member

gizmola

Posted
Posted

Relational databases have fixed schemas.  This is one of the limitations of working with them that you have to live with.  

 

If I understand your question, you want a schema that can adjust to a variable number of tags and adapt to changes in the underlying data.

 

This is not possible with a relational database.  

 

A table will always have the same number of columns in it, until you alter the actual structure of the table.  Having code that attempts to adapt by altering the structure of the table is ill advised in my opinion.  Of course there is no problem in having a table that has many empty fields (so long as the table definition does not require those columns)  and Barand already gave you a bunch of code that addresses your questions.

 

Alternatively, you could normalize the structure, so that tag names were stored in a table, and broke out every piece of data and stored it as a separate tag.  I have done this in the past and the structure works fairly well if you typically need to query for a specific tag, however there's a significant cost to reading the rest of the associated tags.  I'm not going to go into this idea in any detail however.

 

There are a number of popular alternatives to RDBMS's that many people are using these days with php.  One extremely popular alternative is MongoDB.  It has many of the abilities for indexing and searching in documents (equivalent to rows in the rdbms) but is schema- less, so it doesn't have the inherent problem of document variation.  

 

Based on your described problem, I'd highly recommend you consider mongoDB, as it solves many of your problems, and reduces your concerns down to conversion of xml to json so you can store it in mongodb.

 

Also!!!! MongoDB has specialized support for geo data:  http://docs.mongodb.org/manual/applications/geospatial-indexes/

 

This is the reason it has been used by companies like https://foursquare.com used MongoDB instead of a relational database. 

Maze Advanced Member

Maze

Posted
  • Author
Posted

hello dear Gizmola

many thanks for the reply.


 

If I understand your question, you want a schema that can adjust to a variable number of tags and adapt to changes in the underlying data.

 

 

you got it right - absoultly: I want to have a robust database that is able to cope with differend xml (see above)

often the request that is been done with overpass-api or something alik spits out different sets of XML

 

 

 

 

Well - i thougth that Barands solution would fit this need:


$fields = array('id','lat','lon','name','amenity','operator','vending');


you could use this to pick up any additional columns added to your pois  table
 

$sql = "SHOW COLUMNS FROM pois";
$fields = array();
$res = $db->query($sql);
while ($row = $res->fetch_row()) {
    $fields[] = $row[0];
}

well  at the weekend i have more time - then i look at mongodb and will read your ideas more carefully...

 

greetings

matz

Barand Prolific Member

Barand

Posted
Posted (edited)

An alternative structure would be

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

 

Processing would be like this

<?php

$db = new mysqli(HOST,USERNAME,PASSWORD,'test'); // use your 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://www.kilim-mannheim.de/"/>
  </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://www.vapiano.de/newsroom/?store=29"/>
    <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://epirus-ma.blogspot.com/"/>
    <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>

Which would give output like this

post-3105-0-90263000-1401919836_thumb.png

Edited by Barand
  • 2 weeks later...
Maze Advanced Member

Maze

Posted
  • Author
Posted

sure you can help me here tooo...

I am new to PHP's SimpleXML. Below I have added my own answer to the question, how to refine the code to ad more tags.

I want to filter the data to get the nodes with special category. Here is sample of the OSM data I want to get the whole schools within an area. The first script runs well - but now I want to refine the search and add more tags. I want to store all into MySQL.

So we need to make some XML parsing with PHP:

The following is a little OSM Overpass API example with PHP SimpleXM

we need to do this here

<?php
/**
 * OSM Overpass API with PHP SimpleXML / XPath
 *
 * PHP Version: 5.4 - Can be back-ported to 5.3 by using 5.3 Array-Syntax (not PHP 5.4's square brackets)
 */


//
// 1.) Query an OSM Overpass API Endpoint
//

$query = 'node
  ["amenity"~".*"]
  (38.415938460513274,16.06338500976562,39.52205163048525,17.51220703125);
out;';

$context = stream_context_create(['http' => [
    'method'  => 'POST',
    'header' => ['Content-Type: application/x-www-form-urlencoded'],
    'content' => 'data=' . urlencode($query),
]]);

# please do not stress this service, this example is for demonstration purposes only.
$endpoint = 'http://overpass-api.de/api/interpreter';
libxml_set_streams_context($context);
$start = microtime(true);

$result = simplexml_load_file($endpoint);
printf("Query returned %2\$d node(s) and took %1\$.5f seconds.\n\n", microtime(true) - $start, count($result->node));

//
// 2.) Work with the XML Result
//

# get all school nodes with xpath
$xpath = '//node[tag[@k = "amenity" and @v = "school"]]';
$schools = $result->xpath($xpath);
printf("%d School(s) found:\n", count($schools));
foreach ($schools as $index => $school)
{
    # Get the name of the school (if any), again with xpath
    list($name) = $school->xpath('tag[@k = "name"]/@v') + ['(unnamed)'];
    printf("#%02d: ID:%' -10s  [%s,%s]  %s\n", $index, $school['id'], $school['lat'], $school['lon'], $name);
}

?>


i just have had a quick view on the above mentioned site. i try to figure out how to do this - for any and all hints i am thankful


The following code lists all schools and tries to obtain their names as well. I have not covered translations yet because my sample data didn't have those, but you can also look for all kind of names including translations and just prefer a specific one):

Code:

//
// 2.) Work with the XML Result
//

# get all school nodes with xpath
$xpath = '//node[tag[@k = "amenity" and @v = "school"]]';
$schools = $result->xpath($xpath);
printf("%d School(s) found:\n", count($schools));
foreach ($schools as $index => $school)
{
    # Get the name of the school (if any), again with xpath
    list($name) = $school->xpath('tag[@k = "name"]/@v') + ['(unnamed)'];
    printf("#%02d: ID:%' -10s  [%s,%s]  %s\n", $index, $school['id'], $school['lat'], $school['lon'], $name);
}

The key point here are the xpath queries.

In the above mentioend example two are used, the first xpath queriy is to get the nodes that have certain tags.I think this is the most interesting one for me:


//node[tag[@k = "amenity" and @v = "school"]]

This line says: Give us all node elements that have a tag element inside which has the k attribute value "amenity" and the v attribute value "school". This is the condition we have to filter out those nodes that are tagged with amenity school.

Further on xpath is used again, now relative to those school nodes to see if there is a name and if so to fetch it:


tag[@k = "name"]/@v'

This line says: Relative to the current node, give me the v attribute from a tag element that as the k attribute value "name". As you can see, some parts are again similar to the line before. I think you can both adopt them to your needs.

Because not all school nodes have a name, a default string is provided for display purposes by adding it to the (then empty) result array:


list($name) = $school->xpath('tag[@k = "name"]/@v') + ['(unnamed)'];
                                                    ^^^^^^^^^^^^^^^
                                                Provide Default Value


So here my results for that code-example:
Query returned xxxxx node(s) and took 1.10735 seconds.
2179 School(s) found: the last ones are shown below.....

#2151: ID:2688357765 [51.4668941,-0.9731135] New Directrions, North Reading
#2152: ID:2702504696 [51.5884265,-0.7829013] Burford School
#2153: ID:2702549737 [51.5802201,-0.7653918] Great Marlow School
#2154: ID:2706219304 [51.3779317,-0.0895302] ARK Oval Primary Academy
#2155: ID:2706219314 [51.3871935,-0.0623001] Ashburton Primary School
#2156: ID:2706219320 [51.3210977,-0.1398859] CALAT Smitham Centre
#2157: ID:2706219326 [51.3638861,-0.0922032] Elmhurst School
#2158: ID:2706219339 [51.4007121,-0.0743710] Harris Academy South Norwood
#2159: ID:2706219343 [51.3831662,-0.0405476] Orchard Way Primary School
#2160: ID:2706219347 [51.3531047,-0.0959447] Purley Oaks Primary School
#2161: ID:2706219348 [51.3428384,-0.0069931] Rowdown Primary School
#2162: ID:2706219350 [51.3954917,-0.0732185] South Norwood Primary School
#2163: ID:2706219351 [51.3377151,-0.1230482] St David's Preparatory School
#2164: ID:2706219353 [51.3993760,-0.1144352] Winterbourne School
#2165: ID:2717394621 [51.8706538,0.1480886] Prep
#2166: ID:2717394636 [51.8685838,0.1463720] Pre-Prep
#2167: ID:2722704201 [51.1398429,-0.0457445] Felbridge Primary School
#2168: ID:2723815070 [50.8465429,-0.3030261] Lancing College
#2169: ID:2727170814 [51.5780664,-0.0249051] Adult Education Centre
#2170: ID:2833253896 [50.9928140,-0.7774996] (unnamed)
#2171: ID:2837001831 [51.1783749,-0.7970866] More House School
#2172: ID:2865091022 [50.9090614,-0.5565425] Dorset House School
#2173: ID:2882477853 [51.6261198,-0.7349665] Bowerdean Primary School
#2174: ID:2901434856 [51.6542477,-0.3098923] The Haberdashers' Aske's Girls School
#2175: ID:2901434857 [51.6565707,-0.3129822] The Haberdashers' Aske's Boys School
and now i try to figure out how i can enter more xpath queries at the above mentioned code


and get out even more important data - see here Key:contact - OpenStreetMap Wiki

contacthone
contact:fax
contact:website
contact:email
i will digg into all documents and come back later the weekend... and report all the findings


well - i think that i need to extend the xpath requests within the loop where xpath is used again,
now relative to those school nodes to see if there is a name and if so to fetch it:


tag[@k = "name"]/@v'
tag[@k = "    contact:website"]/@v'
tag[@k = "    contact:email"]/@v'

hope you can help me...

look forward to hear from you
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.