[mapserver-users] Strange Postgis/Mapserver SQL behaviour
Guillaume Sueur
no-reply at neogeo-online.net
Fri Mar 20 08:14:52 PDT 2009
My idea is that in mapfile context your main query should only be SELECT
the_geom from (select...)
as DATA is expecting a single geom object
Hope it helps
Guillaume
Stefan Schwarzer a écrit :
> Hi there,
>
> I have a unfortunately rather complex query for my postgis layers.
>
> But if I take the SQL and insert it into PgAdmin, I get what I want: a
> table with the_geom, name, value, uid.
>
> But when using this query in the map file, I suddenly get an error message:
>
> Warning: [MapServer Error]: prepare_database(): Error declaring cursor:
> ERROR: syntax error at end of input LINE 2: ...D(-180.200445434298
> -90,180.200445434298 90)'::BOX3D, 4326 ) ^ With query string: DECLARE
> mycursor BINARY CURSOR FOR SELECT
> "value"::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),uid::text
> from ( SELECT * FROM (SELECT sub1.* FROM ( SELECT r.the_geom, r.name,
> COALESCE(SUM(d.value), -9999) AS value, r.id AS uid FROM cereals_yield
> AS d RIGHT JOIN countries_view AS c ON c.id = d.id_country RIGHT JOIN
> subregions AS r ON r.id = c.sreg_id WHERE d.year_start = 2006 GROUP BY
> r.name, r.the_geom, r.id ) AS sub1 LEFT JOIN ( SELECT * FROM crosstab(
> 'SELECT r.name AS name, d.year_start AS year, SUM(d.value *
> d_nom.value) / SUM(d_nom.value) AS value FROM in_cereals_yield AS d
> RIGHT JOIN countries_view AS c ON c.id = d.id_country RIGHT JOIN
> rice_harvested AS d_nom ON d_nom.id_country = d.id_country RIGHT JOIN
> subregions AS r ON r. in /www/geodataportal/htdocs/mod_map/map.php on
> line 1331
>
>
> Here is the query:
>
> SELECT * FROM (SELECT sub1.* FROM ( SELECT r.the_geom, r.name,
> COALESCE(SUM(d.value), -9999) AS value, r.id AS uid FROM cereals_yield
> AS d RIGHT JOIN countries_view AS c ON c.id = d.id_country RIGHT JOIN
> subregions AS r ON r.id = c.sreg_id WHERE d.year_start = 2006 GROUP BY
> r.name, r.the_geom, r.id ) AS sub1 LEFT JOIN ( SELECT * FROM crosstab(
> 'SELECT r.name AS name, d.year_start AS year, SUM(d.value *
> d_nom.value) / SUM(d_nom.value) AS value FROM in_cereals_yield AS d
> RIGHT JOIN countries_view AS c ON c.id = d.id_country RIGHT JOIN
> rice_harvested AS d_nom ON d_nom.id_country = d.id_country RIGHT JOIN
> subregions AS r ON r.id = c.sreg_id WHERE (d.year_start = 2003 ) AND
> (d_nom.year_start = 2003 ) AND d_nom.value <> 0 GROUP BY r.name,
> d.year_start ORDER BY 1,2;', 3) AS ct(name varchar, y_2003 numeric)
> ORDER BY 2 NULLS LAST ) AS sub2 ON sub2.name = sub1.name) AS foo
>
>
> And within the mapfile I attach a "USING UNIQUE uid USING srid=4326" to
> the query.
>
>
> Can anyone give me a hint what the problem might be?
>
> Thanks for any adive!
>
> Stef
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
>
More information about the MapServer-users
mailing list