[Mapserver-users] Virtual Spatial Data and SQL query

[ludovic gnemmi]

--- Frank Warmerdam <warmerdam at pobox.com> a écrit : >
ludovic gnemmi wrote:
> > Hi all,
> > I'm trying to set up a dynamic virtual spatial
> data
> > connection to map X,Y coordinates (from a Mysql
> odbc
> > connection) with OGR and phpmapscript.
> > It works with a simple sql query like:
> > <SrcSQL>SELECT * FROM table WHERE ...</SrcSQL>
> > but doesn't work with a more complicated query
> with
> > several tables and arithmetics operators (produce
> an
> > apache error).
> > I saw on the MapserverWiki:virtual spatial data
> > tutorial that the latest version of the OGR ODBC
> > driver passes the SQL directly to the ODBC driver.
> So
> > i don't undestand why my query doesn't work...ODBC
> > datasource is working properly, i've tested with
> > success this sort of query with a direct ODBC
> client
> > (Winsql-ODBC). Has anybody got an idea?
> > I'm working on windows with the last release of
> > mapserver-mapscript.
> > Thanks a lot.
> 
> Ludovic,
> 
> Have you tried your ovf file with ogrinfo.exe?  It
> might be easier to get
> respectible debug information this way.  Indeed the
> VRT layer does pass
> on SrcSQL directives to the underlying driver, and
> the OGR ODBC driver should
> just pass it off to the ODBC driver for the database
> in question.
> 
> 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    | Geospatial
> Programmer for Rent
> 
>  

Frank,
Thanks a lot for your answer.
Everything is ok when i try my ovf file with
ogrinfo.exe and when i use it with mapscript 
($layer->set("connection", "myovf.ovf")).even with a
big query..The problem appear when i use a connection
string instead of an ovf file with this sort of query
(need to build it dynamically).
It makes crash Apache...when i make shorter this query
mapserver return this error: Error in msBuildPath():
(SELECT...)path is too long.

Regards,

Ludovic



exple of connection string:
$str=
"<OGRVRTDataSource>
    <OGRVRTLayer name='batiment'>
        <SrcDataSource>ODBC:root at DSN</SrcDataSource> 
 		<SrcSQL>SELECT
 batiment.X_LambertIII,
 batiment.Y_LambertIII,
 db. *,
 coeffstructure.coeffMin * 1.25 AS coeffminstructure,
 coeffstructure.coeffMax * 1.25 AS coeffmaxstructure,
 coeffstructure.coeffMoy * 1.25 AS coeffmoystructure,
 coeffenvironnement.coeffMin * 0.75 AS coeffminenv,
 coeffenvironnement.coeffMax * 0.75 AS coeffmaxenv,
 coeffenvironnement.coeffMoy * 0.75 AS coeffmoyenv,
 coeffregelevation.coeffMin * 0.75 AS coeffminelev,
 coeffregelevation.coeffMax * 0.75 AS coeffmaxelev,
 coeffregelevation.coeffMoy * 0.75 AS coeffmoyelev,
 coefftoiture.coeffMin * 0.25 AS coeffmintoit, 
 coefftoiture.coeffMax * 0.25 AS coeffmaxtoit, 
 coefftoiture.coeffMoy * 0.25 AS coeffmoytoit, 
 coeffregplan.coeffMin * 0.5 AS coeffminplan, 
 coeffregplan.coeffMax * 0.5 AS coeffmaxplan, 
 coeffregplan.coeffMoy * 0.5 AS coeffmoyplan, 
 ( coeffstructure.coeffMin * 1.25 +
coeffenvironnement.coeffMin * 0.75 +
coeffregelevation.coeffMin * 0.75 +
coefftoiture.coeffMin * 0.25 + coeffregplan.coeffMin *
0.5 ) AS coeffmin, 
 ( coeffstructure.coeffMax * 1.25 +
coeffenvironnement.coeffMax * 0.75 +
coeffregelevation.coeffMax * 0.75 +
coefftoiture.coeffMax * 0.25 + coeffregplan.coeffMax *
0.5 ) AS coeffmax FROM batiment b, descriptionbatiment
db, coeffregplan, coeffregelevation, coefftoiture,
coeffenvironnement, coeffstructure 
WHERE 
coeffstructure.materiau = db.Construction AND 
coeffstructure.epoque = db.DateConstruction AND 
coeffenvironnement.natureTerrain = db.Rocher AND 
coeffenvironnement.penteTerrain = db.Terrain AND 
coeffenvironnement.materiau = db.Construction AND 
coeffenvironnement.nbEtages = db.Etage AND 
coefftoiture.toiture = db.Toiture AND 
coeffregelevation.materiau = db.Construction AND 
coeffregelevation.regEleve = db.RegulariteElevation
AND 
coeffregplan.regPlan = db.RegularitePlan AND 
coeffregplan.materiau = db.Construction AND 
db.id_batiment = batiment.id AND 
batiment.id_ZoneHomogene = 1</SrcSQL>
	<GeometryField encoding='PointFromColumns'
x='X_LambertIII' y='Y_LambertIII'/> 
    </OGRVRTLayer>
</OGRVRTDataSource>";



	

	
		
Vous manquez d’espace pour stocker vos mails ? 
Yahoo! Mail vous offre GRATUITEMENT 100 Mo !
Créez votre Yahoo! Mail sur http://fr.benefits.yahoo.com/

Le nouveau Yahoo! Messenger est arrivé ! Découvrez toutes les nouveautés pour dialoguer instantanément avec vos amis. A télécharger gratuitement sur http://fr.messenger.yahoo.com