ManagedSpatiaLite layer using table view not rendering

Mar 5, 2013 at 7:04 AM
Howdy,
Using Friday's trunk, Windows 7, 4.0 Framework - Full

At this point is it expected that this provider render views?
The provider works well for the basic table(in fact I think it is somewhat faster than the OLD provider). However there seems to be some difficulty when it renders a view. Actually, it seems to obliterate the map. This strategy worked well back in the SpatiaLite provider days.

Table:
CREATE TABLE s (SPCALEASE_ID INTEGER NOT NULL PRIMARY KEY, SPACENAME TEXT(12) NULL, AREASQFT REAL NULL, PERIMETERFT REAL NULL, FACILITY TEXT(50) NOT NULL, ZONE TEXT(256) NULL, FLOOR TEXT(124) NULL, AGENCY TEXT(80) NULL, CLASS TEXT(256) NULL, GEOMETRY BLOB NULL, SECTION TEXT(50) NULL, CURRENTUSE TEXT(256) NULL, PLANNEDUSE TEXT(256) NULL, TAX_STATUS TEXT(40) NULL, BILLING_QU INTEGER NULL, COST_CENT TEXT(50) NULL, UTIL_PERC INTEGER NULL, LOC_ID TEXT(50) NULL, "GEOMETRY_WKB" POLYGON NOT NULL DEFAULT '')

View:
CREATE VIEW vs_01 AS
SELECT "AGENCY" || '(' || "SPACENAME" || ')' AS "TXTCONTENTS", "SPCALEASE_ID" AS "SPCALEASE_ID",
"SPACENAME" AS "SPACENAME", "AREASQFT" AS "AREASQFT",
"PERIMETERFT" AS "PERIMETERFT", "FACILITY" AS "FACILITY",
"ZONE" AS "ZONE", "FLOOR" AS "FLOOR", "AGENCY" AS "AGENCY",
"CLASS" AS "CLASS", "GEOMETRY" AS "GEOMETRY", "SECTION" AS "SECTION",
"CURRENTUSE" AS "CURRENTUSE", "PLANNEDUSE" AS "PLANNEDUSE",
"TAX_STATUS" AS "TAX_STATUS", "BILLING_QU" AS "BILLING_QU",
"COST_CENT" AS "COST_CENT", "UTIL_PERC" AS "UTIL_PERC",
"LOC_ID" AS "LOC_ID", "GEOMETRY_WKB" AS "GEOMETRY_WKB"
FROM "s"
where "FLOOR" like "%First Floor"
ORDER BY "FLOOR"

I use the view as the basis for the vector layer and also as the basis for a corresponding label layer.

r,
dennis
Coordinator
Mar 5, 2013 at 8:04 AM
Please check:
  • ManagedSpatialiteProvider can only read SpatiaLite-Blobs and no plain WKB.
  • Have you registered your view to the geometry_columns table?
Mar 5, 2013 at 12:35 PM
Yes, I am using SpatiaLite-Blobs.
Yes, I just added entry #2 for the view:
1 s GEOMETRY_WKB POLYGON 2 2236 1
2 vs_01 GEOMETRY_WKB POLYGON 2 2236 0

I also added the following to views_geometry_columns:
1 vs_01 GEOMETRY_WKB ROWID s GEOMETRY_WKB

Something Breaks ... since I lose the Map background when I enable the layer.
r,
dennis
Mar 5, 2013 at 1:07 PM
Maybe I am trying to hard here but as a quick note I do get this:
A first chance exception of type 'System.ArgumentException' occurred in System.Data.dll
When I attempt to use the view...
r,
dennis
Coordinator
Mar 5, 2013 at 1:53 PM
Edited Mar 5, 2013 at 1:54 PM
If your view is relying on any functionality provided by SpatiaLite, it is bound to fail with the managed provider.
Except the blobs of course.
Mar 5, 2013 at 2:42 PM
No, none at all. All I did was create a view to separate geometry by floors. I am doing NOTHING special here and it is the same view used by the old spatialite provider.
Coordinator
Mar 5, 2013 at 3:54 PM
Could you post the command text (sql) that the provider wants to execute?
Mar 5, 2013 at 4:10 PM
I must apologize. I do not know what you are asking for. I am doing no manipulation here. Vanilla provider. "vs_01" is the view/"s" is the actual table
Here is the DataSource I am applying to a VectorLayer:
        ManagedSpatiaLite msl = new ManagedSpatiaLite(SpatiaLiteDataSourcePath + MDAD_Tenants, "vs_01", "GEOMETRY_WKB", "SPCALEASE_ID");
