I noticed that one example UsingCustomData is using that event CustomColumnFetch of FeatureSource. However, it is not well documented on how to use it.
Specially, how to specify Custom Columns so that the event will fire?
The example used this line:
worldLayer.ZoomLevelSet.ZoomLevel01.DefaultAreaStyle.RequiredColumnNames.Add("Test");
It is weird! Just a name, no type.
Thanks,
Hi guangming,
Thanks for your post, the CustomColumnFetch event will try to fire when getting feature from layer, at this time the map will try to search all returnColumns from the layer, if get any returnColumn, map will fire the CustomColumnFetch events.
And in the code you paste, you have added a column named “Test”, so when the layer try to review it’s feature, it will notice there is a return column, and fire the CustomColumnFetch, once this event fired, you can get a CustomColumnFetchEventArgs, this event argument is the column “Test”, you can use e.id, e.columnname, e.columnvalue etc.
Please don’t hesitate to let us know if you have any queries.
Regards,
Gary
Hi Gary,
I got that. Thanks.
If my data is from a sql database table, and the custom field is from another table, does this event get triggered for each custom field for each feature? if so, seems not efficient.
Another question, if I zoom the map, the application is going back to the server to retrieve all the data. Do the events above happen all in the server side once? or required multiple client - server trips?
I am trying to get the performance impact on the client side.
HI Guangming,
Please check the anwsers as following:
For the first question, the answer is that the event will be triggerred for each custom field for each feature, i agree with you that seems like not efficient.
For another question,i think it should be muktiple client-server trips, i’m not sure whether i understand you correctly, but the event will be rasied once you draw a tile on client side or call getFeaturesInsideBoundingBox, GetAllFeatures etc.
Hope it helps, also can you show more details about the requirements here, so that we can did an evaluation and give some suggestions here.
Thanks,
Johnny
Hi Johnny,
Thanks for your offer. Here is my scenario:
I have a report to map: eg, polygonID, ReportValue (for each polygon). This report data set is generated on the fly (different filters) from a set of stored procedures. Then I need to use the ReportValue for each Polygon and map the polygon (colors/fills -> Thematic mapping). The polygon geometry data is saved in one table in the database.
I am wondering what is the best strategy to join the two dataset: easy to code, good performance, …?
I noticed that to connect to data in database, your API allows only passing table or view name (seems you can also change Where clause). If I catch that ExecutingSQLStateMent and modify the SQL statement to retrieve the ReportValue for each polygon. Does this have a performance concern as you agreed with me in another post regarding CustomColumnFetch?
If I used CustomColumnFetch and cached the report dataset, then I merged it with spatial dataset here: it may still have a performance issue as I have to join the two dataset by Polygon ID.
The best solution would be if your API allows passing a dynamic sql statement either a query or a stored procedure. At the same time, specifying primary key and geo column.
It would be very helpful if you can provide a level of details on how your API calls work with database if my datasource is from database, or shapefile. If you already have a document, pls share that with me. The major question I would like to ask is how your API requests spatial and non spatial data giving a bounding box and data in database.
Thanks,
Hi Guangming,
would you please let us know what’s the relationship of the two dataset? are they from the same database? Also, would you let us know what’s the purpose to register the “CustomColumnFetch” event? as I know, if the event will bring a bad performance if we do some calculations in it. If we can, we should avoid the event.
But using the ExecutingSQLStateMent should be a good way to retrieve the data by your requirements, but we need to notice that we should keep the returned columns in the sql statement is matched with the table’s which we specified for the sqllayer, or the other columns value would not be filled in the returned features.
Other thing is the MsSql2008Featurelayer doesn’t support the stored procedure, but I think we can use the ‘Function’ to instead of it. here is a post about it:thinkgeo.com/forums/MapSuite…fault.aspx
We request the spatial data by looking up the geometry column in sql table and then fill the data as the feature baseshape, as for the non-spatial data, we just search them from database and then convert them as the feature columns. All the requests is through the sql statement and we can capture it in the “ExecutingSQLStateMent” event.
Hope it helps.
Regards,
Troy
Hi Troy,
Thanks. Seems ExecutingSQLStatement is my solution.
Can you explain the different types of ExecutingSQLStatementType options: when and why is used / called, for example in an event of zooming in the map?
Thanks,
Guangming
Hi Guangming,
For the details of ExecutingSQLStatementType, I guess we can check the document wiki.thinkgeo.com/wiki/Think…tementType
May be some types in the document are not clear for you as they are operated in background. For example, when the map is loading, the “GetFirstGeometryType” and “GetBoundingBox”, “GetAllFeatures” etc. will be called in a short time. When the map pan or zoom, the “GetFeaturesInsideBoundingBoxEx” will be triggered to get the current features in the current view.
Hope it helps.
Regards,
Troy
A great link! would be clear to whom? someone from Mars?
That document basically says nothing about what a type is and when that type of query will be called. It says nothing useful!
HI guangming,
Just as I mentioned in an email before about the “avoiding
duplicated labels” from our sales guys, I shows the Map Suite Drawing process
as in following picture:
You know, in the first step, we need to query all the
features in current view extent and draw them on a specified canvas (generally
should be a GDI+ bitmap in most of time.), so here is the place (query the
required features from a specified SQL Server database) the “ExecutingSQLStatement”
is used. Actually the process of querying the features from database is really
similar to a normal SQL Server query, such as build the query SQL Script like “Select
* from tableA”, or something like that at first, and then call the “ADO.net
operation” APIs, like “ExecuteNonQuery”, “ExecuteScalar” etc. with built
sql script to query the database. In Map Suite, these “query sql script” are
called “ExecutingSQLStatement”, while what’s the ExecutingSQLStatementType? Actually,
which means which method in MsSQLServerFeatureSource built this sql script and
call the “Execute Query”, to some extent, the method name is the
ExecutingSQLStatementType, for example, the type “GetColumns”
means the public method “GetColumns” passed the query script and call the query
executing, Hope it helps.
Also when the drawing process is raised? The answer is when
you redraw or refresh the map, maybe a mouse move, or a zoom in/out etc.
Thanks,
Johnny
Hi Johnny:
Guangming and I work on the same project. We have found out that we cannot use functions and views to return the sql server data since the database is very complex and we have to use stored procedures to return data instead. Could you provide some sample code for us to look at it.
Thanks,
Adrian
Hi Adrian,
I am sorry to say the MsSqlFeatureLayer doesn’t support stored procedure currently based on current Apis and I have no idea if we will support it in the future, but seems it would be very complex to implement it. The reason is it is impossible to assign a procedure as the sql table name. In mssqlfeatureLayer, we always can see the select T-SQL statement by registering the ExecutingSQLStatement like the below:
SELECT [geom].STAsBinary() as [geom],[ID] FROM TableName
However, the stored procedure name can’t be as the table name.
I also try the other ways, but still didn’t find any good way. Please let me know if you have more information.
Thanks,
Troy
Thank you so much Troy for your reply. I will play with your idea.
I did find the following method but I am not sure if I can use it appropriately: QueryTools.ExecuteQuery.
1. My question is that can I use it to call the stored procedure to get a DataTable?
2.The following question would be. How can I use that table to add the LayerOverlay and to the map afterwards?
3. I have the following code in my sql statement:
geography::STGeomFromText(@geography,4326).STIntersects(geom)=1 in the ExecutingSqlStatement even handler. my question is where is the variable @geography defined and how can I do it in the stored procedure?
Thank you very much,
Adrian
Hi Adrian,
For your questions:
1). Yes, we can use the stored procedure in QueryTools.ExecuteQuery. For instance, we have a stored procedure defined like this:
CREATE PROCEDURE [dbo].[Pro_Test]
@Geometry text
AS
BEGIN
SELECT [geom].STAsText() as [geom],[ID] FROM cntry02 WHERE (geometry::STGeomFromText(@Geometry,4326).STIntersects(geom)=1);
END
Then, we call the query like:
string sqlStatement = string.Format(“EXEC [dbo].[Pro_Test] @Geometry = ‘{0}’”,sql2008Layer.GetBoundingBox().GetWellKnownText());
sql2008Layer.QueryTools.ExecuteQuery(sqlStatement);
2): By default, the ExecuteQuery is always used to get the column values but no geometry information. But for your case, we can return the geometry wkt in the datatable with the column named “geom” yellow marked. Then, we can create a help method to convert the datatable to a feature collection. In the end, with those features, we can add them into any layers or map.
3): the “@geography” is a hard-code parameter in MsSqlFeatureLayer and we will assign the parameter value in the SqlCommand on the fly. But unfortunately, the SqlCommand is not public, so, we can’t custom its value or use stored procedure.
Hope the above helps.
Troy
with all these limits from ThinkGeo and SQL Server, I think the best solution so far is to create an inherited MSSql2008FeatureSource, override the required methods, and call stored procedure to get the data for mapping/styling/labeling.
I got a question on creating this new FeatureSource. Because what we really need is to add a new column to a spatial layer, eg zipcode, we would like to draw all zip code polygons given a bounding box, then for each zip code polygon, we would like to find a so called report value to style the polygon and label the polygon if chosen.
I think to minimize the work, I would like to use the parent method eg GetAllFeaturesCore() to get all spatial features (from the spatial table, which will be the table name of the featuresource), then call my stored procedure to get zip code report values, and update the report field’s value.
#1: so is this possible? for this I would like to use your existing implementation to create feature collection.
#2: when I override the functions, can I call the parent.ExecuteQueryCore() to call my stored procedure to return a datatable for report field? for this I would like to use your existing implementation to execute sql statement.
Thanks,
Guangming
Thank you Troy very much Troy. You are awesome. Everything that you have said is on the target.
Here is the follow up question and I think that we are almost to the end of it.
In your sample code you pass a parameter of @geometry and it is set to as {0} in order to satisfy the call to the following functions :
geometry::STGeomFromText(@Geometry,4326).STIntersects(geom)=1
Is that right?
In our case we also need to pass another GUID for get the value for thematic mapping and I think we just pass two parameters, right?
Let us try it to see if it works for us without override the ExecuteQueryCore.
Thanks,
Adrian
so my code:
public DkSqlFeatureSource()
: this(string.Empty, string.Empty, string.Empty)
{ }
// We have provided another constructor that has all of the parameters we need for the FeatureSource
// to work.
public DkSqlFeatureSource(string connectionString,string tableName, string idColumnName )
: base(connectionString, tableName, idColumnName)
{
}
protected override Collection<FeatureSourceColumn> GetColumnsCore()
{
Collection<FeatureSourceColumn> returnColumns = base.GetColumnsCore();
// add my new column to returnColumns
return returnColumns;
}
protected override Collection<Feature> GetAllFeaturesCore(IEnumerable<string> columnNames)
{
Collection<Feature> returnFeatures = base.GetAllFeaturesCore(columnNames);
string sqlForReport = @"exec mystoredprocedureHere";
DataTable dt = this.ExecuteQueryCore(sqlForReport);
// join dt with return features
return returnFeatures;
}
public class DkSqlFeatureLayer : MsSql2008FeatureLayer
{
// It is important for compatability that you always have a default constructor that
// takes no parameters. This constructor will just chain to the more complex one.
public DkSqlFeatureLayer()
: this(string.Empty, string.Empty, string.Empty)
{ }
public DkSqlFeatureLayer(string connectionString, string tableName, string featureIdColumn)
: base(connectionString, tableName, featureIdColumn)
{
this.ConnectionString = connectionString;
this.TableName = TableName;
this.FeatureIdColumn = FeatureIdColumn;
// Here is where we create our FeatureSource and set the internal property on the FeatureLayer
DkSqlFeatureSource dkFeatureSource = new DkSqlFeatureSource(connectionString, tableName, featureIdColumn);
this.FeatureSource = dkFeatureSource;
}
}
the problems I am having are:
Collection<FeatureSourceColumn> returnColumns = base.GetColumnsCore();
always return NULL;
the overloaded GetAllFeaturesCore never get called so no data on the map.
Or a more generic question: what is the order of these override functions get called?
Hi Adrian,
Good to hear my answer helps.
For the parameter @Geometry, yes, it is mapping with the parameter in the defined parameter of stored procedure and also you can rename it as any one you want. And in your case, you said you have another parameter to pass the Guid, apparently, this is valid pass multi parameters in stored procedure based on T-SQL specification.
Hope you can keep going well and let us know if any questions.
Troy