osm2pgsql SQL error about AsText function

ronzulu · 2012-04-24 14:51:37

Hi

Newbie Ronny here...

I've installed Postgres 9.1 with postgis 2.0 under Windows XP.

And ran the 900913.sql script.

When I run:

C:\postgres\9.1\bin>osm2pgsql -d postgis -U postgres -H localhost -W -P 5432 -S "C:\[Software Packages]\OSM\osm2pgsql\default.style" "C:\Documents and Settings\admin\My Documents\Hear Here\Mapping\OSM Australia\London.osm"

I get the error:

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.

Any ideas? I've never used Postgres or osm2pgsql before.

Thanks!
Ronny

sly · 2012-04-24 18:31:33

it looks like postgis is either not installed or not activated
(search google about "activating a module" in postgresql 9.1)

ronzulu · 2012-04-25 04:55:32

Thanks Sly, but it seems that postgis is working correctly.

I can successfully execute test commands such as:

CREATE TABLE gtest ( ID int4, NAME varchar(20) );
SELECT AddGeometryColumn('', 'gtest','geom',-1,'LINESTRING',2);
INSERT INTO gtest (ID, NAME, GEOM) 
VALUES (
  1, 
  'First Geometry', 
  ST_GeomFromText('LINESTRING(2 3,4 5,6 5,7 8)', -1)
);

The surprising thing is that the osm2pgsql query refers to the planet_osm_point table, but this does not exist in the postgis database. If I execute:

PREPARE get_way (int4) AS SELECT AsText(way) FROM planet_osm_point WHERE osm_id = $1

in pgAdmin, I get a more expected error:

ERROR: relation "planet_osm_point" does not exist
SQL state: 42P01
Character: 51

I must be missing something fundamental here... I would be grateful for any ideas!

ronzulu · 2012-04-25 06:04:53

Ok, making progress... if I create the planet_osm_point table, and then put in the PREPARE statement into pgAdmin, I get the same error message. Good.

The issue seems to be that AsText is now called st_AsText.

The following SQL works:

PREPARE get_way (int4) AS SELECT st_AsText(way) FROM planet_osm_point WHERE osm_id = $1;

So I must be using an old version of the windows osm2pgsql

JRA · 2012-04-25 09:01:39

Hi,

Seems to be the same issue than with the current Mapserver and Postgis 2.0. Postgis 2.0 renames some functions to more standard ones. Read the following link and try legacy.sql
https://github.com/mapserver/mapserver/issues/4186
There has been only two downloadable osm2pgsql versions for Windows ever and the newer is two years old now. Developers are developing for Linux. I used a little sum of money for getting the first Windows executable. Perhaps I will try the same trick later this year.

ronzulu · 2012-04-25 13:37:13

Thanks for the info JRA.

For now, I uninstalled Postgis 2.0, and installed 1.5 using StackBuilder. osm2pgsql works now.

Last edited by ronzulu (2012-04-25 13:38:47)

Hishan · 2013-02-13 08:47:37

ronzulu

How did you find the older PostGIS 1.5 from Stackbuider.

OpenStreetMap Forum

#1 2012-04-24 14:51:37

osm2pgsql SQL error about AsText function

#2 2012-04-24 18:31:33

Re: osm2pgsql SQL error about AsText function

#3 2012-04-25 04:55:32

Re: osm2pgsql SQL error about AsText function

#4 2012-04-25 06:04:53

Re: osm2pgsql SQL error about AsText function

#5 2012-04-25 09:01:39

Re: osm2pgsql SQL error about AsText function

#6 2012-04-25 13:37:13

Re: osm2pgsql SQL error about AsText function

#7 2013-02-13 08:47:37

Re: osm2pgsql SQL error about AsText function

Board footer