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.
[EMAIL PROTECTED] wrote:
Send mapserver-users mailing list submissions to
[email protected]
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
[EMAIL PROTECTED]
You can reach the person managing the list at
[EMAIL PROTECTED]
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" <[EMAIL PROTECTED]>
Subject: [mapserver-users] JOIN in a mapfile from a shapefile to a
MySQL database
To: [email protected]
Message-ID:
<[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]>
Subject: [mapserver-users] High CPU
To: "Mapserver-users" <[email protected]>
Message-ID: <[EMAIL PROTECTED]>
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
[email protected]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
End of mapserver-users Digest, Vol 8, Issue 18
**********************************************
--
Charles Theobald
NC Division of Water Resources
[EMAIL PROTECTED]
919-715-5425
_______________________________________________
mapserver-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
