[mapserver-users] Re: JOIN in a mapfile from a shapefile to a MySQL database

[Minka Stoyanova]

Thank you Charles! Your example really works and I very quckly managed to
achieve what I intended.

BUT it is a different thing - you make a join during constracting the layer
and I suppose that it should affect how a layer looks like.

I was asking about the JOIN object of the mapfile, that according to the
documentation cannot affect the look of a layer and gives results only whan
the layer is queried. I tried simpler JOIN with a csv file which give no
results eighther.

Someone has any idea?

On Fri, Sep 5, 2008 at 10:29 PM, Charles Theobald <
charles.theobald at ncmail.net> wrote:

> We do that kind of join with a lot of our data.  Here's a code snippet from
> a polygon layer in one of our maps:
>
> LAYER #Service area poly layer starts here
>   NAME Service_areas
>   CONNECTIONTYPE ogr
>   CONNECTION "/path/to/shape/file/on/mapserver/cpws.shp"
>   DATA "SELECT * FROM cpws LEFT JOIN
> 'MYSQL:databasename,host=mysqlhostname,user=mysqluseraccount,password=mysqluserpassword'.sysstatus
> ON cpws.id = sysstatus.permit"
>
> *cpws is the shape file (on the map server), and sysstatus is a table on
> our MySQL server (different boxes); the join is being performed on cpws.idand sysstatus.permit.
> Hope this helps.
>
>
> mapserver-users-request at lists.osgeo.org wrote:
>
>> Send mapserver-users mailing list submissions to
>>        mapserver-users at lists.osgeo.org
>>
>> To subscribe or unsubscribe via the World Wide Web, visit
>>        http://lists.osgeo.org/mailman/listinfo/mapserver-users
>> or, via email, send a message with subject or body 'help' to
>>        mapserver-users-request at lists.osgeo.org
>>
>> You can reach the person managing the list at
>>        mapserver-users-owner at lists.osgeo.org
>>
>> When replying, please edit your Subject line so it is more specific
>> than "Re: Contents of mapserver-users digest..."
>>
>>
>> Today's Topics:
>>
>>   1. JOIN in a mapfile from a shapefile to a MySQL     database
>>      (Minka Stoyanova)
>>   2. High CPU (Steve White)
>>
>>
>> ----------------------------------------------------------------------
>>
>> Message: 1
>> Date: Fri, 5 Sep 2008 19:13:39 +0300
>> From: "Minka Stoyanova" <minka.stoyanova at gmail.com>
>> Subject: [mapserver-users] JOIN in a mapfile from a shapefile to a
>>        MySQL   database
>> To: mapserver-users at lists.osgeo.org
>> Message-ID:
>>        <66b0a28d0809050913k16c6864cj47ef8ddb6e113b31 at mail.gmail.com>
>> Content-Type: text/plain; charset="iso-8859-1"
>>
>>
>> Hello,
>>
>> It's the first time i try to make a JOIN in a mapfile layer. I want to
>> query
>> a mysql database for some attributes (columns) of a selected point from
>> the
>> point layer.
>>
>> This is the layer definition:
>>
>>  LAYER # States point layer 1 begins here
>>    NAME         "meteostations"
>>    DATA         meteostationstotal_11
>>    STATUS       ON
>>    TYPE         POINT
>>    CLASS
>>      SYMBOL 'circle'
>>      SIZE 8
>>      COLOR 255 0 0
>>      OUTLINECOLOR 0 0 0
>>    END
>>    TEMPLATE "sometemplate.html" TOLERANCE 3 TOLERANCEUNITS PIXELS
>>    METADATA
>>      "DESCRIPTION"    "Meteo stations"
>>      "RESULT_FIELDS"  "A sin_kod NAME IME longi X_COO lat Y_COO"
>> # the result fields are mixed - the* names of some columns* from the
>> shapefile and from the database
>>    END
>>    JOIN
>>      NAME FirstJoin
>>      CONNECTION
>> 'MYSQL:meteo,host=some_IP,user=myuser,password=mypassword,tables=bgc'
>>      CONNECTIONTYPE ogr
>>      FROM A
>>      TABLE bgc
>>      TO sin_kod
>>      TYPE ONE-TO-ONE
>>    END
>>  END # States point layer 1 ends here
>>
>> I receive no error (neighther in the mapserver.log file, nor in the web
>> server logs) BUT I don't receive any info from the database.
>>
>> Thanks in advance!
>> -------------- next part --------------
>> An HTML attachment was scrubbed...
>> URL:
>> http://lists.osgeo.org/pipermail/mapserver-users/attachments/20080905/64c07317/attachment-0001.html
>>
>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20080906/f5db7c57/attachment.html