Hello Bart!<br>Following your advice, I have installed MySQL and downloaded one public access shapefile, to let you (and everybody who wants to try this test) reproduce the results<br><br><br>Download it from <a href="http://edcftp.cr.usgs.gov/pub/data/nationalatlas/hydrogm020.tar.gz">http://edcftp.cr.usgs.gov/pub/data/nationalatlas/hydrogm020.tar.gz</a> that have 2 shapefiles : lines (rivers) and polygons (lakes). I'm going to use hydrogl020.shp :<br>
<br>D:\ms4w\apps\maps\data\test\hydrogm020>ogrinfo -so -al hydrogl020.shp<br>INFO: Open of `hydrogl020.shp'<br> using driver `ESRI Shapefile' successful.<br><br>Layer name: hydrogl020<br>Geometry: Line String<br>
Feature Count: 76975<br>Extent: (-179.998245, 17.674692) - (179.983139, 71.398186)<br>Layer SRS WKT:<br>(unknown)<br>FNODE_: Integer (9.0)<br>TNODE_: Integer (9.0)<br>LPOLY_: Integer (9.0)<br>RPOLY_: Integer (9.0)<br>LENGTH: Real (13.11)<br>
HYDROGM020: Integer (9.0)<br>FEATURE: String (80.0)<br>F_CODE: Integer (2.0)<br>NAME: String (80.0)<br>STATE: String (5.0)<br>STATE_FIPS: String (5.0)<br>NAME_UC: String (80.0) <br> <br>Let's create qix index:<br>
D:\ms4w\apps\maps\data\test\hydrogm020>shptree hydrogl020<br>creating index of new LSB format<br><br><br>I'm going to test the shapefile, then I will convert it to a MySQL table and I will do the tests again<br><br>
<br>SHAPEFILE<br>3 tests, let's use shp2img that skips apache, http requests, etc. Only one request in the command line.<br><br>a1) No data access<br>a2) Data access, only used in expressions to change the line color, no labeling<br>
a3) Data access only used for labeling<br><br><br><br>--------------------------- <br>--------- TEST A1 ---------<br>---------------------------<br>Shapefile Map without data access<br><br>MAP<br> NAME "US RIVERS"<br>
SIZE 800 600<br> IMAGETYPE PNG<br> EXTENT -179.998245 17.674692 179.983139 71.398186<br> <br> <br> PROJECTION<br> "init=epsg:4269"<br> END<br><br> LAYER<br> NAME "hydrogl020"<br>
STATUS OFF<br> TYPE LINE<br> DATA "hydrogl020"<br> CLASS <br> STYLE<br> COLOR 0 0 0<br> END<br> END<br> END<br>END<br><br>Result Test A1:<br>
--------------<br>D:\ms4w\apps\maps\data\test\hydrogm020>shp2img -m d:\ms4w\apps\maps\data\test\hydrogm020\a1.map -o D:\ms4w\apps\maps\data\test\hydrogm020\a1.png -e -179.998245 17.674692 -60.0 71.398186 -t -l hydrogl020 -i PNG -all_debug 2<br>
msDrawMap(): Layer 0 (hydrogl020), 1.328s<br>msDrawMap(): Drawing Label Cache, 0.000s<br>msDrawMap() total time: 1.328s<br><br><br><br>--------------------------- <br>--------- TEST A2 ---------<br>---------------------------<br>
Shapefile Map with data access in expression<br><br>MAP<br> NAME "US RIVERS"<br> SIZE 800 600<br> IMAGETYPE PNG<br> EXTENT -179.998245 17.674692 179.983139 71.398186<br> <br> <br> PROJECTION<br>
"init=epsg:4269"<br> END<br><br> LAYER<br> NAME "hydrogl020"<br> STATUS OFF<br> TYPE LINE<br> DATA "hydrogl020"<br> CLASS<br> EXPRESSION([F_CODE] = 4)<br>
STYLE<br> COLOR 0 0 0<br> END<br> END <br> CLASS<br> STYLE<br> COLOR 255 0 0<br> END<br> END <br> END<br>END<br>
<br><br>Result Test A2:<br>---------------<br>D:\ms4w\apps\maps\data\test\hydrogm020>shp2img -m d:\ms4w\apps\maps\data\test\hydrogm020\a2.map -o D:\ms4w\apps\maps\data\test\hydrogm020\a2.png -e -179.998245 17.674692 -60.0 71.398186 -t -l hydrogl020 -i PNG -all_debug 2<br>
msDrawMap(): Layer 0 (hydrogl020), 6.250s<br>msDrawMap(): Drawing Label Cache, 0.000s<br>msDrawMap() total time: 6.250s<br><br><br>--------------------------- <br>--------- TEST A3 ---------<br>---------------------------<br>
Shapefile Map with data access<br><br><br>MAP<br> NAME "US RIVERS"<br> SIZE 800 600<br> IMAGETYPE PNG<br> EXTENT -179.998245 17.674692 179.983139 71.398186<br> <br> <br> PROJECTION<br> "init=epsg:4269"<br>
END<br><br> LAYER<br> NAME "hydrogl020"<br> STATUS OFF<br> TYPE LINE<br> DATA "hydrogl020"<br> LABELITEM "STATE"<br> CLASS <br> STYLE<br>
COLOR 0 0 0<br> END<br> LABEL<br> COLOR 51 51 51<br> END <br> END <br> END<br>END<br><br>Result Test A3:<br>--------------<br>D:\ms4w\apps\maps\data\test\hydrogm020>shp2img -m d:\ms4w\apps\maps\data\test\hydrogm020\a3.map -o D:\ms4w\apps\maps\data\test\hydrogm020\a3.png -e -179.998245 17.674692 -60.0 71.398186 -t -l hydrogl020 -i PNG -all_debug 2<br>
msDrawMap(): Layer 0 (hydrogl020), 83.203s<br>msDrawMap(): Drawing Label Cache, 0.703s<br>msDrawMap() total time: 83.906s<br><br><br><br>Ok, a2 shows that using F_CODE to determine the color of the line is not really very hard to mapserver but a3 shows that labeling is very hard to MapServer.<br>
It's time to repeat the tests with MySQL Spatial Data. <br>Export shapefile to MySQL:<br>----------------<br>D:\ms4w\apps\maps\data\test\hydrogm020>ogr2ogr -f MySQL MySQL:maps,host=localhost,user=mapserver,password=mapserver hydrogl020.shp -nln hydrogl020 -update -overwrite -lco engine=MYISAM<br>
<br><br>Repeat the 3 tests<br><br>--------------------------- <br>--------- TEST B1 ---------<br>---------------------------<br>OGR MySQL Map without data access<br><br><br>MAP<br> NAME "US RIVERS"<br> SIZE 800 600<br>
IMAGETYPE PNG<br> EXTENT -179.998245 17.674692 179.983139 71.398186<br> <br> <br> PROJECTION<br> "init=epsg:4269"<br> END<br><br> LAYER<br> NAME "hydrogl020"<br> STATUS OFF<br>
TYPE LINE<br> CONNECTIONTYPE OGR<br> CONNECTION "MySQL:maps,host=localhost,user=mapserver,password=mapserver"<br> DATA "hydrogl020"<br> CLASS <br> STYLE<br>
COLOR 0 0 0<br> END<br> END<br> END<br>END<br><br><br>Result Test B1:<br>---------------<br>D:\ms4w\apps\maps\data\test\hydrogm020>shp2img -m d:\ms4w\apps\maps\data\test\hydrogm020\b1.map -o D:\ms4w\apps\maps\data\test\hydrogm020\b1.png -e -179.998245 17.674692 -60.0 71.398186 -t -l hydrogl020 -i PNG -all_debug 2<br>
msOGRFileOpen(MySQL:maps,host=localhost,user=mapserver,password=mapserver)...<br>OGROPen(MySQL:maps,host=localhost,user=mapserver,password=mapserver)<br>msOGRLayerClose(MySQL:maps,host=localhost,user=mapserver,password=mapserver).<br>
msOGRFileClose(MySQL:maps,host=localhost,user=mapserver,password=mapserver,1).<br>msDrawMap(): Layer 0 (hydrogl020), 4.172s<br>msDrawMap(): Drawing Label Cache, 0.000s<br>msDrawMap() total time: 4.188s<br><br><br>--------------------------- <br>
--------- TEST B2 ---------<br>---------------------------<br>OGR MySQL Map with data access in expression<br><br>MAP<br> NAME "US RIVERS"<br> SIZE 800 600<br> IMAGETYPE PNG<br> EXTENT -179.998245 17.674692 179.983139 71.398186<br>
<br> <br> PROJECTION<br> "init=epsg:4269"<br> END<br><br> LAYER<br> NAME "hydrogl020"<br> STATUS OFF<br> TYPE LINE<br> CONNECTIONTYPE OGR<br> CONNECTION "MySQL:maps,host=localhost,user=mapserver,password=mapserver"<br>
DATA "hydrogl020"<br> CLASS<br> EXPRESSION([F_CODE] = 4)<br> STYLE<br> COLOR 0 0 0<br> END<br> END <br> CLASS<br> STYLE<br>
COLOR 255 0 0<br> END<br> END <br> END<br>END<br><br><br>Result Test B2:<br>---------------<br>D:\ms4w\apps\maps\data\test\hydrogm020>shp2img -m d:\ms4w\apps\maps\data\test\hydrogm020\b2.map -o D:\ms4w\apps\maps\data\test\hydrogm020\b2.png -e -179.998245 17.674692 -60.0 71.398186 -t -l hydrogl020 -i PNG -all_debug 2<br>
msOGRFileOpen(MySQL:maps,host=localhost,user=mapserver,password=mapserver)...<br>OGROPen(MySQL:maps,host=localhost,user=mapserver,password=mapserver)<br>msOGRLayerClose(MySQL:maps,host=localhost,user=mapserver,password=mapserver).<br>
msOGRFileClose(MySQL:maps,host=localhost,user=mapserver,password=mapserver,1).<br>msDrawMap(): Layer 0 (hydrogl020), 4.766s<br>msDrawMap(): Drawing Label Cache, 0.000s<br>msDrawMap() total time: 4.766s<br><br><br><br>--------------------------- <br>
--------- TEST B3 ---------<br>---------------------------<br>OGR MySQL Map with data access for labeling<br><br>MAP<br> NAME "US RIVERS"<br> SIZE 800 600<br> IMAGETYPE PNG<br> EXTENT -179.998245 17.674692 179.983139 71.398186<br>
<br> <br> PROJECTION<br> "init=epsg:4269"<br> END<br><br> LAYER<br> NAME "hydrogl020"<br> STATUS OFF<br> TYPE LINE<br> CONNECTIONTYPE OGR<br> CONNECTION "MySQL:maps,host=localhost,user=mapserver,password=mapserver"<br>
DATA "hydrogl020"<br> LABELITEM "STATE"<br> CLASS <br> STYLE<br> COLOR 0 0 0<br> END<br> LABEL<br> COLOR 51 51 51<br>
END <br> END <br> END<br>END<br><br><br>Result Test B3:<br>---------------<br>D:\ms4w\apps\maps\data\test\hydrogm020>shp2img -m d:\ms4w\apps\maps\data\test\hydrogm020\b3.map -o D:\ms4w\apps\maps\data\test\hydrogm020\b3.png -e -179.998245 17.674692 -60.0 71.398186 -t -l hydrogl020 -i PNG -all_debug 2<br>
msOGRFileOpen(MySQL:maps,host=localhost,user=mapserver,password=mapserver)...<br>OGROPen(MySQL:maps,host=localhost,user=mapserver,password=mapserver)<br>msOGRLayerClose(MySQL:maps,host=localhost,user=mapserver,password=mapserver).<br>
msOGRFileClose(MySQL:maps,host=localhost,user=mapserver,password=mapserver,1).<br>msDrawMap(): Layer 0 (hydrogl020), 83.361s<br>msDrawMap(): Drawing Label Cache, 0.781s<br>msDrawMap() total time: 84.142s<br><br><br><br>So, I think that MySQL doesn't solve the question, it seems to be a labeling issue. Could it be so hard to render the labels? I repeated the A3 and B3 tests including FORCE ON in the LABEL object<br>
to avoid MapServer to calculate anything about lengths, the result was the same. How is it possible? <br>I don't know if this is normal MapServer behavior and the cost of render labels is so high!<br><br>Thank you very much in advance for your answers<br>
Sergio<br><br><br><br><br><div><span class="gmail_quote">2008/2/22, Desarrollo Desarrollo <<a href="mailto:devmapserver@gmail.com">devmapserver@gmail.com</a>>:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Thank you very much for your answer,<br><br>Therefore, Should I think that Shapefiles / DBF data is not suitable for MapServer if the number of features in the shapefile is a "big" number like 473815 line strings in the Streets.shp? I've tried with MajHwys.shp<br>
(35295 linestrings features) labeling and using expressions and works ok. Streets.shp with its 473815 linestrings is rendered fast if there is not DBF access (not labeling neither expressions), but when DBF access is needed Mapservers hangs up!<br>
Will MySQL spatial data increase the speed and number of features that MapServer can manage?<br><br>Best regards<br><br><br><div><span class="gmail_quote">2008/2/21, Bart van den Eijnden (OSGIS) <<a href="mailto:bartvde@osgis.nl" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">bartvde@osgis.nl</a>>:</span><div>
<span class="e" id="q_1184018ee81d3dc2_1"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
It is because Mapserver cannot use any attribute indexes on the DBF.<br> <br> Put your shapefile in a spatial database like PostGIS I would suggest and<br> create attribute indexes.<br> <br> Best regards,<br> Bart<br> <br>
--<br> Bart van den Eijnden<br> OSGIS, Open Source GIS<br> <a href="http://www.osgis.nl" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://www.osgis.nl</a><br> <br> <br> --------- Oorspronkelijk bericht --------<br>
Van: Paul Spencer <<a href="mailto:pspencer@dmsolutions.ca" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">pspencer@dmsolutions.ca</a>><br>
Naar: Desarrollo Desarrollo <<a href="mailto:devmapserver@gmail.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">devmapserver@gmail.com</a>><br> Cc: <a href="mailto:mapserver-users@lists.osgeo.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">mapserver-users@lists.osgeo.org</a><br>
Onderwerp: Re: [mapserver-users] MapServer consuming 100% CPU<br>
Datum: 21/02/08 11:09<br> <br><br> > See:<br> ><br> > <a href="http://mapserver.gis.umn.edu/docs/howto/mapfiletuning" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://mapserver.gis.umn.edu/docs/howto/mapfiletuning</a><br>
> <a href="http://mapserver.gis.umn.edu/docs/howto/optimizevector" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://mapserver.gis.umn.edu/docs/howto/optimizevector</a><br>
> <a href="http://mapserver.gis.umn.edu/docs/howto/tileindex" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://mapserver.gis.umn.edu/docs/howto/tileindex</a><br> ><br> > Cheers<br> ><br>
> Paul<br> ><br> > On 21-Feb-08, at 2:56 AM, Desarrollo Desarrollo wrote:<br>
><br> <br>> > Hi,<br> > ><br> > > I am developing an application with MapServer v2.2.7 with<br> > > Apache on Windows XP, but I have a serious problem, below I<br> > > describe it:<br>
> ><br> > > Using Navteq demo maps from Paris, the Streets.shp have around<br> > > 500000 line strings features, when mapserver renders the layer<br> > > without using DBF data (only draw lines) everything is OK and fast,<br>
<br> > > but when mapserver needs to access to the DBF to render labels or<br> > > use expressions to filter data, mapserver starts to consuming 100%<br> > > CPU and nothing is rendered after to spends a long time.<br>
> ><br> > > Using Teleatlas demo maps for a medium city of Spain, the Street.shp<br> <br> > > with around 11000 line strings features doesn't show any trouble and<br> <br> > > mapserver renders the images OK and fast even accessing to DBF to<br>
> > render labels<br> > ><br> > > Could somebody help me with this problem? Why does it happen? How do<br> <br> > > I solve it?<br> > ><br> > ><br> > > Thank you very much.<br>
> > _______________________________________________<br> > > mapserver-users mailing list<br> > > <a href="mailto:mapserver-users@lists.osgeo.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">mapserver-users@lists.osgeo.org</a><br>
> > <a href="http://lists.osgeo.org/mailman/listinfo/mapserver-users" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
<br>><br> ><br> > __________________________________________<br> ><br> > Paul Spencer<br> > Chief Technology Officer<br> > DM Solutions Group Inc<br> > <a href="http://www.dmsolutions.ca/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://www.dmsolutions.ca/</a><br>
><br> > _______________________________________________<br> > mapserver-users mailing list<br> > <a href="mailto:mapserver-users@lists.osgeo.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">mapserver-users@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/mailman/listinfo/mapserver-users" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
><br> ><br> <br> <br> <br> </blockquote></span></div></div><br>
</blockquote></div><br>