[mapserver-users] Re: JOIN in a mapfile from a shapefile to a MySQL
database
Charles Theobald
charles.theobald at ncmail.net
Fri Sep 5 15:29:41 EDT 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