[Mapserver-users] Virtual Spatial Data and SQL query

[ludovic gnemmi]

--- ludovic gnemmi <l_gnemmi at yahoo.fr> a écrit : > ---
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>";
> 
>
Frank,
I finally succeeded to pass my query with the use of
tables aliases. Thanks a lot for your help.
Ludovic


	
	
		
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