[mapserver-users] ODBC OGR MS SQL CONNECTION

[Brent Fraser]

Some things to try:

1. Create a view in MS SQL to do most of SQL

2. List the view name in the SrcDataSource:
  <SrcDataSource>ODBC:webuser/webuser at MsSql,MyView</SrcDataSource>
so ogr/odbc/sqlserver doesn't have to enumerate all the db objects

3. Using <SrcSQL> means that GDAL/ogr/mapserver is not using any spatial 
indexing, and potentially all records are being returned from the database to 
mapserver which then looks at the coordinates and decides which ones to render. 
This was a real performance killer for me (as the database was returning 500k 
records on every pan and zoom).  I had to add my own spatial filter as a WHERE 
clause:

<SrcSQL>
     SELECT * FROM  MyView
     WHERE
	Longitude &gt; %VIEW_MIN_LONG% AND
	Longitude &lt; %VIEW_MAX_LONG% AND
	Latitude  &gt; %VIEW_MIN_LAT%  AND
	Latitude  &lt; %VIEW_MAX_LAT%
</SrcSQL>

and pass the VIEW_MIN_LONG,etc as CGI variables to have mapserver do the 
substitution.

Fortunately I was able to modify the client-side Javascript toolkit (GeoMoose) 
to calculate the values from the project map extent.

Best Regards,
Brent Fraser


ibrahimsaricicek wrote:
> Dear all,
> 
> This definition works,
>  
>  CONNECTIONTYPE OGR
>   CONNECTION "
>   <OGRVRTDataSource>
>     <OGRVRTLayer name='Araclar'>
>         <SrcDataSource>ODBC:webuser/webuser at MsSql</SrcDataSource>
>         <SrcSQL>SELECT TOP 10 * FROM MUGPSDATALOG</SrcSQL>
>         <GeometryType>wkbPoint</GeometryType>
>         <GeometryField encoding='PointFromColumns' x='LONGITUDE'
> y='LATITUDE'></GeometryField>
>     </OGRVRTLayer>
> </OGRVRTDataSource>"
> 
> This doesn't and syas; msDrawMap(): Image handling error. Failed to draw
> layer named 'Araclar'. msOGRFileOpen(): OGR error. Open failed for OGR
> connection in layer `Araclar'. Parse error at EOF, not all elements have
> been closed, starting with SrcSQL 
> 
> CONNECTIONTYPE OGR
>   CONNECTION "
>   <OGRVRTDataSource>
>     <OGRVRTLayer name='Araclar'>
>         <SrcDataSource>ODBC:webuser/webuser at MsSql</SrcDataSource>
>         <SrcSQL>SELECT MOBILEUNIT.MUID, MOBILEUNIT.PLATE,
> MUGPSDATALOG.LONGITUDE, MUGPSDATALOG.LATITUDE FROM MOBILEUNIT INNER JOIN
> MUGPSDATALOG ON MOBILEUNIT.MUID=MUGPSDATALOG.MUID WHERE
> MUGPSDATALOG.EVENTLOGID IN(SELECT MAX(EVENTLOGID) FROM MUFLEET INNER JOIN
> MUGROUP ON MUFLEET.FLEETID = MUGROUP.FLEETID INNER JOIN MOBILEUNIT ON
> MUGROUP.GROUPID = MOBILEUNIT.GROUPID INNER JOIN MUGPSDATALOG ON
> MOBILEUNIT.MUID=MUGPSDATALOG.MUID where MUFLEET.FLEETID=37144 group by
> MOBILEUNIT.MUID) ORDER BY MOBILEUNIT.PLATE</SrcSQL>
>         <GeometryType>wkbPoint</GeometryType>
>         <GeometryField encoding='PointFromColumns' x='LONGITUDE'
> y='LATITUDE'></GeometryField>
>     </OGRVRTLayer>
> </OGRVRTDataSource>"
> 
> But why?
> 
> And why ogr connection is too slow? Is there a way for speeding-up?
> 
> Regards...