[Tilecache] More CustomService.py Questions

[Christopher Schmidt]

On Fri, Mar 13, 2009 at 04:04:42PM -0500, Brian Walawender wrote:
> Ok - I've designed a service that reads the configuration information from a
> Postgres database.  But now I've seem introduced a new bottleneck.  My table
> has 20,500 rows.  Any suggestions to my code below?  It seems that I am
> quickly maxing out the connections (or at least really slowing down the
> database).

I expect that, as someone else suggested, you simply don't have an 
index on your map_id column. At the psql prompt, try:

  create index map_id_idx on (map_id);

It should take about 10s, and your queries after that should be nearly
instant.

I'm assuming that this is running under mod_python? Otherwise, you could
have a problem with the imports being slow.

Also, is your database local or remote? If it's remote and high latency,
the setup time to talk to the database could also be a problem.

Also, you should do this differently:

>   sqlCMD = "SELECT wms_layer, wms_url, extension, bbox, max_resolution,
> debug, srs FROM tilecache_config WHERE map_layer='"
>   sqlCMD = sqlCMD + map_layer + "'" 

sqlCMD = "SELECT wms_layer, wms_url, extension, bbox, max_resolution,
          debug, srs FROM tilecache_config WHERE map_layer=%"

cursor.execute(sqlCMD, (map_layer,))

This prevents sql injection attacks by someone typing 'layer='; drop
database;'

-- Chris

> #!/usr/bin/python
>  
> # This example code demonstrates how you can construct
> # a custom service as an alternative to the config file.
> # This allows you to, for example, determine what layers to
> # load based on request parameters or something similar --
> # the config file based loading technique is handy, but 
> # probably doesn't solve all problems.
>  
> from TileCache.Service import Service, modPythonHandler
> from TileCache.Caches.Disk import Disk
> import TileCache.Layers.WMS as WMS
> import re
> import psycopg2
> import sys
>  
> def handler (req):
>  
>   url_args = req.args
>   tmp_str = url_args.upper()
>   if tmp_str.find("LAYERS=") > -1:
>     argList = url_args.split("&")
>     map_layer = argList[0]
>     map_layer = map_layer[7:]
>   else:
>     url_args = req.unparsed_uri
>     paramList = url_args.split("/")
>     map_layer = paramList[4]
>  
>   try:
>     conn = psycopg2.connect("dbname='gis' user='user' host='123.456.789'
> password=''pass"); 
>   except:
>     sys.exit()
>  
>   sqlCMD = "SELECT wms_layer, wms_url, extension, bbox, max_resolution,
> debug, srs FROM tilecache_config WHERE map_layer='"
>   sqlCMD = sqlCMD + map_layer + "'" 
>  
>   cur = conn.cursor()
>   cur.execute(sqlCMD)
>   row = cur.fetchone()
>   conn.close()
>  
>   wms_layer = row[0]  
>   wms_url = row[1]
>   myExtension = row[2]
>   myBBox = row[3]
>   myMaxRes = row[4]
>   myDebug = row[5]
>   mySRS = row[6]
>  
>   myService = Service (
>     Disk("/www/html/ridge2/tilecache"),
>     {
>       map_layer       : WMS.WMS( map_layer,
>                     layers = wms_layer, 
>                     url = wms_url,
>                     extension = myExtension,
>                     bbox = myBBox, 
>                     maxResolution= myMaxRes,
>                     debug = myDebug,
>                     srs= mySRS),
>      }
>   )
>  
>   return modPythonHandler(req, myService)
>  
> _________________________________________________
> Brian Walawender
> Technique Development Meteorologist
> Scientific Services Division - Central Region Headquarters
> 816-268-3114 - Office
> 816-805-6497 - Cell
>  

> _______________________________________________
> Tilecache mailing list
> Tilecache at openlayers.org
> http://openlayers.org/mailman/listinfo/tilecache


-- 
Christopher Schmidt
MetaCarta