SqliteFeatureLayer displays no content but has values

[Damian_Hite]

Hi,

I’ve created a simple project to write data to a SQLite database and am using the SQLiteExtension with SqliteFeatureLayer to display info on a map.

The layer seems to have all the data as I would expect, but nothing displays on the map. Do I need some other extension or maybe conflicting dll? I got the System.Data.SQLite and associated packages from NuGet, so maybe there is a version conflict? I attach a screen shot of the packages directory because I’ve had to remove that from the project due to size.

To further illustrate the point, I used GetAllFeatures and populated an InMemoryFeatureLayer from the SqliteFeatureLayer. This layer displays on the map as expected.

Another question I have on this process is when I create the Shots table, the “name” field is automatically added. Why is this field reserved and can I use it in my database design safely?

Along this line, the SQLite guide seems to indicate that when using CreateTable that the associated spatial index is automatically created. This is indeed not happening as I get errors about idx_Shots_geom not existing unless I run CreateSpatialIndex.

LiteMap.zip (63.6 KB)

[Ethan]

Hi Damian,

It looks your project works well in my machine, I tested it with our latest 9.0.x.0 dlls, and I don’t need comment the column “Name”, because it won’t be automatic generated when I tested it.

The only different is my Nuget dlls looks have one more folder for 1.0.102

But when I view the project it looks the version still be 1.0.104

I think you should want to reference our latest dlls and test again.

Regards,

Don

[Damian_Hite]

Hi Don,

No joy with latest dll’s.

I would also say that the project isn’t working right for you either. If you look carefully at the style parameters for the SqliteFeatureLayer, you will see it makes red points and the InMemoryFeatureLayer are assigned a smaller blue point. So, your image is not showing the data either as there are no red points.

I still have the issue with the Name field already existing. See attached screen shot.

You should also please refer to the boundary shape I am trying to process for the SqliteFeatureLayer. If you set a break point at rectFl, the values shown are not correct …

I am curious, does the extension require other files to be installed on System32 folder? There is really no documentation on the use of SqLiteExtension, so I only have added a reference to it in my project.

Can you please also comment on why I need to create the spatial index when the documentation says it will be created with the table. To be honest, my goal is not to use ThinkGeo CreateTable method as I need to add relationships, foreign keys, etc. to my db and I don’t see that the tools are there to do that with the SqLiteExtension. In fact, if I try and create the db model using the Sqlite libraries from NuGet and then subsequently try and add spatial index with ThinkGeo, I get errors.

Please can you make a more thorough review of the problem.

Regards,
Damian

[Ethan]

Hi Damian,

1. For your duplication column name issue, I still cannot reproduce that, are you using latest 9.0.x.0 or 9.0.0.x? Please use 9.0.x.0 for test. And the Sqlite don’t related with unmanaged dlls, please don’t worry about that.

2. I downloaded a 3rd part utility named SqliteStudio and view the table which is created, I found our code will automatic build a column named “Geometry”, and your code build a column named “Geom”, I think you can remove that and saved your data into “Geometry”.

3. As below is some code we use:
   a. Render shapes, that’s why the red points hadn’t been render correct, it looks the index build failed and it don’t contained any record, I think that maybe related with the main page format.
   select “geom” as “geom”,id from “Shots” WHERE Shots.id IN (SELECT id FROM idx_Shots_geom WHERE minx < 2186.27367237486 AND maxx > -687.273672374864 AND miny < 3668.74474252285 AND maxy > 1330.25525747715)

b. Get features, that’s why you can read data from table.
select “id”,“geometry”,“geom” as “geom”,“LineName”,“Name”,“Patch”,“SurveyId”,“PropertyId” from “Shots”

c. Build index, it looks the statement is correct, but I hadn’t found why it cannot build the index correct, I will go on research that, and if you have anything news about it please let me know.
CREATE VIRTUAL TABLE idx_Shots_geom USING rtree_i32(id,minx, maxx,miny, maxy)

Regards,

Don

