mapjoin : msPOSTGRESQLJoinClose & Fastcgi & OGR ODBC Connection

[Frank Warmerdam]

Fabio D'Ovidio wrote:
...
>             CONNECTIONTYPE OGR
>   CONNECTION "pug_iffi.shp"
>  DATA "SELECT * from pug_iffi c LEFT JOIN 
> 'ODBC:swra at CartaewnetIFFI,Query_prova_IdFrana'.Query_prova_IdFrana b ON 
> c.IDFRANA = b.IDFRANA"
>   
...
> There are two type of problems :
> 
> one :
> 
> I have Timeout error of CGI again even if  FASTCGI is activated. I think 
> the problem is that SQL SERVER with OGR ODBC Connection downloads all of 
> the records in the table I need and after it makes SQL SELECT.

Fabio,

In the above scenario I think OGR will select all the features in
pug_iffi.shp that intersect the current spatial region.  Then it will
issue a SELECT request against the ODBC data source once for each of
those shapes to fetch the record to join to it.  I am guessing that
there are a lot of features this is being done for and that things are
timing out while it chugs away at it.

It is also possible that the spatial region isn't being taken into account
in the original select against the shapefile, though I am reasonably
confident that it is.

> two :
> 
> If I try to do this in the Layer Object :
> 
> ...
> DATA "SELECT id from 'ODBC:sa at CartanetIFFI,Query_IdFrana'.Query_IdFrana 
> WHERE subid = 04"
> ....
> 
> or :
> 
> ...
> DATA "SELECT IDFRANA from pug_iffi c"
> ...
> 
> I have the following error with a simple GetFeatureInfo Request :
> 
> GetFeatureInfo results:
> 
>   Search returned no results.
> msPOSTGRESQLJoinClose() already close or never opened.
> msPOSTGRESQLJoinClose() already close or never opened.
> 
> "Search returned no results" it's OK for me because the table is not a 
> Geometry, but Why I have "msPOSTGRESQLJoinClose()" ERROR if my 
> connection is to DBF or to SQL Server ??

Good question.  In this case are you using a "mapserver" style JOIN
(as opposed to an OGR join)?  This seems like it may be a bug and
if we can isolate it in an easier to reproduce situation with 4.10.0RC1
perhaps we should be looking at fixing it.


> I'd like to know how can download ONLY the Selection in my SQL Server 
> without problem with TIMEOUT CGI (with or without FASTCGI support ? )

Well, if you can load the shapefile (pug_iffi) into SQL server in
some fashion, then you could do the join there.  In the case where you
did the following:

 >             CONNECTIONTYPE OGR
 >   CONNECTION "pug_iffi.shp"
 > DATA "SELECT id from 'ODBC:sa at CartanetIFFI,Query_IdFrana'.Query_IdFrana
 > WHERE subid = 04"

I am guessing you were just trying to fetch simple attribute records
from SQL server.  I think this would be better expressed as:

  CONNECTIONTYPE OGR
  CONNECTION "ODBC:sa at CartanetIFFI,Query_IdFrana"
  DATA "select id from Query_IdFrana WHERE subid = 04"

Overall, the lesson is that the OGR join mechanism can have severe
performance issues and that if possible you are better putting your
spatial and non-spatial data in one RDBMS when you need to relate them.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | President OSGeo, http://osgeo.org