[mapserver-users] ODBC OGR MS SQL CONNECTION
Dear all, This definition works, CONNECTIONTYPE OGR CONNECTION OGRVRTDataSource OGRVRTLayer name='Araclar' SrcDataSourceODBC:webuser/webu...@mssql/SrcDataSource SrcSQLSELECT TOP 10 * FROM MUGPSDATALOG/SrcSQL GeometryTypewkbPoint/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' SrcDataSourceODBC:webuser/webu...@mssql/SrcDataSource SrcSQLSELECT 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 GeometryTypewkbPoint/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... -- View this message in context: http://n2.nabble.com/ODBC-OGR-MS-SQL-CONNECTION-tp4824960p4824960.html Sent from the Mapserver - User mailing list archive at Nabble.com. ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] ODBC OGR MS SQL CONNECTION
ibrahimsaricicek wrote: 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' SrcDataSourceODBC:webuser/webu...@mssql/SrcDataSource SrcSQLSELECT 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 GeometryTypewkbPoint/GeometryType GeometryField encoding='PointFromColumns' x='LONGITUDE' y='LATITUDE'/GeometryField /OGRVRTLayer /OGRVRTDataSource But why? Ibrahim, I was unable to reproduce the problem parsing this XML VRT. I don't know what is going wrong. I had expected to find a sign in the sql that was not escaped properly as lt; but I see none. And why ogr connection is too slow? Is there a way for speeding-up? You will likely want to list the tables to be scanned on accessing the database in the connection string or else all are scanned for the schema - at great expense. Also, be aware that SrcSQL results are slow if used in random access mode - such as is done my MapServer's query mechanisms. That should not be a problem for simple drawing though. Best regards, -- ---+-- I set the clouds in motion - turn up | Frank Warmerdam, warmer...@pobox.com light and sound - activate the windows | http://pobox.com/~warmerdam and watch the world go round - Rush| Geospatial Programmer for Rent ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] ODBC OGR MS SQL CONNECTION
Some things to try: 1. Create a view in MS SQL to do most of SQL 2. List the view name in the SrcDataSource: SrcDataSourceODBC: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' SrcDataSourceODBC:webuser/webu...@mssql/SrcDataSource SrcSQLSELECT TOP 10 * FROM MUGPSDATALOG/SrcSQL GeometryTypewkbPoint/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' SrcDataSourceODBC:webuser/webu...@mssql/SrcDataSource SrcSQLSELECT 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 GeometryTypewkbPoint/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