RE: [mapserver-users] Mapserver and Microsoft SQL Server 2008
Thanks for the reply, I did notice the geometry_columns table was missing and at first when I added it, I got SQL errors, but after a few tweaks, I no longer get the errors when I have a geometry_columns table in my database. I narrowed the error down to the layer.open() command, and found where the error occurs in the source code of the sql server plugin dll. For some reason it cannot locate the geometry column in either the table itself or the geometry_columns table, even though both are specified as 'the_geom'. I'm looking into the security now, I did notice that I could not set the owner of the geometry_columns table... CREATE TABLE geometry_columns OWNER TO owner, SQL isn't liking the 'OWNER' part... Otherwise I'll have another look at the webserver permissions, but those have never been a problem... At a bit of a loss here and running out of ideas... Thanks Chris -Original Message- From: Brent Wood [mailto:[EMAIL PROTECTED] Sent: 11 April 2008 10:49 PM To: Chris King Subject: Re: [mapserver-users] Mapserver and Microsoft SQL Server 2008 --- Chris King <[EMAIL PROTECTED]> wrote: > Hi > > I manually imported my data from postgre into SQL 2008 and the map > displays fine. I am now executing a the layer.queryByPoint () command > followed by the > layer.getResults() command at which point the program fails and gives > the following error: > > msMSSQL2008LayerGetItems(): Query error. msMSSQL2008LayerGetItems: > tried to find the geometry column in the results from the database, > but couldnt find it. Is it miss-capitialized? 'the_geom' > > I know when you create the tables in the postgre database you > explicitly add the geometry column after the table creation I'm guessing here, never having used SQL Server This is like it is with PostGIS because PostGIS is not fully integrated with Postgres, but a third party addon. In SQL Sever 2008 I would assume you'd use "alter table add ..." to add a new geometry column, like you do for any other native datatype. Assuming MS actually followed someone else's standard for a change, your database should have a geometry_columns table with the metadata of your geometry column & its table there. See if that all seems OK first. My guess is that mapserver will be reading some info about the geometry from there... So your first check should be that the geometry column exists in both the table you created & the geometry_columns table. Other than that, I can only think of a permissions problem somewhere? Is the web server user running the mapserver cgi able to access the SQL db/table OK?? Someone who has more SQL Server expertise will have to advise any further :-) HTH, Brent Wood > > ie: SELECT > AddGeometryColumn('','ct_main_points','the_geom','-1','POINT',2); > > when I tried this in SQL 2008 the function was not recognized and I > cannot find the SQL equivalent function. So I simply added the > geometry column when I created the table, now I have a feeling that is > what is causing this error. > > If anyone has seen this error or knows of the AddGeometryColumn SQL > 2008 equivalent function, please could you let me know. > > Thanks > Chris > > ___ > mapserver-users mailing list > mapserver-users@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/mapserver-users > ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Mapserver and Microsoft SQL Server 2008
Hi I manually imported my data from postgre into SQL 2008 and the map displays fine. I am now executing a the layer.queryByPoint () command followed by the layer.getResults() command at which point the program fails and gives the following error: msMSSQL2008LayerGetItems(): Query error. msMSSQL2008LayerGetItems: tried to find the geometry column in the results from the database, but couldnt find it. Is it miss-capitialized? 'the_geom' I know when you create the tables in the postgre database you explicitly add the geometry column after the table creation ie: SELECT AddGeometryColumn('','ct_main_points','the_geom','-1','POINT',2); when I tried this in SQL 2008 the function was not recognized and I cannot find the SQL equivalent function. So I simply added the geometry column when I created the table, now I have a feeling that is what is causing this error. If anyone has seen this error or knows of the AddGeometryColumn SQL 2008 equivalent function, please could you let me know. Thanks Chris ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
RE: [mapserver-users] Mapserver and Microsoft SQL Server 2008
Hi: Let´s face the challenge! the big question here is: it is posible to tile geodata stored in spatial SQL ?? Here goes my first aproach: divide display accord scale and relevance say principal roads for the broad view add data in a relation 20 to one, you just display one small part (one fo twenty) if you go to detail scale take 1 to 100. second aproach. tile the data using some grid aproach, following the same criteria. store the tile in your spatial SQL and go for the tile when you address the DATA statment. I will tray it by myself, but will be nice to hear some comments fo our guru´s. sorry, my english still sucks. :p JC. -Mensaje original- De: Chris King [mailto:[EMAIL PROTECTED] Enviado el: Martes, 08 de Abril de 2008 05:34 p.m. Para: mapserver-users@lists.osgeo.org Asunto: Re: [mapserver-users] Mapserver and Microsoft SQL Server 2008 I thought I'd share my results of my past few weeks work on resolving my problem with the location of the plugin dll I was looking for. I found the dll in a product at http://www.mapdotnet.com (simply installed the demo and copied the dll then uninstalled) And I have just finished importing all my map data in SQL 2008. I reserved the road data for last and noticed a significant decrease in performance as I switched between postgre and sql 2008. And average of 1 or 2 seconds slower than postgre with a max of about 5 or 6 seconds where lots of detail is being displayed on the map. Anyway, as much as I wana tell Bill his program stinks, we will lose the competition if I do ;) so I'm forced to stick with SQL 08 and look for ways to improve performance. (Any links will be greatly appreciated) Cheers Chris ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Mapserver and Microsoft SQL Server 2008
I thought I'd share my results of my past few weeks work on resolving my problem with the location of the plugin dll I was looking for. I found the dll in a product at http://www.mapdotnet.com (simply installed the demo and copied the dll then uninstalled) And I have just finished importing all my map data in SQL 2008. I reserved the road data for last and noticed a significant decrease in performance as I switched between postgre and sql 2008. And average of 1 or 2 seconds slower than postgre with a max of about 5 or 6 seconds where lots of detail is being displayed on the map. Anyway, as much as I wana tell Bill his program stinks, we will lose the competition if I do ;) so I'm forced to stick with SQL 08 and look for ways to improve performance. (Any links will be greatly appreciated) Cheers Chris ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Mapserver and Microsoft SQL Server 2008
[EMAIL PROTECTED] wrote: Well, my team is taking part in the Microsoft Imagine Cup competition this year, we won our local leg using postgre, but now for the international stage you can understand how having a Microsoft product over an Opensource product will help us... Fair enough. I had never heard of the Imagine Cup, but it looks like a interesting competition. Good luck! And, keep PostgreSQL in mind for future projects! :) Colin ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Mapserver and Microsoft SQL Server 2008
[EMAIL PROTECTED] wrote: I'm looking for a way to switch our current postgre setup to SQL Server 2008. I can get the map data into SQL I just need a way to tell mapserver (in the mapfile) to look at the SQL 2008 database. Just out of curiosity, are you at liberty to divulge any of the details that led you to switch from PostgreSQL to MS SQL? Colin ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Mapserver and Microsoft SQL Server 2008
Well, my team is taking part in the Microsoft Imagine Cup competition this year, we won our local leg using postgre, but now for the international stage you can understand how having a Microsoft product over an Opensource product will help us... > [EMAIL PROTECTED] wrote: >> I'm looking for a way to switch our current postgre setup to SQL >> Server 2008. I can get the map data into SQL I just need a way to >> tell mapserver (in the mapfile) to look at the SQL 2008 database. > > Just out of curiosity, are you at liberty to divulge any of the details > that led you to switch from PostgreSQL to MS SQL? > > Colin > --- South Africas premier free email service - www.webmail.co.za -- For super low premiums, click here http://www.webmail.co.za/dd.pwm ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Mapserver and Microsoft SQL Server 2008
I have checked MS4W, but it's not there. I have never managed to compile the source code before so even though it's there, I can't do anything about it atm. :/ > [EMAIL PROTECTED] wrote: >> I'm looking for a way to switch our current postgre setup to SQL Server >> 2008. I can get the map data into SQL I just need a way to tell >> mapserver >> (in the mapfile) to look at the SQL 2008 database. >> >> I found a post relating to a plugin in order to do this: >> >> PLUGIN "C:\ms4w\plugins\msplugin_mssql2008.dll" >> >> http://mapserver.gis.umn.edu/development/rfc/ms-rfc-38 >> >> But i cannot find the dll anywhere, does it exist, if not when will it >> be >> released? > > Chris, > > the MSSQL 2008 driver's source code is available from SVN > (http://svn.osgeo.org/mapserver/trunk/mapserver/mapmssql2008.c). As to > the compiled DLL, I don't know. Have you checked the MS4W package? > > Best regards, > -- > > Andreas Albarello > Analysis & SW Development > > Territorium Online srl/GmbH > Via Buozzi Str. 12 > I 39100 Bolzano/Bozen > > Phone: +39 0471 068611 > Fax:+39 0471 068619 > > email: [EMAIL PROTECTED] > web: http://www.territoriumonline.com > > --- South Africas premier free email service - www.webmail.co.za -- For super low premiums, click here http://www.webmail.co.za/dd.pwm ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Mapserver and Microsoft SQL Server 2008
[EMAIL PROTECTED] wrote: I'm looking for a way to switch our current postgre setup to SQL Server 2008. I can get the map data into SQL I just need a way to tell mapserver (in the mapfile) to look at the SQL 2008 database. I found a post relating to a plugin in order to do this: PLUGIN "C:\ms4w\plugins\msplugin_mssql2008.dll" http://mapserver.gis.umn.edu/development/rfc/ms-rfc-38 But i cannot find the dll anywhere, does it exist, if not when will it be released? Chris, the MSSQL 2008 driver's source code is available from SVN (http://svn.osgeo.org/mapserver/trunk/mapserver/mapmssql2008.c). As to the compiled DLL, I don't know. Have you checked the MS4W package? Best regards, -- Andreas Albarello Analysis & SW Development Territorium Online srl/GmbH Via Buozzi Str. 12 I 39100 Bolzano/Bozen Phone: +39 0471 068611 Fax:+39 0471 068619 email: [EMAIL PROTECTED] web: http://www.territoriumonline.com ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users