[Mapserver-west] postgis query size limit

[Dean C. Mikkelsen]

Here is what I've found on MySQL.....I'm checking PostgreSQL. 

Take a look at the following link:

http://dev.mysql.com/doc/mysql/en/remote-connection.html

Note the MyODBC is needed as well. This is part of MySQL.

Dean



-----Original Message-----
From: Ken-ichi [mailto:kenichi.ueda at gmail.com] 
Sent: May 12, 2005 11:37 PM
To: Dean C. Mikkelsen
Cc: mapserver-west at lists.maptools.org
Subject: Re: [Mapserver-west] postgis query size limit

Interesting, but a cursory Googling seems to suggest that database  
links are specific to Oracle, but please correct me if I'm wrong  
because this sounds like it could solve my problem.

-Ken-ichi

On May 12, 2005, at 11:17 PM, Dean C. Mikkelsen wrote:

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