[Mapserver-west] postgis query size limit

[Dean C. Mikkelsen]

I come from an Oracle background, but here is an idea, see if you can
follow me here. You have two databases DB1 and DB2. DB1 is MySQL and DB2
is PostGIS.

The first step is set up a VIEW into DB2. The VIEW is basically a SELECT
<COLUMN_NAME> FROM TABLE_NAME. You can also do SELECT * FROM TABLE_NAME,
but I assume you are only after the one identifier. You then SELECT
<COLUMN_NAME> FROM TABLE_NAME at DB_LINK - this becomes your VIEW.

>From within DB1 have the SELECT * FROM TABLE_NAME_ONE WHERE
<COLUMN_NAME> IN (SELECT <COLUMN_NAME> FROM VIEW_NAME WHERE <CONDITION
IS HERE>);

This requires that a Database link is created between the two databases.
So make sure that this link exists. This can also work between ODBC
connections.

Let me know if you need more information on this.

Cheers,
Dean



-----Original Message-----
From: mapserver-west-bounces at lists.maptools.org
[mailto:mapserver-west-bounces at lists.maptools.org] On Behalf Of Tyler
Mitchell
Sent: May 12, 2005 9:40 PM
To: mapserver-west at lists.maptools.org
Subject: Re: [Mapserver-west] postgis query size limit

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
_______________________________________________
Mapserver-west mailing list
Mapserver-west at lists.maptools.org
http://lists.maptools.org/mailman/listinfo/mapserver-west