to work with db-fields: hardcoded or not - in order to be flexible

Maze · 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-experts

well 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 columns

see 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 that
it 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

Ch0cu3r · 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.

Sign In

to work with db-fields: hardcoded or not - in order to be flexible

Recommended Posts

Maze

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

Browse

Activity

Important Information