[postgis-users] please NEED help....

[Sébastien DUBOIS]

i'm quite sad no answer to my old post :
http://postgis.refractions.net/pipermail/postgis-users/2004-July/005170.html
(let's see a the end of the message)

i'm quite sure someone of you have to solve in the past this topic issue.
when querying by rectangle multi points i got the error:
---------
Warning: [MapServer Error]: msPOSTGISLayerGetShape(): Error executing POSTGIS
SQL statement (in FETCH ALL): DECLARE mycursor BINARY CURSOR FOR SELECT
the_geom::text,asbinary(force_collection(force_2d(the_geom)),'NDR') from
clientspostgis WHERE oid = 19621 -ERROR: cursor "mycursor" already exists
More Help:
Error with POSTGIS data variable. You specified ''.
Standard ways of specifiying are :
(1) 'geometry_column from geometry_table'
(2) 'geometry_column from (<sub query>) as foo using unique <column name> using
SRID=<srid#>'

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

LET'S SEE ALL MY [LONG] POST:

I'd imported in the Gmap soft (from dmsolutions phpmapscript+mapserver+rosa)
a postgis layer build with shp2pgsql 

>createdb montelimarpostgis
>createlang plpgsql montelimarpostgis
>psql montelimarpostgis < /home/postgre/postgis.sql
>psql montelimarpostgis < /home/postgre/spatial_ref_sys.sql 
>shp2pgsql -D /var/www/html/workshop/data/Clients.shp clientspostgis
montelimarpostgis | psql montelimarpostgis

then

montelimarpostgis=# \d clientspostgis
                                  Table "public.clientspostgis"
   Column   |       Type        |                            Modifiers         

                 
------------+-------------------+-----------------------------------------------------------------
 gid        | integer           | not null default
extval('public.clientspostgis_gid_seq'::text)
 adresse    | character varying | 
 departemen | bigint            | 
 x_adresse  | numeric           | 
 y_adresse  | numeric           | 
 nom        | character varying | 
 prenom     | character varying | 
 tel_fixe   | character varying | 
 tel_mobile | character varying | 
 email      | character varying | 
 id_client  | character varying | 
 the_geom   | geometry          | 

Indexes:
    "clientspostgis_pkey" primary key, btree (gid)
    "clientspostgis_geom_idx" gist (the_geom)
Check constraints:
    "$1" CHECK (srid(the_geom) = -1)
    "$2" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)

montelimarpostgis=# \d geometry_columns
            Table "public.geometry_columns"
      Column       |          Type          | Modifiers 
-------------------+------------------------+-----------
 f_table_catalog   | character varying(256) | not null
 f_table_schema    | character varying(256) | not null
 f_table_name      | character varying(256) | not null
 f_geometry_column | character varying(256) | not null
 coord_dimension   | integer                | not null
 srid              | integer                | not null
 type              | character varying(30)  | not null
 attrelid          | oid                    | 
 varattnum         | integer                | 
 stats             | histogram2d            | 
Indexes:
    "geometry_columns_pk" primary key, btree (f_table_catalog, f_table_schema,
f_table_name, f_geometry_column)


my layer in my .map looks like this :

LAYER
  NAME montelimarpostgis
  METADATA
    "DESCRIPTION"   "Clients/Postgis"
    "RESULT_FIELDS" "ADRESSE DEPARTEMEN X_ADRESSE Y_ADRESSE NOM PRENOM TEL_FIXE
TEL_MOBILE EMAIL ID_CLIENT"
  END
  TYPE POINT
  STATUS ON
  CONNECTIONTYPE postgis
  CONNECTION "host=localhost dbname=montelimarpostgis user=postgre
password=postgre"
  DATA 'the_geom from clientspostgis using unique oid using SRID=-1 '

#'the_geom from (select oid,the_geom from clientspostgis) as foo'
#or
#the_geom from clientspostgis using unique oid using SRID=-1' 

#->ok but rectangle select bug

#"select f_geometry_column,type from geometry_columns where f_table_name like
"clientspostgis""

  LabelItem "the_geom"  #ce que l'on souhaite afficher comme attribut parmi
RESULT_FIELDS à coté des points 
  CLASSITEM "the_geom"
  CLASS
    EXPRESSION "1"
    TEMPLATE "ttt_query.html"
    SYMBOL 2
    SIZE 8
    NAME "clientspostgis"
    LABEL
      COLOR  255 0 0
      FONT fritqat-italic
      TYPE truetype
 SIZE 8
      POSITION AUTO
      PARTIALS FALSE
      OUTLINECOLOR 255 255 255
    END
    COLOR 0 0 0
  END
  CLASS
    EXPRESSION /2|3/
    TEMPLATE "ttt_query.html"
    SYMBOL 7
    SIZE 6
    NAME "clientpostgis"
    LABEL
    COLOR  0 0 0  #couleur affichage legende point
     FONT fritqat
     TYPE truetype
     SIZE 8
     POSITION AUTO
     PARTIALS FALSE
     OUTLINECOLOR 255 255 255
    END
    COLOR 0 0 0
  END
  TOLERANCE 5
END # Layer


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

With this code, i succeed in displaying my point (with label POINT(X,Y))

BUT

*when i make a query on ONLY ONE POINT I can't display the attributes of my
original shapefile (so the colums of clientspostgis)
My metadata is correct ...
No error when i query only no display

*when i make a query on MULTI points: ERROR
-------------------------------
Warning: [MapServer Error]: msPOSTGISLayerGetShape(): Error executing POSTGIS
SQL statement (in FETCH ALL): DECLARE mycursor BINARY CURSOR FOR SELECT
the_geom::text,asbinary(force_collection(force_2d(the_geom)),'NDR') from
clientspostgis WHERE oid = 19621 -ERROR: cursor "mycursor" already exists
More Help:
Error with POSTGIS data variable. You specified ''.
Standard ways of specifiying are :
(1) 'geometry_column from geometry_table'
(2) 'geometry_column from (<sub query>) as foo using unique <column name> using
SRID=<srid#>'

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




I've read that sometimes in old version the multipoint query error is due to
postgis not closed at the end of a requet but i have the last version of
mapserv and postgis ...

CAN SOMEONE GIVE ME SOME HELP??


-- 
Sébastien DUBOIS
Ecole Généraliste d'Ingénieurs de Marseille (ex ESM2)

1024D/A1140BCB 
Empreinte de la clé = 1BC8 7E21 4DA7 13D5 1B5F  876F F719 2B7A A114 0BCB
disponible sur www.keyserver.net





-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/