making a csv file of a data set

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

making a csv file of a data set

wouter van der plas
hello everyone,

I'm working on a game and need a list with cities and the population for that. 
so i downloaded a planet file and filtered for "place=city" and "place = town and population < 10000".
this gave me a nice list of places but the problem is that in some cities there isn't a population or it is tagged differently.
since i'd like to contribute further to the osm data set i was thinking about filling the hols in the data with wiki data or other things.

and therefor i'd like a csv file with all the tags so i can import them to programs and find the data i need.

so what i'm asking: is there a way to make a csv file with all the tags. 
i am already familiar with with the csv function in osmconvert but this tool needs you to know the tags before you run the program.

i'm looking forward to an answer.

Best regards,

wouter van der plas



 

_______________________________________________
newbies mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/newbies
Reply | Threaded
Open this post in threaded view
|

Re: making a csv file of a data set

sabas88



2014-03-22 1:34 GMT+01:00 wouter van der plas <[hidden email]>:
hello everyone,

I'm working on a game and need a list with cities and the population for that. 
so i downloaded a planet file and filtered for "place=city" and "place = town and population < 10000".
this gave me a nice list of places but the problem is that in some cities there isn't a population or it is tagged differently.
since i'd like to contribute further to the osm data set i was thinking about filling the hols in the data with wiki data or other things.

and therefor i'd like a csv file with all the tags so i can import them to programs and find the data i need.

so what i'm asking: is there a way to make a csv file with all the tags. 
i am already familiar with with the csv function in osmconvert but this tool needs you to know the tags before you run the program.

i'm looking forward to an answer.

Hi,
I made a script to do it from geojson some time ago to answer a friend's question. It's neither pretty nor fast (bruteforce the thing :D), but did the job.
To get the geojson from osm file, open it in josm and save as geojson (if you have osm file).

Text is in italian but it's pretty straightforward (upload from first button, execute from Carica, download the result from Salva), I'll translate if now if I can..
 

Best regards,

wouter van der plas

Regards,
Stefano 


 

_______________________________________________
newbies mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/newbies



_______________________________________________
newbies mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/newbies
Reply | Threaded
Open this post in threaded view
|

Re: making a csv file of a data set

Hermann Peifer
In reply to this post by wouter van der plas
On 2014-03-22 1:34, wouter van der plas wrote:
>
> and therefor i'd like a csv file with all the tags so i can import them
> to programs and find the data i need.
>
> so what i'm asking: is there a way to make a csv file with all the tags.
> i am already familiar with with the csv function in osmconvert but this
> tool needs you to know the tags before you run the program.
>

$ ogr2ogr -f csv /dev/stdout liechtenstein-latest.osm.pbf points | head

osm_id,place,all_tags
26860698,,"""name""=>""Mittagspitze"",""tourism""=>""camp_site"""
26863444,,"""name""=>""Kuhgrat"",""natural""=>""peak"""
32011241,suburb,"""name""=>""Oberplanken"",""place""=>""suburb"""
32020993,,"""railway""=>""level_crossing"""
49872384,,"""highway""=>""crossing"""
49872651,,"""information""=>""guidepost"",""name""=>""Schloss
Vaduz"",""tourism""=>""information"""
49939577,,"""highway""=>""crossing"""
49939622,,"""highway""=>""crossing"""
50049362,,"""highway""=>""crossing"""
50069907,,"""amenity""=>""parking"""
(...)

ogr2ogr is a command line tool that converts between various vector file
formats, see [0]. For the field definitions of the above example: see [1].

I am not quite sure in how far ogr2ogr can attack the whole planet.

Hermann


[0] http://www.gdal.org/ogr_utilities.html

[1]

[points]
# common attributes
osm_id=yes

# keys to report as OGR fields
attributes=place

# keys that, alone, are not significant enough to report a node as a OGR
point
unsignificant=created_by,converted_by,source,time,ele,attribution

# keys that should NOT be reported in the "other_tags" field
ignore=created_by,converted_by,source,time,ele,note,openGeoDB:,fixme,FIXME

# uncomment to avoid creation of "other_tags" field
#other_tags=no

# uncomment to create "all_tags" field. "all_tags" and "other_tags" are
exclusive
all_tags=yes


_______________________________________________
newbies mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/newbies
Reply | Threaded
Open this post in threaded view
|

Re: making a csv file of a data set

wouter van der plas
thank you both for your answers.
i have a nice csv file full of cites.
the only problem is finding the population.
but i will figure that out on my own.

best regards,

wouter van der plas


