[Mapserver-west] Re: postgis query size limit

[Steven Monai]

Ken-ichi:

There is possibly an upper limit to the length of the query string that
you're running afoul of, but I don't know what that limit might be.

The real problem is that your dataset is spread across two databases. One
trick to try is to use PHP (or whatever glue language you prefer) to suck
the desired keys out of the MySQL database and then inject them into a
temporary table in the pgsql database. Your pgsql query string can then use
an INNER JOIN with the temporary table to restrict its output set, rather
than using a massive, dynamically generated WHERE clause. This should keep
you well under any query string length limits.

Of course, you are then left with the troubles of: (1) cleaning up the
temporary table should a crash leave a set of keys in there; and also (2)
ensuring that concurrently-running queries don't interfere with each other.

(1) and (2) are left as exercises for the reader. ;-)

Regards,
-SM
--
==============================================
Steven Monai
Database & Application Developer
Spatial Mapping Ltd.
484 2nd Avenue
Prince George, BC, Canada
V2L 2Z7

http://www.spatialmapping.com
mailto:stevem at spatialmapping.com
phone: 250 564 1928
fax: 250 564 0751
==============================================


-----Original Message-----
Date: Thu, 12 May 2005 22:00:25 -0700
From: Ken-ichi <kenichi.ueda at gmail.com>
Subject: Re: [Mapserver-west] postgis query size limit
To: Tyler Mitchell <tylermitchell at shaw.ca>
Cc: mapserver-west at lists.maptools.org
Message-ID: <28af2c47f520908113dc5770e1c68a92 at gmail.com>
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed

Heh, yeah, I guess I'm a bit of a SQL idiot.  Thanks for the tip.   
However, it doesn't seem to keep the query quite short enough.   
500-2000 records is a lot of records.

-Ken-ichi

On May 12, 2005, at 9:39 PM, Tyler Mitchell wrote:

> I'm not familiar with this problem, but I wonder about a couple of
> things:
> -Are you really using the WHERE key=value1 OR key=value2..?  Won't 
> WHERE key IN (value1, value2, value3) do it for you?  It will at least 
> keep the query shorter and that might help you!?
>
> Hope you find an answer!
> Tyler
>
> On May 12, 2005 04:17 pm, Ken-ichi wrote:
>> Hi all.  I've been having trouble using very long PostGIS queries 
>> within MapServer.  I found this year-old email on the same topic
>>
>> http://lists.gis.umn.edu/pipermail/mapserver-users/2004-February/
>> 010233.htm
>> l
>>
>> and I was wondering if anyone knows if this problem has been fixed.
>>
>> My situation is as follows: I'm in an awkward bind in which all the 
>> non-spatial parts of a dataset reside in a MySQL database, and all 
>> the spatial stuff is in PostGIS.  When a user queries the MySQL db, 
>> they get back a bunch of unique record identifiers that can then be 
>> used to pull point data out of the PostGIS table by setting a really 
>> long WHERE clause (ie 'WHERE key=value1 OR key=value2 OR key=value3, 
>> etc).  All well and good, until I start trying to pull out more than 
>> about 400 records.  Then MapServer chokes and I get a 500 error.
>>
>> If anyone can think of a better way around this problem, I'm all ears.
>> Thanks.
>>
>> -Ken-ichi
>>
>> PS.  Anyone going to Open Source Geospatial '05?  I just registered.
>> _______________________________________________
>> Mapserver-west mailing list
>> Mapserver-west at lists.maptools.org
>> http://lists.maptools.org/mailman/listinfo/mapserver-west
>