Cityname to geographical coordinates PHP script???

Question

Hello guys, i need to develop a php script who ask for a city name and return a simple echo with the geographical coordinates of the city.

Input data: Cityname OR Cityname, Country

I know there is a free database called GeoNames were you can download a database who contains this information, but i really dont know how to export this database to my MySQL server, there 3 files who can be the think i need but i dont know what i need to pick:

cities1000.zip                             11-Jul-2010 01:13  3.4M  
cities15000.zip                            11-Jul-2010 01:13  1.1M  
cities5000.zip                             11-Jul-2010 01:13  1.8M  

see were this files are

So, its a good idea to use this data base?, there is an online API to do this?, how can i import the data from citiesXXXX to MySQL?, any suggestion for the php script?... Thanks!

Answer 1

I use Google Maps to get any information about a location: http://code.google.com/apis/maps/index.html

It can return the data in XML or JSON so you can easily parse and save the data.

Here is an example link for Washington DC: http://maps.google.com/maps/geo?output=xml&oe=utf8&sensor=false&hl=en&q=washington%20dc

It will return this XML data:

    <?xml version="1.0" encoding="UTF-8" ?>
<kml xmlns="http://earth.google.com/kml/2.0"&gt;
    <Response>
        <name>washington dc</name>
        <Status>
            <code>200</code>
            <request>geocode</request>
        </Status>
        <Placemark id="p1">
            <address>Washington, DC, USA</address>
            <AddressDetails Accuracy="4" xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0">
                <Country>
                    <CountryNameCode>US</CountryNameCode>
                    <CountryName>USA</CountryName>
                    <AdministrativeArea>
                        <AdministrativeAreaName>DC</AdministrativeAreaName>
                        <SubAdministrativeArea>
                            <SubAdministrativeAreaName>District of Columbia</SubAdministrativeAreaName>
                            <Locality>
                                <LocalityName>Washington</LocalityName>
                            </Locality>
                        </SubAdministrativeArea>
                    </AdministrativeArea>
                </Country>
            </AddressDetails>
            <ExtendedData>
                <LatLonBox north="38.9645516" south="38.8256040" east="-76.9083064" west="-77.1644252" />
            </ExtendedData>
            <Point>
                <coordinates>-77.0363658,38.8951118,0</coordinates>
            </Point>
        </Placemark>
    </Response>
</kml>

You can then use a function like SimpleXML or DOMDocument to parse the data. If you just want to echo the coordinates as supported in the tag simple use this code:

$xml = simplexml_load_string(file_get_contents('http://maps.google.com/maps/geo?output=json&oe=utf8&sensor=false&hl=de&q='.urlencode($address)));
echo (string)$xml->Placemark->Point->coordinates;

If you want the single coordinates use the data in the tag e.g.:

$coordinates = explode(',', $xml->Placemark->Point->coordinates);
echo $coordinates[0];
echo $coordinates[1];

I hope that is what you are searching for.

Answer 2

See http://www.maxmind.com/app/geoip_resources and there in the bottom "MySQL" sections. They will help.