[Damian_Hite]

Hi Don,

1. I am using 9.0.0.541, but I also get the failure to add Name column when using 9.0.541.0. I also downloaded sqlitestudio. If you look at Shots table Structure, you will see it shows “name” in lowercase and all fields I provide in the code start with a capital letter.

2. I’ve dropped “geom” column and started using “geometry” for adding data. The add works fine and the index is created, but as you say, it is not being populated with anything. For note, I was using “geom” because that is what is required for Thinkgeo when using Sql Server.

3a. I confirm the index contains no data.

3b. Okay, I get it now. The Shots table contains all the data as we expect, it’s this virtual table that’s causing the problem.

3c. After updating the column from “geom” to “Geometry”, the fl.GetBoundingBox() command errored out with “No current row”. I found that it’s actually lowercase “geometry” as field name to get it back. Is there maybe also an issue with the projection? The numbers you show in 3a are meters, but fl.GetBoundingBox() shows odd and very tiny numbers (i.e. -5e-6). I note that there doesn’t appear to be a way to indicate what coordinates are being loaded into “geometry” column, but I can imagine that this may be why an index fails to build. Finally, I am confused why we provide “Shots” as the table for the SqliteFeatureLayer if this virtual index is supposed to be doing all the work. I guess it’s for efficiency?

I hope we can get this resolved in the next day or so. I have a POC pending this assessment.

Regards,
Damian

[Ethan]

Hi Damian,

I tested both 9.0.541.0 and 9.0.0.541. The 9.0.541.0 works well, 9.0.0.541 have the problem you mentioned, it default generate a column “name”. So please use 9.0.541.0 and it should works.

And it looks the CreateSpatialIndex API don’t works well, it only generate the index table but hadn’t insert index data into it. So I modified your test code and it works well now.

Please use my modified data and the 9.0.541.0, or you can directly create the table by sql statement but don’t call our API for that if you want to use 9.0.0.541 version.

Form1.cs (7.8 KB)

Wish that’s helpful.

Regards,

Don

[Damian_Hite]

Hi Don,

Looks like I’ve got everything working in my test project. I do have some more question for you on this index issue I am hoping you can help with though.

1. I want to use Where clause on my SqliteFeatureLayer, but this doesn’t work. I guess because the index has to have those fields as well? How to add them if I use CreateSpatialIndex command?

2. If I edit a point in the SqliteFeatureLayer, do I have to recreate the index? What’s the best way to do this?

3. My tables have primary keys to cascade delete. How do I go about ensuring the indexes are cleared? Is it manual process to delete the values from the tables? I notice that if I just clear the indexes that they don’t work right when new data is added. Almost like I have to remove them and create them again…

Thanks,
Damian

[Damian_Hite]

Hi Don,

I spent some more time learning from sqlite site and seem to have answered my own questions. I’ll reply here with what I found out for the rest of the community.

1. The Where clause wasn’t working because I didn’t preface the field with the tablename (i.e. tablename.field). This worked previously with sql 2008 layer, but the rtree index method creates a multi-table query and hence you need to be explicit with what table the fields are from. Also, you do not want to add fields to the spatial index as this causes other problems.

2. I can do an update query based on the rowid and take care of this issue fairly quickly. Since the accuracy of this index relies on good location info, it is not advised to leave it as is or you risk incorrect spatial query results.

3. I’ve opted not to clear the indexes because of rowid. If you clear a virtual table, it restarts the rowid at 0 again whereas clearing a regular table does not reset automatically. Subsequently, the associated id from the data table no longer matches the index rowid and ThinkGeo finds no data to draw on screen. I suppose the index can lose performance with many orphaned records, but I guess it’s less risky than trying to manage id’s from all the other tables.

Regards,
Damian

[Ethan]

Hi Damian,

Thanks for your share!

I hadn’t reply you because I am not in office yesterday.

In fact I am also learning Sqlite, so your share is helpful, I think our Sqlite feature layer need get enhancement in future.

Regards,

Don