Question about GetFeaturesInsideBoundingBox?

ThinkGeo UI for Desktop WPF
1

Hi,

I use “GetFeaturesInsideBoundingBox” on a MsSql2008Feature and this method takes 5 seconds.
The result returns more than 160000 items. It’ so long so I try to reduce the execution time.
I launch sql profiler to see what query is executed with the method GetFeaturesInsideBoundingBox
The query (Query1) is :
exec sp_executesql N’SELECT [Geom].STAsBinary() as [Geom],[CartoId] FROM [Carto].[CartoD ] with(index(Carto_01)) WHERE (geometry::STGeomFromWKB(@Geometry,2154).STIntersects(Geom)=1) AND ( TYPE = 4);’,N’@Geometry varbinary(93)’,@Geometry=0x01030000000100000005000000000000005C47154100000040A5265941000000005C4715410000000027995841000000007C621C410000000027995841000000007C621C4100000040A5265941000000005C47154100000040A5265941
go

In fact , I just need to know if the number of returned items is greater than 0, si I tested this query (Query2) :

exec sp_executesql N’SELECT [CartoId] FROM [Carto].[CartoD ] with(index(Carto_01)) WHERE (geometry::STGeomFromWKB(@Geometry,2154).STIntersects(Geom)=1) AND ( TYPE = 4);’,N’@Geometry varbinary(93)’,@Geometry=0x01030000000100000005000000000000005C47154100000040A5265941000000005C4715410000000027995841000000007C621C410000000027995841000000007C621C4100000040A5265941000000005C47154100000040A5265941
go
And the Query2 takes 1 second, which is better.

Question 1 :
Is it possible to overload querytools and to add a method like QueryTools.GetFeaturesIdInsideBoundingBox(f.GetBoundingBox(), ReturningColumnsType.NoColumns);
And this method would perform Query2 ?

Question 2 :
5 seconds to return 160,000 items with QueryTools.GetFeaturesInsideBoundingBox(f.GetBoundingBox(), ReturningColumnsType.NoColumns); It seems you normal ? If not, do you think the problem may be a bad spatial index ? Or other things ?

Thanks for your help.

Regards

Steph.

2

Hi, Steph

I think you should extend the class with your wanted functions. If you do as you describe, and get just the IDs, the server will still have to get all the 160 k answers for you. It will return less data as the IDs occupy less space than the geometries, but nevertheless.
If you were to create a function, which could be named MyQueryTools.IsAnyFeaturesInsideBoundingBox(f.GetBoundingBox), then it should execute something like:

exec sp_executesql N’IF EXISTS (SELECT * FROM [Carto].[CartoD ] with(index(Carto_01)) WHERE (geometry::STGeomFromWKB(@Geometry,2154).STIntersects(Geom)=1) AND ( TYPE = 4);’,N’@Geometry varbinary(93)’,@Geometry=0x01030000000100000005000000000000005C47154100000040A5265941000000005C4715410000000027995841000000007C621C410000000027995841000000007C621C4100000040A5265941000000005C47154100000040A5265941)
RETURN 1
ELSE
RETURN 0
go

Then you would allow the SQL server to hunt for any result, and return as soon as it found just one. If, after this, you ask for more, then you will get the penalty of the complete query.
The reason for asking for SELECT * in the above is that this allows the SQL server to use ANY index it seems fit to answer your question. This is one of the very few examples of using asterisk instead of a column list that is recommended.

Best regards,
Keld List Laursen

3

Hi Keld,

Thanks a lot for your help.
I tested the sample and when I executed , I have an error message “Incorrect syntax near ‘)’.”
I changed few things and I still have an error message

My second question is for the extend class QueryTools
I create a class like this :
public static class QueryToolsExtension
{
public static bool IsAnyFeaturesInsideBoundingBox(this QueryTools queryTools, BaseShape targetShape)
{
return true;
}
}

I would like the extended methods are generic such you do for GetFeaturesInsideBoundingBox.
How can I do that ?

Thanks again for your help.
Regards.

Steph.

4

Hi Steph,

I think you can do something like this:

(sql2008Layer.FeatureSource as MsSql2008FeatureSource).ExecutingSqlStatement += LoadMsSql2008FeatureLayer_ExecutingSqlStatement;

