[postgis-users] Win32 PostgreSQL: pgsql75win.zip replacement

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Dan R Greening
> Sent: 17 August 2004 16:50
> To: 'PostGIS Users Discussion'
> Subject: RE: [postgis-users] Win32 PostgreSQL: pgsql75win.zip 
> replacement
> 
> 
> So I tried it with my test.sql code, which consists of:
> 
> CREATE TABLE poiloc (
>   poiid varchar(11) NOT NULL UNIQUE,
>   latitude DOUBLE PRECISION NOT NULL,
>   longitude DOUBLE PRECISION NOT NULL,
>   PRIMARY KEY  (poiid)
> );
> CREATE INDEX lat_index ON poiloc (latitude,longitude);
> CREATE INDEX lon_index ON poiloc (longitude,latitude);
> INSERT INTO poiloc (poiid, latitude, longitude) VALUES 
> ('27165',37.766029,-122.430382); INSERT INTO poiloc (poiid, 
> latitude, longitude) VALUES ('11',37.764894,-122.423022); 
> INSERT INTO poiloc (poiid, latitude, longitude) VALUES 
> ('12',37.764829,-122.423882); INSERT INTO poiloc (poiid, 
> latitude, longitude) VALUES ('18',37.762529,-122.396481); 
> INSERT INTO poiloc (poiid, latitude, longitude) VALUES 
> ('19',37.761829,-122.418282); INSERT INTO poiloc (poiid, 
> latitude, longitude) VALUES ('25',37.790508,-122.398332); 
> INSERT INTO poiloc (poiid, latitude, longitude) VALUES 
> ('26',37.788028,-122.395281); SELECT 
> AddGeometryColumn(current_schema(),'poiloc','geom',-1,'POINT',
>  2); UPDATE poiloc set geom = GeometryFromText('POINT(' || 
> latitude || ' ' || longitude || ')', -1); CREATE INDEX 
> geom_idx ON poiloc USING GIST(geom gist_geometry_ops); select 
> poiid from poiloc where geom && 
> GeometryFromText('BOX3D(37.76930 -122.40798,37.78929 -122.38621)',-1);
> 
> And the first SELECT fails with this error:
> 
> psql.exe:/greening/dev/poi/sql/postgres/test.sql:16: ERROR:  
> function addgeometrycolumn(name, "unknown", "unknown", 
> integer, "unknown", integer) does not exist
> HINT:  No function matches the given name and argument types. 
> You may need to add explicit type casts.
> psql.exe:/greening/dev/poi/sql/postgres/test.sql:17: ERROR:  
> column "geom" of relation "poiloc" does not exist
> psql.exe:/greening/dev/poi/sql/postgres/test.sql:18: ERROR:  
> column "geom" does not exist
> psql.exe:/greening/dev/poi/sql/postgres/test.sql:19: ERROR:  
> column "geom" does not exist gistest=#
> 
> Am I doing something wrong?
> 
> Dan Greening, Ph.D.  CEO BigTribe 
> http://dan.greening.name/contact.htm 


Hi Dan,

I managed to recreate your problem here. It looks like current_schema()
is defined in the system catalog as type 'name' instead of one of the
text types (varchar or text) - the quick solution is to cast
current_schema() to a text type before passing it to
AddGeometryColumn(), e.g.:

SELECT
AddGeometryColumn(current_schema()::text,'poiloc','geom',-1,'POINT', 2);

Note that you should do a VACUUM ANALYZE after performing a large UPDATE
in order to calculate the correct statistics for the planner, but other
than that, your script looks fine. I don't know yet whether this is just
a Win32 problem or a PostgreSQL 8.0 problem - I will try and look at it
when I find some free time over the next few weeks.


Kind regards,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.