parsing xml - with PHP-simpleXML - a headstart into this technique

[dil_bert]

hello dear php-experts

i run a request on osm - the openstreetmap tool: see here overpass turbo - see the request below

well - though i could use the blob-feature of the mysql-db i also can run some parser

eg the following - perl-things...

1. some of the XML parsers on cpan.
2. XML::Parser - search.cpan.org
3. XML::Twig - search.cpan.org
4. The CPAN Search Site - search.cpan.org

 

well i think it is time for parsing xml - with PHP-simpleXML  - a headstart into this technique

I'd set up a mysql table with fields like street name, house number, Web address.
Things like street name need to be big, but of the type vchar. With things like zip
 code 20 fixed should be big enough for anywhere in the world.
Phone numbers are best stored as strings to allow for chars like ()+

Things like Web address should be a massive vchar or blob cos some addresses are stupidly long.
 

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

we need to be able to pick up any additional columns added to our pois table.



a. see a request: overpass turbo
b. or run it at Overpass API Query Form


but see - we can go like so...:

 

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;

$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];
}

besides this .....

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`)
)

 

[out:json][timeout:250];
// fetch area “chile” to search in
{{geocodeArea:chile}}->.searchArea;
// gather results
(
  // query part for: “amenity=library and website~/./”
  node(area.searchArea)["amenity"="library"]["website"~"."];
  way(area.searchArea)["amenity"="library"]["website"~"."];
  relation(area.searchArea)["amenity"="library"]["website"~"."];
);
// print results
out meta;
>;
out skel qt;

see the  results  that were gathered... note: i want to strip out all the things that are not

 

name of the library

 

town and postal code

street

housnumber

website

e-mail-adress

 

 

is this possible

{
  "version": 0.6,
  "generator": "Overpass API",
  "osm3s": {
    "timestamp_osm_base": "2015-06-29T15:43:02Z",
    "timestamp_areas_base": "2015-06-28T08:36:02Z",
    "copyright": "The data included in this document is from www.openstreetmap.org. The data is made available under ODbL."
  },
  "elements": [

{
  "type": "node",
  "id": 133769438,
  "lat": -33.4465780,
  "lon": -70.6147822,
  "timestamp": "2012-01-08T18:27:49Z",
  "version": 5,
  "changeset": 10334875,
  "user": "dbusse",
  "uid": 149876,
  "tags": {
    "addr:city": "Providencia",
    "addr:country": "CL",
    "addr:housenumber": "1240",
    "addr:street": "Santa Isabel",
    "amenity": "library",
    "email": "cafesantaisabel@providencia.cl",
    "name": "Café Literario Santa Isabel",
    "phone": "+5622098966",
    "website": "http://www.providencia-biblioteca.cl"
  }
},
{
  "type": "node",
  "id": 479370406,
  "lat": -33.4391656,
  "lon": -70.6324397,
  "timestamp": "2011-10-10T01:43:34Z",
  "version": 4,
  "changeset": 9518356,
  "user": "wheelmap_visitor",
  "uid": 290680,
  "tags": {
    "amenity": "library",
    "name": "Café Literario",
    "source": "Providencia.cl",
    "website": "http://www.providencia-biblioteca.cl/Bustamante.htm",
    "wheelchair": "limited"
  }
},
{
  "type": "node",
  "id": 1251193349,
  "lat": -33.4385638,
  "lon": -70.6533250,
  "timestamp": "2014-07-20T00:27:59Z",
  "version": 2,
  "changeset": 24246469,
  "user": "OttoPilot",
  "uid": 449385,
  "tags": {
    "addr:city": "Santiago",
    "addr:housenumber": "1175",
    "addr:street": "Compañia",
    "amenity": "library",
    "name": "Biblioteca del Congreso Nacional",
    "phone": "+5622701700",
    "website": "http://www.bcn.cl"
  }
},
{
  "type": "node",
  "id": 2573915770,
  "lat": -36.6119931,
  "lon": -72.1047096,
  "timestamp": "2013-12-10T02:03:38Z",
  "version": 2,
  "changeset": 19369781,
  "user": "mandrakula",
  "uid": 1836603,
  "tags": {
    "addr:city": "Chillán",
    "addr:housenumber": "974",
    "addr:street": "Arauco",
    "amenity": "library",
    "name": "Biblioteca  Volodia Teitelboim V.",
    "website": "http://www.biblioredes.cl/chillan/arauco"
  }
},
{
  "type": "way",
  "id": 173295662,
  "timestamp": "2014-10-05T12:27:04Z",
  "version": 2,
  "changeset": 25874804,
  "user": "hybridOL",
  "uid": 2344498,
  "nodes": [
    1064401098,
    1064401050,
    1064401078,
    1064401049,
    1064401054,
    1064401107,
    1064401111,
    1064401098
  ],
  "tags": {
    "addr:city": "Santiago",
    "addr:housenumber": "151",
    "addr:street": "Matucana",
    "amenity": "library",
    "building": "yes",
    "name": "Biblioteca de Santiago",
    "website": "http://www.bibliotecasantiago.cl/",
    "wheelchair": "yes",
    "wheelchair:description": "único problema un escalón de la vereda al patio"
  }
},
{
  "type": "way",
  "id": 305640042,
  "timestamp": "2015-02-13T18:55:03Z",
  "version": 2,
  "changeset": 28827795,
  "user": "KDDLB",
  "uid": 578609,
  "nodes": [
    3102949094,
    3102949095,
    3102949096,
    3102949097,
    3102949094
  ],
  "tags": {
    "addr:city": "Antofagasta",
    "addr:housenumber": "2623",
    "addr:street": "Washington",
    "amenity": "library",
    "building": "yes",
    "name": "Biblioteca Regional Antofagasta",
    "operator": "DIBAM",
    "website": "http://www.bibliotecaregionalantofagasta.cl/",
    "wikipedia": "es:Biblioteca Regional de Antofagasta"
  }
},

[sKunKbad]

Are you trying to parse XML with SimpleXML? Yes, you can do it, but whatever it is you're showing us doesn't look like or ask anything about parsing XML.