[Pmapper-users] Pb with RESULT_JOIN postgis geometry to a postgresql data

[François PINET]

Hello,

Excuse me for my bad english.
I use pmapper 1.9.4 with postgresql 8.0.4 and postgis 1.0.4.
I would like to know how can i make a query result with a data geometry postgis 
and a field store in another postgresql table.
In fact i want to join data geometry with the field "code_insee" of my "route" 
table with the field code_insee of my "commune" table. I want the result 
"libcommune" in my query. 
Can i have help please?

Here is a part of my mapfile for this layer:

LAYER # layer_route
  NAME "route"
  CONNECTIONTYPE postgis
  CONNECTION "user=myuser password=mypass dbname=mydb host=localhost"
  METADATA
    "DESCRIPTION" "route"
    "RESULT_FIELDS" "identifian,nom,code_insee,libcommune"
    "RESULT_HEADERS" "Identifiant,nom,code_INSEE,commune"
    "RESULT_JOIN" "pgsql://postgres:[EMAIL PROTECTED]/sig||[EMAIL 
PROTECTED]@[EMAIL PROTECTED]||code_insee||0"
    "wms_srs" "EPSG:27582" 
  END
  TYPE LINE
  STATUS ON
  DATA "the_geom from (select route.oid,* from route) as foo"
  MAXSCALE 25000
  LABELITEM "nom"
  CLASS
    NAME "Route"
    OUTLINECOLOR 50 230 32
    TEMPLATE "ttt_query.html"  
    LABEL
      TYPE TRUETYPE
      FONT arial
      COLOR 255 0 0
      SIZE 7
      POSITION AUTO
      MINDISTANCE 2
      ANGLE AUTO
      POSITION AUTO
    END  
  END
  TOLERANCE 10
END # layer_route

Thanks a lot.
FP
-------------- next part --------------
An HTML attachment was scrubbed...
URL: 
http://www.faunalia.com/pipermail/pmapper-users/attachments/20051110/862454d7/attachment.htm
From [EMAIL PROTECTED]  Thu Nov 10 16:52:58 2005
From: [EMAIL PROTECTED] (Alessandro Pasotti)
Date: Thu Nov 10 16:53:34 2005
Subject: [Pmapper-users] Pb with RESULT_JOIN postgis geometry to a
        postgresql data
In-Reply-To: <[EMAIL PROTECTED]>
References: <[EMAIL PROTECTED]>
Message-ID: <[EMAIL PROTECTED]>

Il 15:21, gioved? 10 novembre 2005, Fran?ois PINET ha scritto:
> Hello,
>
> Excuse me for my bad english.
> I use pmapper 1.9.4 with postgresql 8.0.4 and postgis 1.0.4.
> I would like to know how can i make a query result with a data geometry
> postgis and a field store in another postgresql table. In fact i want to
> join data geometry with the field "code_insee" of my "route" table with the
> field code_insee of my "commune" table. I want the result "libcommune" in
> my query. Can i have help please?

I'm not sure I've got you question, if you're trying to join two (or more) 
POSTGIS tables, then you should do like this:

    CONNECTIONTYPE postgis
    CONNECTION "user=*** dbname=vngeo host=localhost password=*** port=***"
    TYPE POINT
    UNITS METERS
    DATA "the_geom from (select vngeo_ristoranti.gid AS gid, 
vngeo_ristoranti.gtype AS gtype, vngeo_ristoranti.gwebsites AS 
gwebsites , vngeo_ristoranti.gname AS gname, vngeo_ristoranti.the_geom AS 
the_geom , vngeo_attrvalue.value AS titolo from vngeo_ristoranti LEFT JOIN 
vngeo_attrvalue ON vngeo_ristoranti.gid=vngeo_attrvalue.resid WHERE 
vngeo_ristoranti.gwebsites LIKE '#9#' AND vngeo_attrvalue.lang = 'it' AND 
vngeo_attrvalue.attrcode='titolo' AND 
vngeo_attrvalue.layer='vngeo_ristoranti') AS foo USING UNIQUE gid USING 
SRID=26591"

In this case the main table is vngeo_ristoranti, the joined attributes table 
is vngeo_attrvalue.


Hope it helps.

See also postgis online documentation, there is a section on mapserver.

-- 
Alessandro Pasotti
ICQ# 245871392
Linux User #167502