[mapserver-users] Can't get JOIN to work with Postgis

[Ganaël Jatteau]

You're right, the two tables are not in the same schema but they have the
appropriate select rights.

Having a join defined in the data works fine:

DATA "the_geom from (SELECT dept_aquitaine.the_geom as the_geom, wap1.f0 as
name0, wap1.f1 as name1, dept_aquitaine.oid as oid from dept_aquitaine INNER
JOIN webigeo.wap1 on (dept_aquitaine.code_depar = wap1.f0)) as tmp using
SRID=27581"

However I would have preferred using a JOIN since it is easier to generate
in a program and more generic.

Cheers
Ganael


-----Message d'origine-----
De : Pavel Iacovlev [mailto:iacovlev.pavel at gmail.com] 
Envoyé : vendredi 3 juillet 2009 10:22
À : Ganaël Jatteau
Objet : Re: [mapserver-users] Can't get JOIN to work with Postgis

wap1 is in webigeo schema and dept_aquitaine is in public schema ?

Try to do a simple SQL join query, first test in in pgsql and then
just paste it in your data sections, easier to debug.

DATA "the_geom from (
SELECT dept_aquitaine.the_geom as the_geom,  wap1.some_field as name
from dept_aquitaine
LEFT JOIN webigeo.wap1 on (dept_aquitaine.f0 = wap1.f0)
) as tmp using SRID=YOUR_SRID using unique YOUR_GID"

On Fri, Jul 3, 2009 at 10:37 AM, Ganaël Jatteau<gju at galigeo.com> wrote:
> Hi,
>
>
>
> I'm trying to use the tag JOIN in a layer in order to join two tables in
> POSTGIS.
>
>
>
> The layer gets displayed on the map but it seems that the join is ignored
(a
> subset of the layer should be displayed normally after the join and when I
> select some features I don’t see the attributes from the join table).
>
>
>
> BTW: I use MapServer 5.2.1 on Windows
>
>
>
> Is there anything wrong with the layer that has been defined:
>
>
>
> LAYER
>
>     CONNECTION "host=localhost user=scott dbname=basepiad password='tiger'
>
>     CONNECTIONTYPE POSTGIS
>
>     DATA "the_geom from dept_aquitaine using unique oid using SRID=27581"
>
>       CLASS
>
>       NAME "classe"
>
>       STYLE
>
>         ANGLE 360
>
>         COLOR 255 255 255
>
>         OPACITY 100
>
>         OUTLINECOLOR 180 180 180
>
>         SYMBOL 0
>
>       END
>
>     END
>
>     JOIN
>
>       NAME "test_join"
>
>       TABLE "webigeo.wap1"
>
>       FROM "code_depar"
>
>         TO "f0"
>
>       TYPE ONE-TO-ONE
>
>     END
>
>     NAME "departements_join"
>
>     STATUS ON
>
>     TEMPLATE "ttt"
>
>     TYPE POLYGON
>
>     UNITS METERS
>
>   END
>
>
>
>
>
> Thanks in advance.
>
>
>
> Ganaël
>
>
>
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
>



-- 
http://iap.md, The future is open