2014-03-22 17:46 GMT+01:00 Hermann Peifer <[hidden email]>:
On 2014-03-22 1:34, wouter van der plas wrote:

and therefor i'd like a csv file with all the tags so i can import them
to programs and find the data i need.

so what i'm asking: is there a way to make a csv file with all the tags.
i am already familiar with with the csv function in osmconvert but this
tool needs you to know the tags before you run the program.


$ ogr2ogr -f csv /dev/stdout liechtenstein-latest.osm.pbf points | head

osm_id,place,all_tags
26860698,,"""name""=>""Mittagspitze"",""tourism""=>""camp_site"""
26863444,,"""name""=>""Kuhgrat"",""natural""=>""peak"""
32011241,suburb,"""name""=>""Oberplanken"",""place""=>""suburb"""
32020993,,"""railway""=>""level_crossing"""
49872384,,"""highway""=>""crossing"""
49872651,,"""information""=>""guidepost"",""name""=>""Schloss Vaduz"",""tourism""=>""information"""
49939577,,"""highway""=>""crossing"""
49939622,,"""highway""=>""crossing"""
50049362,,"""highway""=>""crossing"""
50069907,,"""amenity""=>""parking"""
(...)

ogr2ogr is a command line tool that converts between various vector file formats, see [0]. For the field definitions of the above example: see [1].

I am not quite sure in how far ogr2ogr can attack the whole planet.

Hermann


[0] http://www.gdal.org/ogr_utilities.html

[1]

[points]
# common attributes
osm_id=yes

# keys to report as OGR fields
attributes=place

# keys that, alone, are not significant enough to report a node as a OGR point
unsignificant=created_by,converted_by,source,time,ele,attribution

# keys that should NOT be reported in the "other_tags" field
ignore=created_by,converted_by,source,time,ele,note,openGeoDB:,fixme,FIXME

# uncomment to avoid creation of "other_tags" field
#other_tags=no

# uncomment to create "all_tags" field. "all_tags" and "other_tags" are exclusive
all_tags=yes



_______________________________________________
newbies mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/newbies


_______________________________________________
newbies mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/newbies
Reply | Threaded
Open this post in threaded view
|

Re: making a csv file of a data set

Hermann Peifer
Wouter,

Maybe I wasn't clear enough, but what I indicated was a csv extraction
of nodes (in OGR kown as: points) plus all the key/value pairs of the
tags, as you indicated that you wanted "all the tags".

But actually the only meaningful tag for population values is the ...
(surprise!) ...  population tag, see
http://taginfo.osm.org/keys/population#overview

So you'd just have to filter the input file in order to get all nodes
and relations with a population tag, and the result for the planet
should be some 278000 nodes and 34000 relations with a population value,
as indicated on the taginfo website.

I'd do something like this (taking Brazil as an example OSM data file):

osmosis -q --rb brazil-latest.osm.pbf \
     --tf accept-nodes population=* \
     --tf reject-ways \
     --tf accept-relations population=* \
     --used-node \
     --buffer bufferCapacity=6000
     --wx - |
xmlstarlet sel -T -t -m "/osm/node|/osm/relation" \
   -v "tag[@k='name']/@v" -o ";" \
   -v "tag[@k='population']/@v" -n

Sample output below, sorted by descending population value.

Hope this helps, Hermann

Argentina;40677348
São Paulo;11821876
Rio de Janeiro;6429923
Uruguay;3286314
Salvador;2883672
Brasília;2789761
Fortaleza;2551805
Belo Horizonte;2479175
Manaus;1982179
Curitiba;1848943
Recife;1599514
Porto Alegre;1467823
Belém;1425923
Goiânia;1393579
Guarulhos;1299249
Campinas;1144862
São Luís;1053919
São Gonçalo;1025507
Maceió;996736
Loreto;891732
Duque de Caxias;873921
Natal;853929
Teresina;836474
Campo Grande;832350
Londrina;800566
João Pessoa;769604
Jaboatão dos Guararapes;675599
Uberlândia;646673
Contagem;637961
Aracaju;614577


On 2014-03-23 11:48, wouter van der plas wrote:

