How to run an OSM Server on Windows

General talk
1

Ist it possible to run an MySQL Server with OSM on Windows? If ist ist possible, how can I create the database shema?

2

Haven’t seen anyone do it, doesn’t mean it can’t be done. What do you wan to do?

3

I have to implement a geographical search, which depends on OSM. I installed ruby and rails and some other things, which are needed. And now I get following errors:

** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:migrate
== 1 CreateOsmDb: migrating ===================================================
– innodb_table()
→ 0.0000s
– create_table(“current_nodes”, {:options=>“ENGINE=InnoDB”, :id=>false, :force=

true})
→ 0.1720s
– add_index(“current_nodes”, [“id”], {:name=>“current_nodes_id_idx”})
→ 0.2970s
– add_index(“current_nodes”, [“latitude”, “longitude”], {:name=>“current_nodes_
lat_lon_idx”})
→ 0.2960s
– add_index(“current_nodes”, [“timestamp”], {:name=>“current_nodes_timestamp_id
x”})
→ 0.3750s
– change_column(“current_nodes”, “id”, :bigint, {:options=>“AUTO_INCREMENT”, :n
ull=>false, :limit=>64})
rake aborted!
NULL pointer given
(eval):3:in each_hash' (eval):3:in all_hashes’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_a
dapters/mysql_adapter.rb:482:in select' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_a dapters/abstract/database_statements.rb:7:in select_all_without_query_cache’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_a
dapters/abstract/query_cache.rb:55:in select_all' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_a dapters/abstract/database_statements.rb:13:in select_one’
./lib/migrate.rb:42:in change_column' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb :285:in send’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb
:285:in method_missing' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb :265:in say_with_time’
c:/ruby/lib/ruby/1.8/benchmark.rb:293:in measure' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb :265:in say_with_time’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb
:281:in method_missing' ./db/migrate//001_create_osm_db.rb:19:in up_without_benchmarks’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb
:219:in send' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb :219:in migrate’
c:/ruby/lib/ruby/1.8/benchmark.rb:293:in measure' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb :219:in migrate’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb
:348:in migrate' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb :339:in each’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb
:339:in migrate' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb :307:in up’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb
:298:in migrate' c:/ruby/lib/ruby/gems/1.8/gems/rails-2.0.2/lib/tasks/databases.rake:85 c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:617:in call’
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:617:in execute' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:612:in each’
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:612:in execute' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:578:in invoke_with_call_c
hain’
c:/ruby/lib/ruby/1.8/monitor.rb:242:in synchronize' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:571:in invoke_with_call_c
hain’
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:564:in invoke' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:2027:in invoke_task’
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:2005:in top_level' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:2005:in each’
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:2005:in top_level' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:2044:in standard_exceptio
n_handling’
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:1999:in top_level' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:1977:in run’
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:2044:in standard_exceptio n_handling' c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/lib/rake.rb:1974:in run’
c:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.4/bin/rake:31
c:/ruby/bin/rake:16:in `load’
c:/ruby/bin/rake:16

Do you have any idea?

4

It could be easier to do what you want by importing OSM data into PostGIS with osm2pgsql and to continue from there with programs like Geoserver or Mapserver. But perhaps you prefer to do it in a proper OSM way.

5

Exactly what is it you need from OSM?

6

I need geo coordinates of:
towns,
streets,
postal codes,
regions,
phone number prefixes
and car licence numbers.

Also I need the population of towns.

7

This data isn’t complete, you can’t get reliable or complete data for regions, population, postal codes, phone number prefixes from openstreetmap. What you can get is streetnames and town names, use osm2pgsql + postgresql for that as JRA says.

It’s quite easy to do.

8

To migrate data to a postgre database is no problem. But I saw that in the xml export of OSM are tags with opengeodb, which includes phone prefixes, postal codes and license plate codes. The problem is, that this information is lost after a postgre migration.

It is important for me, that I can search not only for towns and streets. But it is not very important, that the data is complete.

9

osm2pgsql uses the file default.style (or another one you point it to) to determine what data in which columns to transfer to postgres. You can add the tag values that you want exported to that file.

10

It is not that easy for us using Windows. Osm2pgsql.exe made by Artem Pavlenko imports only fixed set of tags. Jon Burgess made another one that could be configured and it used to be at http://tile.openstreetmap.org/direct/osm2pgsql.zip but it has disappeared.

OK, Jon Burgess told that it is here nowadays: http://tile.openstreetmap.org/osm2pgsql.zip

11

Thanks for all your help. But now I have the problem, that the tag names, which I have defined in default.style are too long. The length of column names in the postgre database are restricted. I added the following lines in default.style:

node population text
node openGeoDB:postal_codes text
node openGeoDB:telephone_area_code text
node openGeoDB:type text
node openGeoDB:license_plate_code text

And I get following errors:

Using projection SRS 900913 (Spherical Mercator)
Unknown flag ‘text’ line 75, ignored
Unknown flag ‘text’ line 76, ignored
Unknown flag ‘text’ line 77, ignored
Setting up table: planet_osm_point
CREATE TABLE planet_osm_point ( osm_id int4,“access” text,“admin_level” text,“ae
roway” text,“amenity” text,“area” text,“bicycle” text,“bridge” text,“boundary” t
ext,“building” text,“cutting” text,“disused” text,“embankment” text,“foot” text,
“highway” text,“horse” text,“junction” text,“landuse” text,“layer” text,“learnin
g” text,“leisure” text,“man_made” text,“military” text,“motorcar” text,“name” te
xt,“natural” text,“oneway” text,“poi” text,“power” text,“power_source” text,“pla
ce” text,“railway” text,“ref” text,“religion” text,“residence” text,“route” text
,“sport” text,“tourism” text,“tunnel” text,“waterway” text,“width” text,“wood” t
ext,“population” text,“openGeoDB:postal_codes” text,“openGeoDB:telephone_are” a_
code,“openGeoDB:type” text,“openGeoDB:license_plate” _code,“z_order” int4 );
failed: ERROR: type “a_code” does not exist
LINE 1: …eoDB:postal_codes" text,“openGeoDB:telephone_are” a_code,"op…
^

Error occurred, cleaning up # own tag, which is needed

Ist it possible to edit the column name length in postgre?

12

Hi,

I think it would be better to use some shorter column name in PostGIS, it would make writing queries easier as well. But I fear it would require some changes to osm2pgsql code. Subscribe in OSM talk mailing list at http://lists.openstreetmap.org/listinfo/talk and send your question there, it is better followed than this forum.

13

Turn the problem the other way round, change the osm file

Search & replace openGeoDB:telephone_are by telephone_are

14

does reading from standardin work on osm2pgsql?
like this:
gzip -dc planet.osm.gz |sed ‘s/openGeoDB:telephone_are/tlphn/’|osm2pgsql -m

15

Hi,

Looks like one column name gets somehow truncated at the second underscore:
“openGeoDB:telephone_are” a_
code,“openGeoDB:type” text,“openGeoDB:license_plate” _code,“z_order” int4 );

If you don’t have typo there it may be a bug in osm2pgsql. I will write about that to OSM talk list.

PostgreSQL has 63 character limit in column names so that it not the problem in this case.

We got just some more info about the problem:

On Mon, Mar 30, 2009 at 2:08 PM, Jukka Rahkonen wrote:

Hi,

An osm2pgsql user writes on the forum about importing special tags
into PostGIS.
He has defined for example these tags:
node    openGeoDB:telephone_area_code    text node   Â
openGeoDB:license_plate_code      text

Import fails and error message is:
CREATE TABLE planet_osm_point ( osm_id int4,“access” text,
“admin_level” text,

Ok, it’s got nothing to do with the underscores and more to do with the fact that the field names are limited to 23 characters by osm2pgsql. I don’t really know where this restriction comes from, does anyone else have ideas?

It should be easy to fix in output-pgsql.c. Fix the read_style_file() function to increase the length of the buffers.

Have a nice day,

Martijn van Oosterhout

16

Hi emj,

Remember we are running with Windows, piping and standardin works only occasionally for us.

17

Thanks for all the answers. Anyone knows whether the bugs are fixed with the field name length in osm2pgsql?

Have a nice weekend

Martin Ebner

18

All the others seem to run OSM with Linux and it may take long before somebody compiles new Windows executable. You will be going much faster if you just edit the OSM xml file and replace too long values with something shorter. There exists sed for Windows, and some text editors like TextPad can do the job just fine.

19

or just start a virtual machine with linux.