[postgis-users] Update to where clause errors

[Paul A Peters]

David:

I was indeed using the wrong point type, thanks for the help. I have gone
back and rebuilt my postgis installation and rebuilt my test database.
However, I am still getting the same error as before. So, I tried the "\d
gis_test" as suggested and I got the response attached. Thus, I am still
lost.

Thanks,
Paul

--------------------------------------

C:\postgresql\bin>psql -U Administrator edumap
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

edumap=# \d gis_test
             Table "gis_test"
  Column  |       Type        | Modifiers
----------+-------------------+-----------
 gid      | integer           |
 cod_mod  | character varying |
 the_geom | geometry          |
Check constraints: "$1" (srid(the_geom) = -1)
                   "$2" ((geometrytype(the_geom) = 'POINT'::text) OR
(NULL::geom
etry = the_geom))


edumap=# \q

C:\postgresql\bin>

--------------------------------------

Again, the error I get when using shp2img is:

--------------------------------------

C:\Inetpub\Scripts>shp2img -m c:\msapps\projects\Independencia.map -o
out.png
prep_DB(): Query error. Error executing POSTGIS  DECLARE (the actual query)
stat
ement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(forc
e_2d()),'NDR'),OID::text from  WHERE  && setSRID('BOX3D(273346
8670666,281455 86
77412)'::BOX3D, find_srid('','','') )' <br><br>

Postgresql reports the error 'ERROR:  parser: parse error at or near "WHERE"
'<br><br>

More Help:<br><br>

Error with POSTGIS data variable. You specified '<check your .map
file>'.<
br>
Standard ways of specifiying are : <br>
(1) 'geometry_column from geometry_table' <br>
(2) 'geometry_column from (<sub query>) as foo using unique <column
nam
e> using SRID=<srid#>' <br><br>

Make sure you put in the 'using unique  <column name>' and 'using
SRID=#'
clauses in.

<br><br>For more help, please see
http://postgis.refractions.net/documentation.p
hp

<br><br>Mappostgis.c - version of Nov 15/2002.


C:\Inetpub\Scripts>
--------------------------------------

My layer definition is:
--------------------------------------
  LAYER
    CONNECTIONTYPE postgis
    CONNECTION "user=Internet password=Manzana dbname=edumap host=localhost"
    DATA "the_geom FROM gis_test"
    GROUP "Independencia"
    NAME "Escuelas Primarias"
    SIZEUNITS PIXELS
    STATUS ON
    TEMPLATE "ttt.html"
    TOLERANCE 3
    TOLERANCEUNITS PIXELS
    TYPE POINT
    UNITS METERS
    CLASS
      NAME "Escuelas"
      COLOR 150 0 0
      MAXSIZE 100
      MINSIZE 1
      SIZE 8
      SYMBOL 2
    END
  END

--------------------------------------

Paul A Peters
http://www.fes.uwaterloo.ca/u/papeters


-----Original Message-----
From: David Blasby [mailto:dblasby at refractions.net] 
Sent: May 27, 2003 11:15 AM
To: papeters at alumni.uwaterloo.ca; PostGIS Users Discussion
Subject: Re: [postgis-users] Update to where clause errors


Paul A Peters wrote:
> C:\Inetpub\Scripts>shp2img -m C:\msapps\projects\Independencia.map -o 
> out.png prep_DB(): Query error. Error executing POSTGIS  DECLARE (the 
> actual
> query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
> asbinary(force_collection(force_2d(shape)),'NDR'),OID::text from gis_test
> WHERE shape && setSRID('BOX3D(273346 8670666,281455 8677412)'::BOX3D,
> find_srid('','gis_test','shape') )' <br><br>
> 
> Postgresql reports the error 'ERROR:  Function 'force_2d(point)' does 
> not exist
>         Unable to identify a function that satisfies the given 
> argument types
>         You may need to add explicit typecasts
> '<br><br>

The message : Function 'force_2d(point)'

indicates you're probably using the built-in postgresql datatypes NOT 
PostGIS types.  How did you get your data into the database?

You can test this by opening up psql and typing:

\d gis_test

If the shape type is 'point' you're using the old postgresql datatypes. 
  If it says 'geometry' you're using a PostGIS type - unfortunately this 
would indicate that something is wrong with your installation.

dave