This import type uses a MS SQL Server database as its data source and utilizes the power of the database engine to speed up the data preparation. Therefore Before this import type can be used some preparation of the source database is needed.
The database that the import connects to must contain the source data, and the user connecting to that database must have the appropriate permissions to create and delete tables in that database as well as to read and modify all data.
Since it is usually not wanted that the import process changes the original GSD-Property Map database we recommend that the import connects to a working database which contains views or copies of the data in the original GSD-Property Map database. In the following we discuss different ways how data can be made available.
Making source data available via Linked Server and views
If the original GSD-Property Map database is not on the same database server as the database the import connects to you can create views to the FK_AY and FK_BY tables in the original database by first creating a Server Link and then creating the views using the commands shown in listing 1 and listing 2.
CREATE VIEW FK_AY AS SELECT * FROM OPENQUERY( [<ServerLinkName>], 'SELECT * FROM [<SourceDatabaseName>].[<Scheme>].FK_AY'); |
Listing 1: Creating the FK_AY view for a remote database
CREATE VIEW FK_BY AS SELECT * FROM OPENQUERY( [<ServerLinkName>], 'SELECT * FROM [<SourceDatabaseName>].[<Scheme>].FK_BY'); |
Listing 2: Creating the FK_BY view for a remote database
Making source data available via views
If the original GSD-Property Map database is on the same database server as the database the import connects to you can create views to the FK_AY and FK_BY tables in the original database using commands shown in listing 3 and listing 4.
CREATE VIEW FK_AY AS SELECT * FROM [<OriginalDatabaseName>].[<SchemaName>].FK_AY |
Listing 3: Creating the FK_AY view for a local database
CREATE VIEW FK_BY AS SELECT * FROM [<OriginalDatabaseName>].[<SchemaName>].FK_BY |
Listing 4: Creating the FK_BY view for a local database
Making source data available by copying data
A third way to make the data from the original GSD-Propert Map database is copying the data for the FK_AY and FK_BY tables to the database that the import will connect to. While this approach of course requires you to repeatedly copy the database to get all the latest changes it might speed up the import process (especially when the original GSD database is on a remote server)
Listing 5 shows an example how data can be copied with an SQL script from a Linked Server.
IF (EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FK_AY' AND TABLE_CATALOG = DB_NAME())) BEGIN DROP TABLE FK_AY END
Select * INTO FK_AY FROM OPENQUERY( [<ServerLinkName>], 'SELECT * FROM [<SourceDatabaseName>].[<Scheme>].FK_AY') |
Listing 5: Creating the FK_BY view for a local database. Note that this script deletes and already existing table!
Database Permissions
During the preparation of data this import type creates tables in the database it connects to and after import it will delete these tables again. The accessing user therefore needs the appropriate privileges create and delete tables.
Cleaning up the database
After the import the import process will delete tables it created, in terms of an abnormal termination of the import it might however be that these tables are not cleaned up. If this situation happens all tables starting with RbTemp can be manually deleted.
Changes to the database
Redbex is prepared for the Lantmäteriet database that was in use 2015 - 2021.
Should any column in the source database change names, this needs to be converted to the column names that Redbex expects. This can be simulated by using AS statements as done below.
SELECT *
FROM OPENQUERY([DB-GDXGSD2-T.TRAFIKVERKET.LOCAL], '
SELECT
OBJECTID,
OBJECTID as [FNR_FDS],
objekt_ID as OBJEKT_ID,
externid as EXTERNID,
detaljtyp as DETALJTYP,
kommunkod as KOMMUNKOD,
kommunnamn as KOMMUNNAMN,
trakt as TRAKT,
blockenhet AS BLOCKENHET,
omrnr as OMRNR,
fastighet as FASTIGHET,
ytkval as YTKVAL,
convert(nvarchar(16),adat) as ADAT,
isnull(omrtyp,'''') as OMRTYP,
left(kommunkod,2) as LAN_KOD,
SHAPE,
GDB_GEOMATTR_DATA,
IMPORTDATUM
FROM [GDX_GSD2_T].[V10].registerenhet_yta')
SELECT *
FROM OPENQUERY([DB-GDXGSD2-T.TRAFIKVERKET.LOCAL], 'SELECT
OBJECTID,
null as [FNR_BR],
objektidentitet as OBJEKT_ID,
objektversion as [OBJEKT_VER],
objekttyp as DETALJTYP,
convert(nvarchar(16),versiongiltigfran) as ADAT,
insamlingslage as [INSAM_LAGE],
'''' as XYFEL,
byggnadsnamn1 as NAMN1,
byggnadsnamn2 as NAMN2,
byggnadsnamn3 as NAMN3,
huvudbyggnad as HUVUDBYGGN,
'''' as ANDAMAL_1,
andamal1 as ANDAMAL_1T,
andamal2 as ANDAMAL_2,
andamal3 as ANDAMAL_3,
andamal4 as ANDAMAL_4,
andamal5 as ANDAMAL_5,
null as ANDAMAL_6,
null as ANDAMAL_7,
null as ANDAMAL_8,
null as ANDAMAL_9,
null as ANDAMAL_10,
husnummer as HUSNR,
SHAPE,
GDB_GEOMATTR_DATA,
IMPORTDATUM
FROM [GDX_GSD2_T].[V10].byggnad');