> thank you both for your answers.
> i have a nice csv file full of cites.
> the only problem is finding the population.
> but i will figure that out on my own.
>
> best regards,
>
> wouter van der plas
>
>
> 2014-03-22 17:46 GMT+01:00 Hermann Peifer
> <[hidden email]
> <mailto:[hidden email]>>:
>
>     On 2014-03-22 1:34, wouter van der plas wrote:
>
>
>         and therefor i'd like a csv file with all the tags so i can
>         import them
>         to programs and find the data i need.
>
>         so what i'm asking: is there a way to make a csv file with all
>         the tags.
>         i am already familiar with with the csv function in osmconvert
>         but this
>         tool needs you to know the tags before you run the program.
>
>
>     $ ogr2ogr -f csv /dev/stdout liechtenstein-latest.osm.pbf points | head
>
>     osm_id,place,all_tags
>     26860698,,"""name""=>""__Mittagspitze"",""tourism""=>""__camp_site"""
>     26863444,,"""name""=>""__Kuhgrat"",""natural""=>""peak"__""
>     32011241,suburb,"""name""=>""__Oberplanken"",""place""=>""__suburb"""
>     32020993,,"""railway""=>""__level_crossing"""
>     49872384,,"""highway""=>""__crossing"""
>     49872651,,"""information""=>""__guidepost"",""name""=>""__Schloss
>     Vaduz"",""tourism""=>""__information"""
>     49939577,,"""highway""=>""__crossing"""
>     49939622,,"""highway""=>""__crossing"""
>     50049362,,"""highway""=>""__crossing"""
>     50069907,,"""amenity""=>""__parking"""
>     (...)
>
>     ogr2ogr is a command line tool that converts between various vector
>     file formats, see [0]. For the field definitions of the above
>     example: see [1].
>
>     I am not quite sure in how far ogr2ogr can attack the whole planet.
>
>     Hermann
>
>
>     [0] http://www.gdal.org/ogr___utilities.html
>     <http://www.gdal.org/ogr_utilities.html>
>
>     [1]
>
>     [points]
>     # common attributes
>     osm_id=yes
>
>     # keys to report as OGR fields
>     attributes=place
>
>     # keys that, alone, are not significant enough to report a node as a
>     OGR point
>     unsignificant=created_by,__converted_by,source,time,ele,__attribution
>
>     # keys that should NOT be reported in the "other_tags" field
>     ignore=created_by,converted___by,source,time,ele,note,__openGeoDB:,fixme,FIXME
>
>     # uncomment to avoid creation of "other_tags" field
>     #other_tags=no
>
>     # uncomment to create "all_tags" field. "all_tags" and "other_tags"
>     are exclusive
>     all_tags=yes
>
>
>
>     _________________________________________________
>     newbies mailing list
>     [hidden email]
>     <mailto:[hidden email]>
>     https://lists.openstreetmap.__org/listinfo/newbies
>     <https://lists.openstreetmap.org/listinfo/newbies>
>
>
>
>



_______________________________________________
newbies mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/newbies
Reply | Threaded
Open this post in threaded view
|

Re: making a csv file of a data set

wouter van der plas
i know how to get the population of everyting. what i would like to have is a way so i can add the population to cities were it is missing.

for example in china there are a lot of cities but none have a population or a wiki tag were i could find the population




2014-03-23 12:47 GMT+01:00 Hermann Peifer <[hidden email]>:
Wouter,

Maybe I wasn't clear enough, but what I indicated was a csv extraction of nodes (in OGR kown as: points) plus all the key/value pairs of the tags, as you indicated that you wanted "all the tags".

But actually the only meaningful tag for population values is the ... (surprise!) ...  population tag, see http://taginfo.osm.org/keys/population#overview

So you'd just have to filter the input file in order to get all nodes and relations with a population tag, and the result for the planet should be some 278000 nodes and 34000 relations with a population value, as indicated on the taginfo website.

I'd do something like this (taking Brazil as an example OSM data file):

osmosis -q --rb brazil-latest.osm.pbf \
    --tf accept-nodes population=* \
    --tf reject-ways \
    --tf accept-relations population=* \
    --used-node \
    --buffer bufferCapacity=6000
    --wx - |
xmlstarlet sel -T -t -m "/osm/node|/osm/relation" \
  -v "tag[@k='name']/@v" -o ";" \
  -v "tag[@k='population']/@v" -n

Sample output below, sorted by descending population value.

Hope this helps, Hermann

Argentina;40677348
São Paulo;11821876
Rio de Janeiro;6429923
Uruguay;3286314
Salvador;2883672
Brasília;2789761
Fortaleza;2551805
Belo Horizonte;2479175
Manaus;1982179
Curitiba;1848943
Recife;1599514
Porto Alegre;1467823
Belém;1425923
Goiânia;1393579
Guarulhos;1299249
Campinas;1144862
São Luís;1053919
São Gonçalo;1025507
Maceió;996736
Loreto;891732
Duque de Caxias;873921
Natal;853929
Teresina;836474
Campo Grande;832350
Londrina;800566
João Pessoa;769604
Jaboatão dos Guararapes;675599
Uberlândia;646673
Contagem;637961
Aracaju;614577



On 2014-03-23 11:48, wouter van der plas wrote:
thank you both for your answers.
i have a nice csv file full of cites.
the only problem is finding the population.
but i will figure that out on my own.

best regards,

wouter van der plas


2014-03-22 17:46 GMT+01:00 Hermann Peifer
<[hidden email]
<mailto:[hidden email]>>:


    On 2014-03-22 1:34, wouter van der plas wrote:


        and therefor i'd like a csv file with all the tags so i can
        import them
        to programs and find the data i need.

        so what i'm asking: is there a way to make a csv file with all
        the tags.
        i am already familiar with with the csv function in osmconvert
        but this
        tool needs you to know the tags before you run the program.


    $ ogr2ogr -f csv /dev/stdout liechtenstein-latest.osm.pbf points | head

    osm_id,place,all_tags
    26860698,,"""name""=>""__Mittagspitze"",""tourism""=>""__camp_site"""
    26863444,,"""name""=>""__Kuhgrat"",""natural""=>""peak"__""
    32011241,suburb,"""name""=>""__Oberplanken"",""place""=>""__suburb"""
    32020993,,"""railway""=>""__level_crossing"""
    49872384,,"""highway""=>""__crossing"""
    49872651,,"""information""=>""__guidepost"",""name""=>""__Schloss
    Vaduz"",""tourism""=>""__information"""
    49939577,,"""highway""=>""__crossing"""
    49939622,,"""highway""=>""__crossing"""
    50049362,,"""highway""=>""__crossing"""
    50069907,,"""amenity""=>""__parking"""

    (...)

    ogr2ogr is a command line tool that converts between various vector
    file formats, see [0]. For the field definitions of the above
    example: see [1].

    I am not quite sure in how far ogr2ogr can attack the whole planet.

    Hermann


    [0] http://www.gdal.org/ogr___utilities.html

    <http://www.gdal.org/ogr_utilities.html>

    [1]

    [points]
    # common attributes
    osm_id=yes

    # keys to report as OGR fields
    attributes=place

    # keys that, alone, are not significant enough to report a node as a
    OGR point
    unsignificant=created_by,__converted_by,source,time,ele,__attribution


    # keys that should NOT be reported in the "other_tags" field
    ignore=created_by,converted___by,source,time,ele,note,__openGeoDB:,fixme,FIXME


    # uncomment to avoid creation of "other_tags" field
    #other_tags=no

    # uncomment to create "all_tags" field. "all_tags" and "other_tags"
    are exclusive
    all_tags=yes



    _________________________________________________
    newbies mailing list
    [hidden email]
    <mailto:[hidden email]>
    https://lists.openstreetmap.__org/listinfo/newbies
    <https://lists.openstreetmap.org/listinfo/newbies>







_______________________________________________
newbies mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/newbies


_______________________________________________
newbies mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/newbies
Reply | Threaded
Open this post in threaded view
|

Re: making a csv file of a data set

Richard Weait
On Sun, Mar 23, 2014 at 6:38 PM, wouter van der plas
<[hidden email]> wrote:
> i know how to get the population of everyting. what i would like to have is
> a way so i can add the population to cities were it is missing.

[ ... ]

I don't want to inhibit your enthusiasm for improving OpenStreetMap
but I will offer you this caution.  Mechanical edits, that i, bulk
edits without careful, deliberate, human inspection of each aspect,
are frowned upon in OpenStreetMap because of previous contentious
mechanical edits.  Be sure to read and understand the imports policy,
the mechanical edits policy and to follow all of the requirements in
both before heading down the path of a mechanical edit.

Happy Mapping!

_______________________________________________
newbies mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/newbies
Reply | Threaded
Open this post in threaded view
|

Re: making a csv file of a data set

Hermann Peifer
In reply to this post by wouter van der plas
On 2014-03-23 23:38, wouter van der plas wrote:
> i know how to get the population of everyting. what i would like to have
> is a way so i can add the population to cities were it is missing.
>
> for example in china there are a lot of cities but none have a
> population or a wiki tag were i could find the population
>

Hmm. I must have completely misread you mail. Even twice. Sorry. Maybe
because I did not expect a bulk-import question on the newbies list.
There is actually a dedicated list for imports:
https://lists.openstreetmap.org/pipermail/imports/. Maybe you want to
ask there what people think about your idea. Mechanical edits are not
that popular, as stated in another reply.

Hermann



_______________________________________________
newbies mailing list
[hidden email]
https://lists.openstreetmap.org/listinfo/newbies