Join between Postgis table and Postgresql table

[Steven Monai]

Carlo:

If you look closely at the error message, it is saying that you need an
alias for your subquery in the FROM clause. So, try this in your DATA line:

DATA "the_geom from (SELECT pippo.gid, pippo.the_geom, pippo.id_shape,
opo.nome, opo.cognome   FROM pippo LEFT OUTER JOIN opo ON pippo.id_shape =
opo.id_shape) as foo using SRID=4326 using unique gid  "

Hope this helps,
-SM
--


On Thu, 10 Nov 2005 06:00:45 -0600, Carlo Calvino <carlo.calvino at EMAIL.IT>
wrote:

>On Wed, 9 Nov 2005 11:34:58 -0800, Paul Ramsey <pramsey at REFRACTIONS.NET>
>wrote:
>
>>No, no, do it on the fly, using the "arbitrary SQL" feature of the
>>Mapserver PostGIS connector:
>>
>>DATA "the_geom from (SELECT g.gid, g.the_geom, a.name, a.description
>>FROM geometries g, attributes a WHERE g.id = a.id) using SRID=4326
>>using unique gid"
>>
>>Note you have to have a geometry and unique id in your output result,
>>and you have to specify the output SRID.  Any SQL is valid in between
>>the (), just make sure you're always outputting a geometry and id.
>
>Thank you very much for your help. I tried your suggestion with this line
>inside the .map file:
>DATA "the_geom from (SELECT pippo.gid, pippo.the_geom, pippo.id_shape,
>opo.nome, opo.cognome   FROM pippo LEFT OUTER JOIN opo ON pippo.id_shape =
>opo.id_shape) using SRID=4326 using unique gid  "
>
>Here the relationship between pippo(shape) and opo (data) is 1-to-1.
>But mapserver (cgi) gives me this error:
>
>msDrawMap(): Image handling error. Failed to draw layer named 'cata'.
>prepare_database(): Query error. Error executing POSTGIS DECLARE (the
>actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
>asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from
>(SELECT pippo.gid, pippo.the_geom, pippo.id_shape, opo.nome, opo.cognome
>FROM pippo LEFT OUTER JOIN opo ON pippo.id_shape = opo.id_shape) WHERE
>the_geom && setSRID('BOX3D(2766010.83333333 4492764,2781280.16666667
>4504216)'::BOX3D, 4326 )'
>
>Postgresql reports the error as 'ERROR: subquery in FROM must have an
>alias HINT: For example, FROM (SELECT ...) [AS] foo. '
>
>
>What is the matter with that? Moreover, I need 'one-to-may' relationship,
>and as you wrote, it seems to be inapplicable in this case. How could I
>handle 1-to-many relationship with postgis?