Hi
It's possibly and I'm using such a solution in production, but there is
some potential problems/cevats
It's not nessesary with two instances of SQL server, but just to databases
on the same instance.
One with a spatialware table (upload it with easyloader an remember to
create a unique integer index - SW_member) and another with your attribute
data.
The next step is to create a view in the spatialware spatialized database,
but with MI_PRINX as alias for your spatialware tables unique integer index
(normaly SW_member).
Its imple to get tables form another databse on the same server instance,
just prefix with the name
Here is an exsample where ESDHGoProStatistik is my attribute database (a
dump from a Notes document managment solution)
CREATE VIEW dbo.aktivesager
AS
SELECT b.journalnr, b.sag, b.kontor, b.dok_dato, b.brev,
a.SW_MEMBER AS MI_PRINX, a.SW_GEOMETRY,
a.MI_STYLE
FROM dbo.BAGSAGERESDH a INNER JOIN
ESDHGoProStatistik.dbo.aktive b ON a.NotesID = b.vNotesID
The next step is to add a entry to mapinfo.mapcatalog in the spatialware
spatialized database
You has to add the name and can clone the rest from the the ordenary
spatialware table
14,3 AKTIVESAGER dbo SW_GEOMETRY 452939,907752 6229141,685627
553484,492933 6335426,520102 "Earth Projection 8, 28, ""m"", 9, 0,
0.9996, 500000, 0 Bounds (-7746230.6469, -9998287.38389) (8746230.6469,
9998287.38389)" Symbol (34,16744448,10) Pen (1,2,0) Pen
(2,2,6356832) Brush (8,5308240,16777215) NO_COLUMN NO_COLUMN 1
MI_STYLE
It works nice if relativly few records is returned, but we has found the
performance less satistactory with a large number of complex objekts - in
this case, we are using another appoach and is creating a new table based
on a view once a day and is creating normal r-trees for fats access.
Here is an exmple (and agin with a crossdatabase join) and we found it more
stable just to trunkate the table, instead of deleting it and creating a
new instance every night
use VIROKASW
--Drop Rtree-indexes for ejer-tabellen
exec sp_sw_drop_rtree 'dbo', 'TBL_EJER', 'SW_GEOMETRY', 'mi_prinx'
GO
--Drop ejer-Tabel
if exists (select * from dbo.sysobjects where id =
object_id(N'[TBL_EJER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
TRUNCATE TABLE [TBL_EJER]
GO
use VIROKASW
--update ejer-tabel
INSERT INTO TBL_EJER ([ejer_navn] ,[ejer_conavn] ,
[ejer_skoede_dato], [ejer_adr_beskyt],
[ejer_adr] , [ejer_udv_adr] ,
[ejer_post_adr] , [ejerforhold_kode] ,
[ejer_andel_naevn] , [ejer_andel_tael],
[ejer_koebesum] , [komnr],
[komnavn] , [elavnavn] ,
[mas_matrnr] , [ejd_nr],
[SW_GEOMETRY] , [mi_style])
SELECT EJER_NAVN, EJER_CONAVN,
EJER_SKOEDE_DATO, EJER_ADR_BESKYT,
EJER_ADR, EJER_UDV_ADR,
EJER_POSTADR, EJERFORHOLD_KODE,
EJER_ANDEL_NAEV,EJER_ANDEL_TAEL,
EJER_KOEBESUM,b.KOMNR,
b.KOMNAVN, b.ELAVNAVN,
b.MAS_MATRNR, b.EJD_NR, b.SW_GEOMETRY,b.MI_STYLE
FROM VIMapRegSW.dbo.MATRIKEL092004 as B,
(SELECT distinct OIS_Viborg.dbo.CO11700T.EJER_NAVN,
OIS_Viborg.dbo.CO11700T.EJER_CONAVN,
OIS_Viborg.dbo.CO11700T.EJER_SKOEDE_DATO,
OIS_Viborg.dbo.CO11700T.EJER_ADR_BESKYT,
OIS_Viborg.dbo.CO11700T.EJER_ADR, OIS_Viborg.dbo.CO11700T.EJER_UDV_ADR,
OIS_Viborg.dbo.CO11700T.EJER_POSTADR,
OIS_Viborg.dbo.CO11700T.EJERFORHOLD_KODE,
OIS_Viborg.dbo.CO11700T.EJER_ANDEL_NAEV,OIS_Viborg.dbo.CO11700T.EJER_ANDEL_TAEL,
OIS_Viborg.dbo.CO11700T.EJER_KOEBESUM,OIS_Viborg.dbo.CO11700T.KOMMUNE_NR,
OIS_Viborg.dbo.CO11700T.EJD_NR
FROM OIS_Viborg.dbo.CO11700T) as A
WHERE b.KOMNR = A.KOMMUNE_NR AND
b.komejd = A.EJD_NR and
A.EJD_NR > 0
go
--Create Rtree-indexes
exec sp_sw_create_rtree 'dbo', 'TBL_EJER', 'SW_GEOMETRY',
'mi_prinx',null, 200, 4000
go
We are quite satisfied with spatialware (there is no need to a lot of
performancetuning like SDE or Orcale and we are using significant less
ressources on DBA duties, than my colleagues who is running Oracle), but I
must also admit, that we has seen two problems with Spatialware, one is a
performance issue, where both SDE and Oracle is significantly faster to
redarw a large number og objects (could it be the Spatialware/Mapinfo ODBC
bottelneck and limit of 2 Mbit/s ???), the other is a curser error, where
the reult is, that spatialware stops running and its nessesary to restart
the SQL server (but i must also admit, that we sometimes has a heavy load
on our SQL server)
Ole Gregor,
Udviklingskonsulent i Milj� og Teknik
Viborg Amt
(45) 87 27 13 07
Karl K
<[EMAIL PROTECTED]
com> Til
mapinfo
04-05-2005 01:43 <[email protected]>
cc
Emne
MI-L Spatialware for SQL Server
linkage with a separate SQL Server
database of attributes
I have a complicated database that has many cross linkages in it. It is
currently in Access and is linked to MapInfo TABs. I am considering moving
the TABs and the attribute database into Spatialware for SQL Server to help
maintain data integrity.
My question is how to handle the separate database that has the attributes?
I could make one instance of SQL Server for the database, and load the TABs
via easyloader into a separate instance of SQL Server. BUT at that point I
will have a spatial database and a separate attribute database. Has anyone
ever tried linking the two databases together? Note that the attribute
database is too complex to turn into a flat table of attributes. Any hints
or suggestions are greatly appreciated.
Karl
McElhanney Consulting Services Ltd.
L100 - 780 Beatty Street 450 - 999 8th Ave SW
Vancouver, BC Canada Calgary, AB Canada
V6B 2M1 T2R 1J5
Tel (604) 683-8521 Tel (403) 612-8521
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---------------------------------------------------------------------
List hosting provided by Directions Magazine | www.directionsmag.com |
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Message number: 16387