private void LoadMsSql2008FeatureLayer_ExecutingSqlStatement(object sender, ExecutingSqlStatementMsSql2008FeatureSourceEventArgs e)
{
if (e.ExecutingSqlStatementType == ExecutingSqlStatementType.GetFeaturesInsideBoundingBoxEx && top1)
{
e.SqlStatement = e.SqlStatement.Replace(“SELECT”, “SELECT TOP 1”);
}
}

You can control it by switch the value of “top1” between True and False.

Wish that’s helpful.

Thanks,

5

Hi Don,

Thanks.
But my goal is to extend the class querytools.
So I did :

public static class QueryToolsExtension
{

public static bool IsAnyFeaturesInsideBoundingBox(this QueryTools queryTools, BaseShape targetShape,Couche coucheFg)
{
var geometry= coucheFg.ColonneGeometry;
var id = coucheFg.ColonneId;
var schéma = coucheFg.Schema;
var table = coucheFg.Table;
string whereClause = “(geometry::STGeomFromWKB(@Geometry,”+(int)coucheFg.SRID+").STIntersects("+geometry+")=1)";

        *byte[] theBytes = Encoding.UTF8.GetBytes(targetShape.GetWellKnownText());*

        *var sql = "exec sp_executesql N'SELECT TOP 1 [" + id + "] FROM [" + schéma + "].[" + table + "] WHERE " + whereClause + ";',N'@Geometry varbinary(93)',@Geometry=" + theBytes;*
        *var dt = queryTools.ExecuteQuery(sql);*

        *return dt.Rows.Count>0;*
    *}*

}

And I use this method :
var ttt = ((FeatureLayer)coucheCible.Layer).QueryTools.IsAnyFeaturesInsideBoundingBox(f.GetBoundingBox(),coucheCible.Couche );

Questions :

  1. This does not work because I can’t convert string to varbinary ;-(. (byte[] theBytes is not correct.)

  2. Do you have an example “extend querytools” …
    I do not think my method IsAnyFeaturesInsideBoundingBox is correct

Thanks again for your help.
Regards.

Steph.

6

Hi Steph,

Sorry for the delay. Please check the attached below:

MsSql2008FeatureSourceExtension.cs (3.1 KB)

And you can use it like below after opening the MsSql2008FeatureLayer:

 var hasFeatures = ((MsSql2008FeatureSource)sql2008Layer.FeatureSource).IsAnyFeaturesInsideBoundingBox(extent);

Thanks,
Peter

7

Hi Peter,

Thanks.
I tested the code and I can not run the code. Below the different problems I’ve had.

  1. GetDbConnection() :
    The value “source.GetType().GetField(“connection”, BindingFlags.Instance | BindingFlags.NonPublic)” return null, so the db connection does not work
    I changed
    public static bool IsAnyFeaturesInsideBoundingBox(this MsSql2008FeatureSource featureSource, RectangleShape boundingBox)
    {
    using (var conn = new SqlConnection(Connect.DefaultConnectionString))
    {
    //var connection = GetDbConnection(featureSource);
    if (conn.State != ConnectionState.Open)
    {
    conn.Open();
    }
    var command = conn.CreateCommand();
    SetCommand(featureSource, command, boundingBox);
    var reader = command.ExecuteReader(); // remember to dispose this command if necessary.
    return reader.HasRows;
    }
    }

And that works. By doing so, i dispose the connexion … no ?

  1. I tested again and I have an error with $ ==> command.CommandText = $"SELECT {featureSource.FeatureIdColumn}
    I delete this character and now I have :
    command.CommandText =“SELECT {featureSource.FeatureIdColumn} …”

  2. Now, i have this error
    L’exception System.Data.SqlClient.SqlException n’a pas été gérée
    HResult=-2146232060
    Message=Syntaxe incorrecte vers ‘.’.
    Source=.Net SqlClient Data Provider
    ErrorCode=-2146232060
    Class=15
    LineNumber=1
    Number=102
    Procedure=""
    Server=.\SQLEXPRESS2014
    State=1

Do you have the same problem ?

Thanks again for your help.
Regards.

Steph.

8

Hi Steph,

The code can work well on my side. Please check the comments below:

Please call the featureSource.Open() method or featuerLayer.Open() method before call the extentsion method IsAnyFeaturesInsideBoundingBox()

The Character “$” is a syntactic sugar of C#6.0 and we can use the string.Format(format, arg0..) instead. like below:

 string.Format("SELECT {0} ....",  featureSource.FeatureIdColumn);

Please change it like above and have a try again.

Thanks,
Peter