[fdo-users] Adding new featureclass from existing table

[Brent Robinson]

Hi,

If all of your geometries have the same coordinate system then one way to populate the f_ tables automatically would be to do the following:

    - create the table(s) in the FDO enabled database.
    - create a second non-FDO database
    - copy the table(s) to the second database
    - go back to the FDO enabled database and add the revisionnumber and classid columns to the table(s).
    - Use the DescribeSchema and DescribeSchemaMappings commands to generate the feature class and
      Physical mappings, for each table, from the second database. The Physical Mappings keep track of the 
      correspondences between the generated feature class and properties and its table and columns. Use 
      ApplySchema to apply these to the FDO enabled database. This can be done by the following sample 
      code:

    FdoPtr<IConnectionManager> manager = FdoFeatureAccessManager::GetConnectionManager ();
    FdoPtr<FdoIConnection> conn1 = manager->CreateConnection (L"OSGeo.SQLServerSpatial");
    // Customize connect string for your installation
    conn1->SetConnectionString(   
        L"service=<sql_server_instance>;Username=<user>;password=<password>;datastore=<second_database>" );
    conn1->Open();

    // Get the feature schemas from the second database
    FdoPtr<FdoIDescribeSchema>  pDescCmd = 
        (FdoIDescribeSchema*) conn1->CreateCommand(FdoCommandType_DescribeSchema);
    FdoFeatureSchemasP schemas = pDescCmd->Execute();

    // Get the physical mappings from the second database
    FdoPtr<FdoIDescribeSchemaMapping>  pDescMappingsCmd = 
        (FdoIDescribeSchemaMapping*) conn1->CreateCommand(FdoCommandType_DescribeSchemaMapping);
    pDescMappingsCmd->SetIncludeDefaults( true );
    FdoSchemaMappingsP mappings = pDescMappingsCmd->Execute();

    FdoPtr<FdoIConnection> conn2 = manager->CreateConnection (L"OSGeo.SQLServerSpatial");
    // Customize connect string for your installation
    conn2->SetConnectionString( 
        L"service=<sql_server_instance>;Username=<user>;password=<password>;datastore=<fdo_database>" );
    conn2->Open();

    FdoPtr<FdoIApplySchema>  applyCmd = 
        (FdoIApplySchema*) conn2->CreateCommand(FdoCommandType_ApplySchema);
    applyCmd->SetIgnoreStates(true);

    for ( int i = 0; i < schemas->GetCount(); i++ ) 
    {
        FdoFeatureSchemaP schema = schemas->GetItem(i);
        applyCmd->SetFeatureSchema(schema);

        // Apply corresponding physical mappings. These contain mappings between classes and tables,
        // and properties and columns. This causes the feature classes to be latched onto existing
        // tables rather than creating new tables.
        FdoPhysicalSchemaMappingP mapping = mappings->GetItem(conn2, schema->GetName() );
        applyCmd->SetPhysicalMapping( mapping );
        applyCmd->Execute();
    }

The above uses the FDO unmanaged API but if you use the managed API, the code will be similar.

This will only work if the FDO datastore has only one spatial context, and all geometries in the new table have the same coordinate system as this spatial context. If there are multiple spatial contexts, I'll need to dig up some more code to handle this. 

The above can be done for multiple new tables. The steps do not have to be repeated for each individual table.

There are a couple of options for dealing with the classid and revisionnumber:

1) Set default values for these columns. The default for revisionnumber must be set to 0. For classid, check the f_classdefinition row for the table. The default value to use will be in f_classdefinition.classid. Now you can insert rows without having to explicitly set these columns.

2) If you don't plan to use class inheritance or the optimistic locking support provided by revisionnumber, then classid and revisionnumber can be deleted from the table. The corresponding properties must also be removed. This can be done by going into f_attributedefinition and removing the two rows where tablename is your table and columnname is "classid" or revisionnumber". FDO is able to function without these two system properties being present in a feature class. 

The steps to handle classid and revisionnumber can potentially be eliminated by some enhancements to the SQLServerSpatial provider. I'll log a trac ticket for this.

Brent.

-----Original Message-----
From: fdo-users-bounces at lists.osgeo.org [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Bhogi
Sent: Friday, October 16, 2009 5:14 AM
To: FDO Users Mail List
Subject: Re: [fdo-users] Adding new featureclass from existing table

Hi,

Yes, I know about this, tryed it and indeed it works. But the database in use by 
FDO will always have all f_ tables. When these tables are present the provider 
unfortunately does no auto detection.

Thanks.


Orest Halustchak wrote:
> Hi,
> 
> For the SQL Server provider, the fdo metadata tables are optional. You could just create tables that have geometry or geography columns into a new empty SQL Server database and the provider will detect them as feature classes. Have you tried that?
> 
> Thanks,
> Orest.
> 
> -----Original Message-----
> From: fdo-users-bounces at lists.osgeo.org [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of bhogi
> Sent: Thursday, October 15, 2009 6:47 AM
> To: fdo-users at lists.osgeo.org
> Subject: [fdo-users] Adding new featureclass from existing table
> 
> 
> Hi,
> 
> I'm adapting a few apps that export GIS data to SQL server 2008. The goal is
> to make exported data also available to FDO.
> 
> In the beginning I solved the problem of making database tables available to
> FDO by inserting class definitions etc. in f_ tables and adding classid and
> revisionnumber fields to the tables. Althou working this "hack" is not
> acceptable in the long run.
> 
> I want to do this the correct FDO way, but don't know how.
> 
> There are two approaches I was considering:
> 
> 1) Create a table first and make it an FDO featureclass later
> BUT If I create a new feature class intended for the already existing table,
> the fdo will make a new differently named table.
> I could change the values in f_ tables, but that's reverting to "hacking"
> again.
> 
> 2) Make an FDO featureclass and insert new features in corresponding table
> via SQL as I did before
> BUT classid and revisionnumber need to be set. If I set these values via SQL
> I see this as reverting to "hacking" again.
> 
> Is there a way to acomplish this?
> Have I overlooked something?
> Is the 2) way considered as a valid way to insert features in featureclass.
> What if the FDO internals will change regarding these fields...
> 
> Adding new features the FDO way is not an option, there's just too much code
> to adapt.
> 
> Thanks.
> 
_______________________________________________
fdo-users mailing list
fdo-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-users