[mapserver-users] Postgis mapserver sql help

[Guillaume Sueur]

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
>