[mapserver-users] Postgis mapserver sql help

[Alexandre Busquets Triola]

Thanks Guillaume

the final code is

geom FROM (select codi, geom FROM par_vit3
    where intersects(
        (
        select GeomFromText(
        'POLYGON((258000 4485000, 258000 4625800 ,398800 4625800, 398800 
4485000, 258000 4485000))',23031)
    as consulta1
        )
        , geom )
         AND codi IS NOT NULL
      ) as consulta2
     using unique codi using SRID=23031






En/na Guillaume Sueur ha escrit:
> Hi,
>
> try this :
>
> geom FROM (select id, geom FROM par_vit3
>     where intersects(
>         (
>         select GeomFromText(
>         'POLYGON((258000 4485000, 258000 4625800 ,398800 
> 4625800         ,398800 4485000, 258000 4485000))',23031)
>         )
>         , geom )
>       )
>      using unique id using SRID=23031
>
> Note that I've added an id field to the SQL. Adapt it to your own id 
> field.
>
>
>
> Guillaume
>
> Alexandre Busquets Triola a écrit :
>>
>> Hi, this is the first time that I write to the list.
>> I'm working with mapserver and postgis and I have a problem with 
>> mpaserver,
>> if I put this query on postgres
>>
>>
>> select geom FROM par_vit3 where intersects(
>> (
>> select GeomFromText(
>> 'POLYGON((258000 4485000, 258000 4625800 ,398800 4625800 ,398800 
>> 4485000, 258000 4485000))',23031
>> ) as foo
>> )
>> , geom )
>>
>>
>> works ok
>>
>> but if I put this
>>
>>
>> geom FROM par_vit3 where intersects(
>> (
>> select GeomFromText(
>> 'POLYGON((258000 4485000, 258000 4625800 ,398800 4625800 ,398800 
>> 4485000, 258000 4485000))',23031
>> ) as foo using SRID=23031
>> )
>> , geom ) using SRID=23031
>>
>>
>> in maperver not works, this is the error that I have
>>
>>
>> Warning: [MapServer Error]: prepare_database(): Error executing 
>> POSTGIS DECLARE (the actual query) statement: 'DECLARE mycursor 
>> BINARY CURSOR FOR SELECT 
>> asbinary(force_collection(force_2d(geom)),'NDR'),OID::text from 
>> par_vit3 where intersects( ( select GeomFromText( 'POLYGON((258000 
>> 4485000, 258000 4625800 ,398800 4625800 ,398800 4485000, 258000 
>> 4485000))',23031 ) as foo WHERE geom && setSRID('BOX3D(258000 
>> 4485000,398800 4625800)'::BOX3D, 23031 )' Postgresql reports the 
>> error as 'ERROR: syntax error at end of input LINE 5: 
>> ...tSRID('BOX3D(258000 4485000,398800 4625800)'::BOX3D, 23031 ) ^ ' 
>> More Help: Error with POSTGIS data variable. You specified 'check 
>> your .map file'. 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. For more help, please in 
>> D:\alex\webs\alex\codorniu\mapscript\parceles2.php on line 172
>>
>> Warning: [MapServer Error]: msPOSTGISLayerRetrievePK(): Error 
>> executing POSTGIS statement (msPOSTGISLayerRetrievePK():select 
>> attname from pg_attribute, pg_constraint, pg_class where 
>> pg_constraint.conrelid = pg_class.oid and pg_class.oid = 
>> pg_attribute.attrelid and pg_constraint.contype = 'p' and 
>> pg_constraint.conkey[1] = pg_attribute.attnum and pg_class.relname = 
>> 'par_vit3 where intersects( ( select GeomFromText( 'POLYGON((258000 
>> 4485000, 258000 4625800 ,398800 4625800 ,398800 4485000, 258000 
>> 4485000))',23031 ) as foo' and pg_table_is_visible(pg_class.oid) and 
>> pg_constraint.conkey[2] is null in 
>> D:\alex\webs\alex\codorniu\mapscript\parceles2.php on line 172
>>
>>
>> Thanks
>>
>
>


-- 
_________________________________________________________________
Alexandre Busquets Triola
Servei de Sistemes d'Informació Geogràfica i Teledetecció (SIGTE)
Universitat de Girona
Pl. Ferrater Mora, 1
17071 Girona
Tel. 972 41 80 39  Fax. 972 41 82 30
alex at sigte.udg.edu
http://www.sigte.udg.es/
_________________________________________________________________