...and it does not work. Again more is going wrong than just not rendering this particular layer .... when the map gets refreshed here ... all my already rendered and displayed layers disappear. Even my raster layer no longer is displayed. I even lose the Map(white backgound to windows control grey) I turn the layer OFF and all is well.

If I change to this:
        ManagedSpatiaLite msl = new ManagedSpatiaLite(SpatiaLiteDataSourcePath + MDAD_Tenants, "s", "GEOMETRY_WKB", "SPCALEASE_ID");
All works just fine .....
Mar 5, 2013 at 5:18 PM
Additional info.
In stepping through, this is the SQL that is finally being executed:
SELECT "SPCALEASE_ID", "TXTCONTENTS", "SPACENAME", "AREASQFT", "PERIMETERFT", "FACILITY", "ZONE", "FLOOR", "AGENCY", "CLASS", "GEOMETRY", "SECTION", "CURRENTUSE", "PLANNEDUSE", "TAX_STATUS", "BILLING_QU", "COST_CENT", "UTIL_PERC", "LOC_ID", "GEOMETRY_WKB" AS "smtmp" FROM vs_01 WHERE 1=1

fdt.BeginLoadData() ...ends and I go immediately to fdt.EndLoadData();
Looks like reader is empty. Strange: the above statement works just fine in spatialite_gui.
r,
dennis
Mar 5, 2013 at 6:43 PM
FObermaier,
WOW... I have been stepping and stepping and the issue seems to be that:
private Image GetMap(Map map, LayerCollection layers, LayerCollectionType layerCollectionType, Envelope extent)

is catching an exception: Column 'AGENCY' does not belong to table vs_01. --- now this is just NOT true.
Which originates in my CustomStyle - which is:
   public VectorStyle CustomStyle_AreaLeases(SharpMap.Data.FeatureDataRow row)
    {
        Color c;
        Brush b = null;
        SharpMap.Styles.VectorStyle style = new SharpMap.Styles.VectorStyle();
        string AGENCY = row["AGENCY"].ToString();
        if (AGENCY.StartsWith("MDAD"))
        {
            c = Color.FromArgb(80, 20, 86, 20);
            b = new SolidBrush(c);
            style.Fill = b;
            style.EnableOutline = true;
            style.Outline = new Pen(Color.DarkGreen);
        }
        else
        {
            c = Color.FromArgb(80, 255, 106, 0);
            b = new SolidBrush(c);
            style.Fill = b;
            style.EnableOutline = true;
            style.Outline = new Pen(Color.OrangeRed);
        }
        return style;
    }
....

AND now when I change: string AGENCY = row["AGENCY"].ToString();
TO: string AGENCY = (string)row[8];
It SOMETIMES works....
r,
dennis
Coordinator
Mar 5, 2013 at 7:32 PM
What fieldtype gets set for your column in the CreateTableFromReader in the ManagesSpatialLite rrovider?

"var fieldType = reader.GetFieldType(c);"
Mar 5, 2013 at 7:48 PM
petlof and FObermaier,

Well, low and behold you have added double-quote marks around the field names in the featuredatatable.
Know wonder AGENCY could not be found .... "AGENCY" is now the actual column name. So when I style based upon a column name I have to insert double-quotes.
I can cope with this if this is now the intended design?
r,
dennis
Coordinator
Mar 5, 2013 at 7:58 PM
That sounds like a bug..
Coordinator
Mar 5, 2013 at 7:59 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.