[postgis-users] Create View with PostGis table

[chodgson@refractions.net]

I think your mapserver may be broken... I would get the lastest one from CVS 
and recompile, then try again. All of those DATA statements look like they 
should work, mapserver shouldn't be giving server errors (I'm pretty sure a new 
version wouldn't, it would at least give you a proper postgis/postgres error).

You might want to try a lower-case "from" instead of "FROM"... some earlier 
versions of mappostgis.c were case-sensitive. Same with the "USING SRID" 
and "USING UNIQUE" - try them in all different cases.

good luck,
Chris

Quoting OpenGis <opengis@libero.it>:

> 
> chodgson@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
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>