Re: [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/webu...@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/webu...@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/webu...@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...

_______________________________________________
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users