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

Charles Theobald charles.theobald at ncmail.net
Fri Sep 5 12:29:41 PDT 2008


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.id and 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
>
> ------------------------------
>
> Message: 2
> Date: Fri, 5 Sep 2008 20:03:58 +0100
> From: "Steve White" <steve at online-archaeology.co.uk>
> Subject: [mapserver-users] High CPU
> To: "Mapserver-users" <mapserver-users at lists.osgeo.org>
> Message-ID: <002f01c90f8a$26b9e3f0$742dabd0$@co.uk>
> Content-Type: text/plain; charset="us-ascii"
>
> I'm running the latest version of MS4W on a virtual private server with the
> following:
>
>  
>
> -          2003 Server
>
> -          IIS 6.0
>
> -          Intel Xeon CPU 3.20Ghz
>
> -          640MB RAM 
>
>  
>
> I have one shapefile 850KB and a basic mapfile like below:
>
>  
>
> MAP
>
>   NAME                                                                 Test
>
>   STATUS                                              ON
>
>   UNITS                                                 METERS
>
>   SHAPEPATH                                      "C:\Inetpub\scripts\data\"
>
>   IMAGECOLOR                                  0 255 255
>
>   SIZE                                                      400 400
>
>   DEBUG                                                               ON
>
>   IMAGETYPE                                      PNG
>
>   EXTENT                                               -4.35 56.14 -3.23
> 56.91
>
>  
>
> WEB
>
>   METADATA
>
>     "wms_title"                                    "Test MS"
>
>     "wms_onlineresource"             "http://localhost/scripts/mapserv.exe&"
>
>
>     "wms_srs"                                      "EPSG:4326 EPSG:54004
> EPSG:27700"
>
>     "wms_feature_info_mime_type" "text/html"
>
>   END
>
>  
>
> IMAGEPATH                                       "C:\Inetpub\wwwroot\tmp\"
>
> IMAGEURL                                          "C:\Inetpub\wwwroot\tmp\"
>
> LOG
> "C:\Inetpub\wwwroot\tmp\mapserver.log"
>
> TEMPLATE
> "C:\Inetpub\wwwroot\Test\templates\template.html"
>
> EMPTY
> "C:\Inetpub\wwwroot\Test\templates\template_empty.html"  
>
>  
>
> END
>
>  
>
> PROJECTION
>
>   "init=epsg:4326"
>
> END
>
>  
>
> SYMBOL
>
>   NAME "circle"
>
>   TYPE ellipse
>
>   FILLED true
>
>   POINTS
>
>     1 1
>
>   END
>
> END
>
>  
>
> LAYER
>
>                 NAME                                                   Test
>
>                 METADATA
>
>         "wms_title"                                                "Test"
>
>                 "gml_include_items"                     "all"
>
>                 "wms_include_items"                   "all"
>
>                 "wms_feature_info_mime_type"           "text/html"
>
>                 END
>
>                 TYPE
> POINT
>
>                 DATA
> Test
>
>                 STATUS
> ON
>
>                 DUMP                                                   TRUE
>
>         CLASS
>
>                                 TEMPLATE
> "C:\Inetpub\wwwroot\Test\templates\template_test.html"
>
>                 STYLE
>
>                                                 SYMBOL "circle"
>
>                                                 SIZE 10
>
>                                                 COLOR 0 0 255
>
>                 END
>
>         END
>
> END #end of layer
>
>  
>
> END # end of mapfile
>
>  
>
> I'm sending WMS GetMap requests to the layer from my localhost and
> monitoring CPU on the server.
>
>  
>
> Every time I send a request the server CPU hits 100% until the request ends.
>
>  
>
> 1)      Is 640MB RAM enough to cope with this simple request?
>
> 2)      Is there anything wrong with the mapfile?
>
> 3)      Are there any methods I can use to tune this further?
>
>  
>
> Cheers,
>
>  
>
> Steve
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20080905/8a0be3b5/attachment.html
>
> ------------------------------
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
>
> End of mapserver-users Digest, Vol 8, Issue 18
> **********************************************
>   

-- 
Charles Theobald
NC Division of Water Resources
charles.theobald at ncmail.net
919-715-5425 




More information about the MapServer-users mailing list