[postgis-users] Create View with PostGis table

[OpenGis]

chodgson at refractions.net wrote:

>Do you really need to use a view? Did you know that it is possible to specify 
>more complicated queries in the mapserver config file? 
>
>Read this:
>
>http://postgis.refractions.net/docs/x552.html#AEN610
>
[0]
    DATA "the_geom from v_land_t as foo USING UNIQUE oid USING 
SRID=-1"    # OK
where v_land_t:
CREATE VIEW v_land_t AS SELECT t0.oid, t0.*, phone FROM landusea_region 
t0, tab_zu where t0.name= tab_zu.nm;

[1] -- very simple--
 DATA "the_geom FROM (SELECT the_geom, oid FROM landusea_region) AS foo 
USING UNIQUE oid USING SRID=-1"
[2] -- quite simple--
 DATA "the_geom FROM (SELECT the_geom, oid FROM landusea_region WHERE 
landusea_region.gid < 10) AS foo USING UNIQUE oid USING SRID=-1"
 [3]-- more complex--
 DATA "the_geom FROM (SELECT landusea_region.the_geom AS the_geom, 
landusea_region.oid AS oid, tab_zu.phone AS phone FROM landusea_region, 
tab_zu WHERE landusea_region.name = tab_zu.nm) AS new_table USING UNIQUE 
oid USING SRID=-1"

For [1] (but same behavior for [2] & [3])
in mode=browse
Server error!
The server encountered an internal error and was unable to complete your 
request.
Error message:
Premature end of script headers: mapserv

in mode=query
prep_DB(): Query error. Error executing POSTGIS DECLARE (the actual 
query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT 
asbinary(force_collection(force_2d(8B8B)),'NDR'),oid::text 
from @B@B WHERE 8B8B && setSRID('BOX3D(12.4496 
41.8826186666667,12.452 41.8850186666667)'::BOX3D, -1 )'

Postgresql reports the error ''

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 see http://postgis.refractions.net/documentation.php

Mappostgis.c - version of June 12/2003.
msPOSTGISLayerParseData(): Query error. Error parsing POSTGIS data 
variable. Must contain 'geometry_column from table_name' or 'geom from 
(subselect) as foo' (couldnt find ' from '). More help:

Error with POSTGIS data variable. You specified 'the_geom FROM (SELECT 
the_geom, oid FROM landusea_region) AS foo USING UNIQUE oid USING SRID=-1'.
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 see http://postgis.refractions.net/documentation.php

Mappostgis.c - version of June 12/2003.

>>>My landusea_region table:
>>>Field		Type
>>>gid 		int4
>>>polygon_id 	int8
>>>name 		varchar
>>>langcode 	varchar
>>>type 		varchar
>>>city 		varchar
>>>the_geom 	geometry
>>>      
>>>
0   | 50987009   | VILLA DORIA PAMPHILI  | ITA  |PARK (CITY/COUNTY)  | 
Y  | SRID=-1;MULTIPOLYGON(((12.43724 41.88032,12.4366 41.88037,12.43621 
41.88041,12.43609 41.88047,12.43536  .......

What do you think? Is it possible PostGis bad configuration (but work 
fine in other condition)?
Any clue?

Thanks in advance

-- 
opengis (at) libero (dot) it
AOL: open2gis