Maze Posted June 3, 2014 Share Posted June 3, 2014 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-expertswell i play around with some openstreetmap-requests - and want to store the results in a Mysql-db.see the tags: id lat lon name amenity operator vending see the table 'pois' that i want to create - and subsequently create some columnssee the SQL-Script 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; first of all i thought that i can do the db creation with some hard coded methods:but instead of hard-coding the required column names as in $fields = array('id','lat','lon','name','amenity','operator','vending'); we 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]; } btw: 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: 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 thatit is able to work with this - and does not stopt to work....!? well i hope i was able to make clear what i want to achieve: can i use the code snippet to create the db table -we 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]; } note: i need to have a robust way - where i can cope with xmlfiles that are full with additionals tags /(that make it necessary to have more collumns added. I look forward to hear from you many many greetings Link to comment Share on other sites More sharing options...
Ch0cu3r Posted June 3, 2014 Share Posted June 3, 2014 Topic locked. You already have a topic open here http://forums.phpfreaks.com/topic/288907-from-osm-openstreetmap-to-mysql-with-php/?do=findComment&comment=1481537 Please do not create duplicate topics. Link to comment Share on other sites More sharing options...
Recommended Posts