ThinkGeo.com    |     Documentation    |     Premium Support

SqliteFeatureLayer views

#1

I am currently working with ThinkGeo.MapSuite.Layers.SqliteFeatureLayer on version 10.5.8.

I would like to use a Sqlite view instead of a table to display data to a map. However, passing a view into the SqliteFeatureLayer throws errors saying indexes for that view does not exist. It is not possible to create indexes for views in sqlite.

Is there a different class to handle views? Or could I get a hotfix similar to: Query Spatial Data from Database

Thank you!

#2

Hey @JarodH,

If you use the SqliteFeatureSource.CreateView static method, it will also create the geometry index for you, which is really a Virtual Table. After which, you would just supply the view’s name instead of the table when you create the SqliteFeatureLayer like normal. You may want to also update the ThinkGeo.MapSuite.Layers.SqliteFeatureLayer nuget package to the latest 10.6.2.

Thanks,
Kyle

#3

Thank you @Kyle_Day. I did not know about the CreateView option. I have tried it both on 10.5.8 and 10.6.2 but still get the same error I did previously. When passing in the view into the SqliteFeatureLayer I get an exception when the Draw() method is called saying, “SQL logic error no such table: idx_viewName_geom”.

My initial assumption is that it was unable to create that index because of the restrictions sqlite has on creating index tables for a view. Can you shed some light on this? Perhaps there is something else I am not seeing.

#4

Hey @JarodH,

In your Sqlite database, do you have a table called idx_viewName_geometry? If so, set mySqliteLayer.FeatureSource.AlternateGeometryIndexTableName = "idx_viewName_geometry"; It looks like your geometry column for your view might be “geom” based on your error message.

Thanks,
Kyle

#5

@Kyle_Day,

Sorry it has been so long, I have been trying a number of different things since your last message to get this to work.

I am using the Snippet

SqliteFeatureSource.CreateSpatialIndex to create the index. This has stopped those errors I was originally getting. However, nothing displays. I suspect because this idx table is completely blank. Is there something else I must call to get this table populated?

Thank you!

#6

@Kyle_Day,

I believe I have resolved this issue. The SqliteFeatureSource.CreateView requires that the geometry column be named “geometry”. Nothing else. When named correctly, it would correctly populate those virtual index files and allow the data to display.

Sadly, slower than the base table with the nested queries, but it is working!

Thank you so much for your help and patience.

#7

Hey @JarodH,

Glad to hear that you got things working, albeit a little slower than you had hoped. Hopefully using the base table works best for you.

Thanks,
Kyle