ThinkGeo.com    |     Documentation    |     Premium Support

Query a SqlServerFeatureSource for displaying

#1

I want to select and display features from a SqlServerFeatureSource using a where clause with multiple values.

Sample SQL query looks like this : SELECT * FROM table where Columname in (‘value1’,‘value2’)

What is the way to query and display these objects ?

  • I tried the SqlServerFeatureSource.ExecuteQuery(…) that gives me the result in a data table, where the geometry is in sqlservergeometry. How to convert these to Thinkgeo Features for displaying on a map ?

  • Other way to go is to use GetFeaturesByColumnValue but then i have to repeat this line for every value, because it can handle only 1 value at a time.

Any hints ?

#2

Thanks,
I looked into the detail. We don’t provide the GetFeaturesByStatement method. But you can do it by yourself. Like you mentioned After you run ExecuteQuery(…) You will get ID, column values and sqlservergeometry. I put the full code we get the feature from dataReader. You may need adjust these code a little to use the datarow instead of dataReader.

private Feature GetFeature(SqlDataReader dataReader, IEnumerable<string> columnNames)
        {
            string id = null;
            byte[] wellKnownBinary = null;
            Dictionary<string, string> fieldValues = new Dictionary<string, string>();
            List<string> fieldsList = (columnNames == null) ? new List<string>() : new List<string>(columnNames);
            for (int i = 0; i < dataReader.FieldCount; i++)
            {
                string columnName = dataReader.GetName(i);

                if (columnName.Equals(featureIdColumn, StringComparison.CurrentCultureIgnoreCase))
                {
                    string value = dataReader[i].ToString();
                    id = value;
                    fieldValues.Add(columnName, value);
                }
                else if (!columnName.Equals(customGeometryColumnName, StringComparison.CurrentCultureIgnoreCase))
                {
                    fieldValues.Add(fieldsList[i], dataReader[i].ToString());
                }
                else if (dataReader.GetDataTypeName(i).Equals("VarBinary", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (dataReader[i] != DBNull.Value)
                    {
                        wellKnownBinary = (byte[])dataReader[i];
                    }
                    else
                    {
                        wellKnownBinary = new byte[] { };
                    }
                }
            }

            return new Feature(wellKnownBinary, id, fieldValues);
        }

Thanks

Frank

#3

OK, your conversion to WKB doesnt work directly on sqlgeometry, but i took the WKT string from the sql geometry and that is working well. The only problem now is, that if i get the WKT, projection info is lost.

Is there a way to get the WKB of WKT with projection info, or do I have to convert it with my own reproject method ?

#4

Thanks,
The WKB or WKT doesn’t contain any projection info. I assume you are using InMemoryFeatureLayer to display the feature you get by the statement. Here is the way to apply the projection.

   InMemoryFeatureLayer inMemoryFeatureLayer =  new InMemoryFeatureLayer();
            // add feature to the inMemoryFeatureLayer
            var projectionConverter = new ProjectionConverter(4326, 3857); ;
            inMemoryFeatureLayer.FeatureSource.ProjectionConverter = projectionConverter;

            // You can also use projectionConverter.ConvertToExternalProjection(...) to convert the feature one by one.

Thanks

Frank

#5

Thanks, that did the trick !

#6

Thanks,
Good to know it works. Go ahead let us know if you have any question.

Thanks

Frank