Hello,
I'm curious if you can load a MsSql2008FeatureLayer with a custom query. Limiting the rows and columns by supplying your own custom query like ("select columnA(as data), columnB(as geometry) from table where columnA=value and columnC=value).
This would be most valuable and consume less memory.
Thanks in advance,
Dan
Dan,
I agree that this would be very useful. We are still somewhat newbies to SQL 2008’s capabilities but no strangers to SQL in general. This could be a bit tricky because there are many things we consider when building the SQL statement. We ensure that the columns you need are there that are requested form the styles. We do the spatial operations as well. There are also other statements we need to execute like to get the count etc.
There are a bunch of ways we can solve the issue, let me run some by you.
The first is for you to build you own custom SQL 2008 FeatureSource and FeatureLayer. This is not as hard as it sounds and we have examples of accessing other databases. If you know the spatial SQL statements this can give you total control. Check out the video on how to do this if you haven’t already. Even if you don’t like this approach I still suggest you review it to get some insight on to how our framework operated in regards to Layers.
gis.thinkgeo.com/Support/DiscussionForums/tabid/143/aff/16/aft/4769/afv/topic/Default.aspx
Another option would be for us to raise an event before we execute any SQL statements. This would allow you to use your own or to tack on additional statements to the ‘where’ clause. This would be possible but we would have to do it for all SQL statements. You would need to handle things like the count as well. You would need to really understand our queries to make sure you include the things the system needs such as fields requested by the styles for thing like value styles or label columns.
Yet another approach would be to have a where clause collection. Here you could add additional where clause statements like 'TollRoad = true” etc to do filtering. This could include things like greater than, less than etc. This doesn’t give as much flexibility but also prevents you from making mistakes as well. In this method you could not do joins etc.
Maybe a different approach would be to use views instead. I haven’t checked if we support views but I think we might as they usually look just like tables to SQL. This is generally a good practice anyway if you want to treat a certain subset of data as a first class citizen.
I invite feedback on this. We want to make our support for SQL 2008 powerful and intuitive and are very open to suggestions and feedback. If you have scenarios would would love to hear them as it gives us something concrete to work with.
David
David,
I also need the ability to run ad hoc queries. I would expect most people using SQL Server would also need this ability, especially if they are writing something more complex than a static map viewer. My users will want to create new views of their own design, which will require querying out specific features in specific areas. They are not going to want to write a complex query into the database, they will want my code to do that for them. They may decide they don’t like that view and modify it on the fly. This doesn’t lend itself to rigid predefined database views (which we aren’t sure you support anyway). Perhaps you could simply tell us what is required in the query and put the onus of writing correct queries on the programmer.
Charles
David,
It appears that you can’t specify a View in place of the table when creating a new MsSql2008FeatureLayer.
Charles
I am using PostgreSQL server, but also need this ability. I need to get a subset of data based on different search criterias on-the-fly.
Instead of a table name, can we pass in a query string, you can tell us what kind of columns must be included in the query string.
Rose
Dan and Charles,
The current desktop edition doesn’t support view for SQL 2008 and we will include it in the upcoming version. Here we create a temporary extension for you so you can have a try now. Do please note that as there are already corresponding classes in the desktop, we add “Temp” as the prefix in this extension and when the next version is out, you can remove this extension and directly use the intrinsic classes. Here are the codes to generate the view and connect to the view, have a try!
string connectString = "Data Source=192.168.0.58,1041;Initial Catalog=InternalDB;Persist Security Info=True;User ID=username;Password=password";
TempMsSql2008FeatureLayer sql2008Layer = new TempMsSql2008FeatureLayer(connectString, "cntry02", "ID");
sql2008Layer.Srid = 4326;
sql2008Layer.Open();
sql2008Layer.ExecuteNonQuery("create view cntry02view as select id, SIZE, geom from cntry02;");
sql2008Layer.Close();
sql2008Layer = new TempMsSql2008FeatureLayer(connectString, "cntry02view", "ID");
sql2008Layer.Srid = 4326;
sql2008Layer.Open();
sql2008Layer.ZoomLevelSet.ZoomLevel01.DefaultAreaStyle = AreaStyles.Country1;
sql2008Layer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20;
winformsMap1.StaticOverlay.Layers.Add("Sql2008Layer", sql2008Layer);
Thanks,
Ben
563-MsSql2008Extension.zip (32 KB)
Rose,
Here is the new Postgre Extension for you. You can create a view and connect to a view with the similar code like above. As the Postgre is an extension, you just need to replace the old one and do not need to add any prefix for the classes.
Thanks,
Ben
564-PostgreExtension.zip (12.7 KB)
Ben,
Thanks for the reply. I created a new topic with the same question when I thought this topic was closed. The new topic references Ad-Hoc queries. You might want to post this response there as well in case someone is watching that topic.
Any idea when you might release a version with Views included in the MSSQL2008FeatureLayer?
One more question just to clarify. If the View already exists, can I skip the step of creating it?
Charles
Charles,
Thanks very much for reminding. I think it might be better to provide a link in that post though to kind of centralize the focus to one place, public / update the dlls in one place to reduce the possibility of dll hells. Just do not want that extension dlls be posted all over the threads that if it needs to be updated, it’s difficult to cover all of them.
We are now busying working on the next release and have already added many cool features, I think it might be available early next month. You will find it with many great enhancements compared with the last release.
I think we can check if a View exists or not using SQL query. I googled “Check if a view exists, SQL server” and found some pages about that, please have a try.
Thanks,
Ben
Ben,
The Extension works well with most of my layers. I have tested the extension with the one view I have created and it works great. Most of the layers I am opening are still tables because I haven't written the views yet. I am still testing but I have run across a problem I can't debug on my own. When I use the TempMsSql2008FeatureLayer to open one on my tables I get an error. The error occurs after my code finishes processing and the form with the winFormMap1 control is refreshing. The feedback from the error is as follows:
NullReferenceException
Object reference not set to an instance of an object.
Error Details:
at ThinkGeo.MapSuite.Core.TempMsSql2008FeatureSource.x1e8067e880a0b849(RectangleShape x05576ac689f0c0df, Int32 x4a30a6ec9c7ff851, IEnumerable`1 x8ad42fcdfcf2a001)
at ThinkGeo.MapSuite.Core.TempMsSql2008FeatureSource.GetFeaturesInsideBoundingBoxCore(RectangleShape boundingBox, IEnumerable`1 returningColumnNames)
at ThinkGeo.MapSuite.Core.FeatureSource.GetFeaturesForDrawingCore(RectangleShape boundingBox, Double screenWidth, Double screenHeight, IEnumerable`1 returningColumnNames)
at ThinkGeo.MapSuite.Core.FeatureSource.GetFeaturesForDrawing(RectangleShape boundingBox, Double screenWidth, Double screenHeight, IEnumerable`1 returningColumnNames)
at ThinkGeo.MapSuite.Core.FeatureLayer.DrawCore(GeoCanvas canvas, Collection`1 labelsInAllLayers)
at ThinkGeo.MapSuite.Core.Layer.Draw(GeoCanvas canvas, Collection`1 labelsInAllLayers)
at ThinkGeo.MapSuite.DesktopEdition.LayerOverlay.xa6cb874f7090c07f(GeoCanvas x31c084515ae9393f)
at ThinkGeo.MapSuite.DesktopEdition.LayerOverlay.DrawCore(GeoCanvas canvas)
at ThinkGeo.MapSuite.DesktopEdition.Overlay.Draw(GeoCanvas canvas)
at ThinkGeo.MapSuite.DesktopEdition.xf0380b1a0bc40ca6.StartDraw()
at ThinkGeo.MapSuite.DesktopEdition.x601a5561df898600.x7510a5c148e0acfd(Object x19b0b2213afc2289)
at System.Threading._ThreadPoolWaitCallback.WaitCallback_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading._ThreadPoolWaitCallback.PerformWaitCallbackInternal(_ThreadPoolWaitCallback tpWaitCallBack)
at System.Threading._ThreadPoolWaitCallback.PerformWaitCallback(Object state)
The table I am trying to open when the error occurs has approximately 5000+ line type geometry records. It is quite possible that there is a problem with the data, but I don't get the error if I use the MsSql2008FeatureLayer.
Any thoughts?
Charles
Charles,
Could you have a try on this one? We made some updates on the assembly and please let me know if you have any problems.
Thanks,
Ben
592-TempMsSql2008Extension2.zip (33.4 KB)
Ben,
That did the trick. I can use the MakeAllGeometriesValid function in my map manager that will create the custom views, but it would be nice to identify the invalid entries rather than just fixing (truncating?) them. I have a suggestion. Could you put in a FindInvalidGeometry function that returns the first invalid geometry record. Alternatively, could you tell me how you are finding the invalid geometry. SQL Server doesn’t seem to have a problem with the geometry, but I don’t know for a fact that I have opened every record in SQL Server. It will only open 1000 records at a time.
Charles
Rose, Posted By Ben on 04-09-2009 07:41 AM
Here is the new Postgre Extension for you. You can create a view and connect to a view with the similar code like above. As the Postgre is an extension, you just need to replace the old one and do not need to add any prefix for the classes.
Thanks,
Ben
Ben, I tried the new extension, it does not work for me. the view was created successfully, but when open the PostgreSQLFeatureLayer, following exception was thrown, if I use the table instead of view, it works. Something must be wrong in the view.
System.ArgumentException was unhandled
Message=“Can not find the geometry column in table : ‘vesselsView’”
Source=“PostgreExtension”
StackTrace:
at ThinkGeo.MapSuite.Core.PostgreSqlFeatureSource.OpenCore() at ThinkGeo.MapSuite.Core.FeatureSource.Open() at ThinkGeo.MapSuite.Core.FeatureLayer.OpenCore() at ThinkGeo.MapSuite.Core.Layer.Open()
Charles,
We have the method TempMsSqlFeatureLayer.Validate() to get all the incorrect records as well as the reason why they are not accepted. Another way is to execute this SQL-Statement on Server: “SELECT * from TableName where GeometryColumnName.STIsValid()=0;” don’t forget to replace TableName and GeometryColumnName with your corresponding ones.
Thanks,
Ben
Rose,
Can you have a quick try with the name "vesselsview" instead of "vesselsView"? We met this case before and seems after creating a view without the view name is quoted, Postgre will change the name to lowercase.
Thanks,
Ben
Rose,
Also here is the new PostgreExtension where we made some improvements on the ExecuteSqlQuery() method. Could you try this assembly instead?
Thanks,
Ben
598-PostgreExtension2.zip (13.7 KB)
Ben:
Thanks for the new PostgreExtension.
I changed my view name to all lower-case, it works now.
Rose
You are welcome, Rose.
Dan and Charles,Posted By Ben on 04-09-2009 07:34 AM
The current desktop edition doesn’t support view for SQL 2008 and we will include it in the upcoming version. Here we create a temporary extension for you so you can have a try now. Do please note that as there are already corresponding classes in the desktop, we add “Temp” as the prefix in this extension and when the next version is out, you can remove this extension and directly use the intrinsic classes. Here are the codes to generate the view and connect to the view, have a try!
string connectString = "Data Source=192.168.0.58,1041;Initial Catalog=InternalDB;Persist Security Info=True;User ID=username;Password=password";
TempMsSql2008FeatureLayer sql2008Layer = new TempMsSql2008FeatureLayer(connectString, "cntry02", "ID");
sql2008Layer.Srid = 4326;
sql2008Layer.Open();
sql2008Layer.ExecuteNonQuery("create view cntry02view as select id, SIZE, geom from cntry02;");
sql2008Layer.Close();
sql2008Layer = new TempMsSql2008FeatureLayer(connectString, "cntry02view", "ID");
sql2008Layer.Srid = 4326;
sql2008Layer.Open();
sql2008Layer.ZoomLevelSet.ZoomLevel01.DefaultAreaStyle = AreaStyles.Country1;
sql2008Layer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20;
winformsMap1.StaticOverlay.Layers.Add("Sql2008Layer", sql2008Layer);
Thanks,
Ben
Ben:
I am using Postgre Extension instead of MSSQL2008, but the code is similar.
My question is how to query the spatial data from more than one tables? In your above code, when you 1st create the sql2008Layer, you passed in the table name "cntry02", in the ExecuteNonQuery method, you passed in a query string which will retrieve the data from the same table "cntry02". Can I pass in a quer string which is something like this:
create view cntry02view as select cntry02.id,cntryo2.geom, table2.name where cntry02.id=table2.id
Any help is greatly appreciated!
Rose
Rose,
As you note, I am using the SQL Server version. I created a View in the database (just a pre-defined query). In my case the query behind the View was "SELECT * FROM RoadsGeometry LEFT JOIN Roads on RoadsGeometry.FeatureID = Roads.ID WHERE Roads.RDCode < 2". Road Codes of 1 and 1.1 represent major roads. Once the View is defined, I used it like a table with the TempMsSqlLFeatureLayer extension and the map window only contains major roads.
I hope this helps.
Charles