Jump to content

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


dil_bert

Recommended Posts



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"
  }
},
Link to comment
Share on other sites

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.

×
×
  • 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.