Loading data into PostGIS 2.0

[djq]

Hi, I've been using the installer on Win 7 and it works great with PostGIS 1.5 - thank you.

However, when using PostGIS 2.0 it does not work and I get the following message:

C:\Program Files (x86)\HOTOSM\bin>osm2pgsql.exe -c -H localhost -d osm -U postgres -P 5434 J:/OSM/massachusetts.osm.bz2 -S J:/OSM/default.style osm2pgsql SVN version 0.69-21289M

Using projection SRS 900913 (Spherical Mercator) Setting up table: planet_osm_point NOTICE: table "planet_osm_point" does not exist, skipping NOTICE: table "planet_osm_point_tmp" does not exist, skipping PREPARE get_way (int4) AS SELECT AsText(way) FROM planet_osm_point WHERE osm_id = $1; failed: ERROR: function astext(geometry) does not exist LINE 1: PREPARE get_way (int4) AS SELECT AsText(way) FROM planet_osm... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Error occurred, cleaning up

I'm wondering if there is a way of incorporating this patch into the windows build? http://trac.openstreetmap.org/changeset/26659

[ronzul]

Shame the google gods didn't point me to this post earlier, being a postgres/postgis/osm2pgsql newbie took ages to make find the cause of this error.

[springmeyer]

Hey there. Yes, more recent osm2pgsql works with PostGIS 2.0, but we need to get a windows build working. I'll see what I can do, but could take several weeks at best.

[djq]

Wonderful! Thanks.

[springmeyer]

update: I've made some progress trying to get osm2pgsql compiled on windows: https://github.com/springmeyer/osm2pgsql. But the app is not stable/usable.

So, I don't have a sense of when or even if I will ever get this working.

I recommend that instead of waiting, you look for workarounds. The best workaround is likely to find the legacy.sql file distributed with PostGIS 2.0. Loading it should allow the older osm2pgsql tool to run without the error above. Then you will hit another error about a missing projection/srid: 900913, because it was accidentally removed: http://trac.osgeo.org/postgis/ticket/1805. To solve that you would need to add that projection using this bit of sql:

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 900913, 'sr-org', 6, '+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext  +no_defs', 'PROJCS["unnamed",GEOGCS["unnamed ellipse",DATUM["unknown",SPHEROID["unnamed",6378137,0]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]],PROJECTION["Mercator_2SP"],PARAMETER["standard_parallel_1",0],PARAMETER["central_meridian",0],PARAMETER["false_easting",0],PARAMETER["false_northing",0],UNIT["Meter",1],EXTENSION["PROJ4","+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext  +no_defs"]]');

[ronzul]

Thanks Dane for the update

[springmeyer]

another update, you need three things to get the old osm2pgsql working with postgis 2.0: 900913.sql, legacy.sql, and adding gist_geometry_ops. See https://github.com/springmeyer/win-osm-workshop/blob/master/Tutorial.md#step-6-configure-the-osm-postgis-database for steps for all of them.

[Zhining-ht]

@springmeyer Hello, i am a beginner of learning postgis & osm2pgsql. I have some difficult in loading osm data into postgis by using the osm2pgsql tool. When i input my command line (C:\osm2pgsql\x64>osm2pgsql -c -d osm -U postgres -W -H localhost -P 5432 -S C:...\default.style C:.....\map.osm),i get the following reply:osm2pgsql SVN version af61cae663<64bit id space> release notes:windows version built by Dominik Perpeet,osm2pgsql:illegal option --W
Thanks for all help!!

[Zverik]

Did you try --password instead?

[Zverik]

Try placing -U postgres -W last in the command line (an advice from Runge of #osm-ru).

[Zhining-ht]

@Zverik Hi~ i did as you said, but it did not work.The reply is still the "windows version ......".Is there problem about the version of my windows or osm2pgsql? Thanks o lot!

[zhuyunji]

Did you solve this password problem? I am now using osm2pgsql on Windows and encounter the same problem?