[mapserver-users] RE: MySQL data

[Kent Morrison]

Thanks for the reply. 

Below is the information used to generate tiles from TigerLine data for 1 county.. The tiles are generated but the perfomance is excutiatinly slow. This is a test set of data.

I guess what I need is some guidance on using mapserver with MySQL and how to optimize the performance. Ultimately my tables will contain millions of rows of data pulled from difference sources.



Versions:
Mysql: 5.1.41
Mapserver: using  MS4W is version 3.0 beta 11  install

Access method: ? (see map file text below)

Spacial data: MySQL geometries

Map File:
MAP                                              
                                                 
  IMAGETYPE      AGG                             
  SIZE           256 256                         
  SHAPEPATH      "/mapdata/shapefiles" 
  IMAGECOLOR     255 255 255                     
  TRANSPARENT    ON                              
  FONTSET        "../fonts/fonts.list"      
                      
  EXTENT -95.909985 29.523624 -95.014496 30.110732
   
  CONFIG PROJ_LIB "c:\ms4w\proj\nad\"  
                   

CONFIG MS_ERRORFILE "c:\ms4w\tmp\errors.txt"                         
DEBUG 2
      
  PROJECTION             
     "init=epsg:4326"  
  END                    
                         
  OUTPUTFORMAT           
    NAME 'AGG'           
    DRIVER AGG/PNG       
    IMAGEMODE RGB        
  END #Outputformat      
      
  LAYER    
    DEBUG ON                              
    
    NAME        roads
    STATUS	ON
    CONNECTIONTYPE OGR

    CONNECTION	"MYSQL:revgeotest,host=tws1r-tj,user=*****,password=*****,port=3306,tables=tl_2009_48201_edges"
    DATA	"SELECT SHAPE as the_geom, fullname FROM tl_2009_48201_edges"

    TYPE        LINE                  
    
   CLASS
	STYLE
		COLOR 	0 0 0
		WIDTH 	8
	END #Style
    END #End Class    
                                                
  END #layer
                   
   
                           
END #Map

-----Original Message-----
From: Lime, Steve D (DNR) [mailto:Steve.Lime at state.mn.us] 
Sent: Friday, June 18, 2010 8:39 AM
To: Kent Morrison; 'mapserver-users at lists.osgeo.org'
Subject: RE: MySQL data

Hi Kent: Probably need more information regarding:

  - versions
  - access method (are you using GDAL?)
  - what type of spatial data (MySQL geometries vs points in x/y columns)
  - etc...

Steve
________________________________________
From: mapserver-users-bounces at lists.osgeo.org [mapserver-users-bounces at lists.osgeo.org] On Behalf Of Kent Morrison [KMorrison at advantrack.com]
Sent: Thursday, June 17, 2010 2:13 PM
To: 'mapserver-users at lists.osgeo.org'
Subject: [mapserver-users] MySQL data

I have data stored in MySQL. When I configure the map file to pull from a small subset of the data, tiles are generated correctly. If I change the map file to point to the larger master table, map server returns "CGI Timeout". I dont't really want to breakdown the master data table into hundreds of smaller tables. I need additional informaiton and/or suggestions on how to get mapserver to work with millions of rows of data in MySQL.

Kent W. Morrison
Software Development Manager
Advanced Tracking Technologies, Inc.
6001 Savoy, Suite 600, Houston Texas 77036
(W) 713-353-6065 (C) 832-434-8175
<mailto:kmorrison at advantrack.com>