OSGeo.org › OSGeo Software and Data Projects › Feature Data Objects (FDO) › FDO Users

Adding new featureclass from existing table

7 messages

bhogi

Adding new featureclass from existing table

Reply Threaded

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.

Orest Halustchak

RE: Adding new featureclass from existing table

Reply Threaded

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: [hidden email] [mailto:[hidden email]] On Behalf Of bhogi
Sent: Thursday, October 15, 2009 6:47 AM
To: [hidden email]
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.

--
View this message in context: http://n2.nabble.com/Adding-new-featureclass-from-existing-table-tp3828504p3828504.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

bhogi

RE: Adding new featureclass from existing table

Reply Threaded

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@lists.osgeo.org [mailto:fdo-users-bounces@lists.osgeo.org] On Behalf Of bhogi
Sent: Thursday, October 15, 2009 6:47 AM
To: fdo-users@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.

--
View this message in context: http://n2.nabble.com/Adding-new-featureclass-from-existing-table-tp3828504p3828504.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
fdo-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-users
_______________________________________________
fdo-users mailing list
fdo-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-users

bhogi

Re: Adding new featureclass from existing table

Reply Threaded

In reply to this post by Orest Halustchak

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: [hidden email] [mailto:[hidden email]] On Behalf Of bhogi
> Sent: Thursday, October 15, 2009 6:47 AM
> To: [hidden email]
> 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

Brent Robinson

RE: Adding new featureclass from existing table

Reply Threaded

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: [hidden email] [mailto:[hidden email]] 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:

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

bhogi

Re: Adding new featureclass from existing table

Reply Threaded

Brent,

thank you very much for your lengthy advice!
Fortunately all my FDO scenarios are very simple, so I will take your 2) advice
at the bottom.
At the moment, I simply left classid set to 0, and featureclasses still worked
fine. I suspected this field is ignored if my featureclasses remain simple. You
confirmed this, great!

Thanks again.

Brent Robinson wrote:

> 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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [mailto:[hidden email]] On Behalf Of bhogi
>> Sent: Thursday, October 15, 2009 6:47 AM
>> To: [hidden email]
>> 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
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/fdo-users
> _______________________________________________
> fdo-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/fdo-users
>

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

Brent Robinson

RE: Adding new featureclass from existing table

Reply Threaded

Hi,

One other option I forgot to mention would be to convert the database to a non-FDO one. However, if the FDO metadata is being used to provide some functionality not available through non-FDO databases, then this would not be an option.

If feasible, converting the database is fairly simple. As a first step, the metadata can be disabled by renaming f_schemainfo to something else. Once this is done, a class will automatically be generated for each table, when the datastore is opened via FDO. After that, new tables can simply be added, with no extra effort to expose them through FDO. If everything works ok, the f_ tables can eventually be backed up and removed altogether.

Brent.

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Bhogi
Sent: Friday, October 16, 2009 1:14 PM
To: FDO Users Mail List
Subject: Re: [fdo-users] Adding new featureclass from existing table

Brent,

thank you very much for your lengthy advice!
Fortunately all my FDO scenarios are very simple, so I will take your 2) advice
at the bottom.
At the moment, I simply left classid set to 0, and featureclasses still worked
fine. I suspected this field is ignored if my featureclasses remain simple. You
confirmed this, great!

Thanks again.

Brent Robinson wrote: