[mapserver-users] One-to-many JOIN works only on integer fields?
Hi list! I have a mapfile with a one-to-many JOIN, but PostgreSQL logs a syntax error whenever I query such an object on the map: - ERROR: syntax error at or near _3 at character 333 STATEMENT: SELECT codvia_num::text, cognome::text, nome::text, cf::text, data_nascita::text, indirizzo::text, interno::text, nr_civ_barrato::text, nr_fam::text, piano::text, scala::text, nr_ind::text, capofamiglia::text, codvia::text FROM anagrafe_territoriale.anagrafe_popolazione_con_codvia WHERE codvia_num = 323_3 -- The fields to join the two tables are named codvia_num and are text fields, but it queries them as if they were integer ones, without quoting! So I guess Mapserver understands only integer fields in the joins... Am I guessing right? And on the same topic, there's no support for a two-fields join, isn't it? Thank you in advance! Greetings Vito Meuli PS: Mapserver 5.5.2 on Linux PostgreSQL 8.3 ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
[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
[mapserver-users] ANGLE [attribute]
Hello everyone! I wonder if ANGLE accepts an attribute that comes from a table in a database, eg Oracle Spatial? Either he accepts only one attribute of a DBF? Thanks! -- Daiane ___ 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
Re: [mapserver-users] ANGLE [attribute]
Daiane Azevedo de Fraga wrote: Hello everyone! I wonder if ANGLE accepts an attribute that comes from a table in a database, eg Oracle Spatial? Either he accepts only one attribute of a DBF? Yes, that should work for all connection types (but I didn't test oracle spatial specifically myself). Daniel -- Daniel Morissette http://www.mapgears.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
[mapserver-users] Re: ODBC OGR MS SQL CONNECTION
Hi all, This now works, I've used * instead of listing columns.. SrcSQLSELECT * 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 And I'll try for ogr performance suggestions... Regards.. -- View this message in context: http://n2.nabble.com/ODBC-OGR-MS-SQL-CONNECTION-tp4824960p4829220.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
