-- vajalikud GML-failid leiab maaameti kodulehelt
-- http://www.maaamet.ee/index.php?lang_id=1&page_id=272&menu_id=36

-- SQL 2008 Launchi spatial Data demo
-- ilma Filestream osa ja piltideta 
-- alustame andmebaasi loomisest

create database Demo
go
use demo

-- 1st we create table to read XML files in - for simplification
-- teeme tabeli, kuhu loeme kolm GML-faili
create table XMLFailid
(name sysname,
sisu xml
)

-- end then we read 3 dounloaded GML files to this table (correct for actual filenames)
-- ja loeme sinna kolm faili
-- failide asukoht ja nimed peavad vastama scriptile
insert XMLFailid
select 'maakond',*
FROM OPENROWSET (
  BULK 'C:\xmldata\maakond_gml.xml',
  SINGLE_BLOB) as T

insert XMLFailid
select 'omavalitsus',*
FROM OPENROWSET (
  BULK 'C:\xmldata\omavalitsus_gml.xml',
  SINGLE_BLOB) as T

insert XMLFailid
select 'asustus',*
FROM OPENROWSET (
  BULK 'C:\xmldata\asustus_gml.xml',
  SINGLE_BLOB) as T

-- small test for viewing the structure - because big files I extranct only one featureMember
-- väike test, mis võimaldab vaadata nenede failide sisse
-- kuna iga neis koosneb "featurememeber"-itest, siis vaatame üksikuid - näites kolmandat
select name,sisu.query('
declare namespace gml="http://www.opengis.net/gml" ;
//gml:featureMember[3]')
from XMLFailid

-- we create 1st table for counties (Maakond)
-- teeme tabeli Maakond, kuhu loeme andmed XMLi kujul 
-- Iga Featurememberi kohta tekib üks kirje
-- Üks Featuremember on üks maakond
-- infoveerud MNIMI ja MKOOD on feature vabadest elementidest
create table Maakond -- COUNTY
(
kood int identity primary key,
MNIMI nvarchar(50),
MKOOD nchar(10),
sisu xml
)

-- other one for municipalitets (Omavalitsus)
-- samasugune tabel Omavalitsuste jaoks
create table Omavalitsus
(
kood int identity primary key,
MNIMI nvarchar(50),
MKOOD nchar(10),
VNIMI nvarchar(50),
VKOOD nchar(10),
TYYP nchar(2),
sisu xml
)

-- and 3rd for localities (willages)
-- ja saamsugune tabel ka asustusüksuste jaoks
create table Asustus
(
kood int identity primary key,
MNIMI nvarchar(50),
MKOOD nchar(10),
VNIMI nvarchar(50),
VKOOD nchar(10),
TYYP nchar(2),
NIMI nvarchar(50),
ANIMI nvarchar(50),
AKOOD nchar(10),
sisu xml
)

-- fill the 3 tables with actual XML(GML) data - every line is one featureMember

-- täidame tabelid andmetega
-- aluseks on XQUERY päringud
-- nodes meetodiga eraldame featurememberid ja salvestame nad veergu sisu
-- value meetodiga eraldame igast veerud MNIMI ja MKOOD
insert maakond (MNIMI, MKOOD, sisu)
select 
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:MNIMI)[1]', 'nvarchar(50)') as mnimetus,
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:MKOOD)[1]', 'nvarchar(10)') as mkood,
d.query('.') maakonnakaart
from XMLFailid 
cross apply 
sisu.nodes('
declare namespace gml="http://www.opengis.net/gml" ;
//gml:featureMember'
) as T(d)
where name = 'maakond'

-- teine analoogiline päring täidab omavalitsuste tabeli
-- muu on täpselt sama, eraldatavaid veerge on rohkem
insert omavalitsus (MNIMI, MKOOD, VNIMI, VKOOD, TYYP, sisu)
select 
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:MNIMI)[1]', 'nvarchar(50)') as mnimetus,
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:MKOOD)[1]', 'nvarchar(10)') as mkood,
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:VNIMI)[1]', 'nvarchar(10)') as vnimetus,
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:VKOOD)[1]', 'nvarchar(10)') as vkood,
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:TYYP)[1]', 'nvarchar(10)') as tyyp,
d.query('.') vallakaart
from XMLFailid 
cross apply 
sisu.nodes('
declare namespace gml="http://www.opengis.net/gml" ;
//gml:featureMember'
) as T(d)
where name = 'omavalitsus'

-- ja viimasena täidame asustusüksuste tabeli
-- siin on veerge veel rohkem aga muus osas täpselt sama
insert asustus (MNIMI, MKOOD, VNIMI, VKOOD, ANIMI, AKOOD, NIMI, TYYP, sisu)
select 
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:MNIMI)[1]', 'nvarchar(50)') as mnimetus,
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:MKOOD)[1]', 'nvarchar(10)') as mkood,
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:VNIMI)[1]', 'nvarchar(10)') as vnimetus,
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:VKOOD)[1]', 'nvarchar(10)') as vkood,
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:ANIMI)[1]', 'nvarchar(10)') as animetus,
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:KOOD)[1]', 'nvarchar(10)') as akood,
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:NIMI)[1]', 'nvarchar(10)') as nimetus,
d.value('
declare namespace p2="http://www.safe.com/gml/fme" ;
(.//p2:TYYP)[1]', 'nvarchar(10)') as tyyp,
d.query('.') vallakaart
from XMLFailid 
cross apply 
sisu.nodes('
declare namespace gml="http://www.opengis.net/gml" ;
//gml:featureMember'
) as T(d)
where name = 'asustus'

-- checking the results
-- kontrolliks võiks uurida, mis on maakonna tabeli sisu
select * from Maakond

-- now adding one more column - geometry to all 3 tables
-- järgnevalt täiendame kõiki kolme tabelit ja lisame veeru, kuhu arvutame Geomeetria
alter table maakond
add Geom geometry

alter table omavalitsus
add Geom geometry

alter table asustus
add Geom geometry

-- next we fill geometry from GML-data - note the XQUERY
-- we have to exclude attributes and elements not supperted by SQL
-- here we have to use default (not prefixed) namespace for generating XML

-- täidame loodud veeru andmetega
-- selleks kasutame meetodit GeomFromGml
-- põhimõtteliselt võiks sellele ette anda xml-i sisuks featuremember
-- aga kuna SQLi GML ei toeta kõiki GML3 atribuute ja konstruktsioone, siis muudame natuke GMLi struktuuri
-- selleks kasutame XQUERY päringut
-- Päring genereerib MultiSurface 
-- selle sees on iga leitud posList (koordinaatide leotelu) kui eraldi surfaceMember-polygon
-- see ei ole päris korrektne teisendus, aga demo jaosk küllalt hea
update maakond
set Geom = geometry::GeomFromGml(sisu.query(N'
declare default element namespace "http://www.opengis.net/gml";
declare namespace g="http://www.opengis.net/gml" ;
<MultiSurface>
{for $m in //g:posList return 
<surfaceMember>
<Polygon>
<exterior>
<LinearRing>
{$m}
</LinearRing>
</exterior>
</Polygon>
</surfaceMember>
}
</MultiSurface>'), 0)

-- sama päringut kasutame ka omavalitsuste tabeli täitmiseks
update omavalitsus
set Geom = geometry::GeomFromGml(sisu.query(N'
declare default element namespace "http://www.opengis.net/gml";
declare namespace g="http://www.opengis.net/gml" ;
<MultiSurface>
{for $m in //g:posList return 
<surfaceMember>
<Polygon>
<exterior>
<LinearRing>
{$m}
</LinearRing>
</exterior>
</Polygon>
</surfaceMember>
}
</MultiSurface>'), 0)

-- sama päringut kasutame ka asustusüksuste tabeli täitmiseks
update asustus
set Geom = geometry::GeomFromGml(sisu.query(N'
declare default element namespace "http://www.opengis.net/gml";
declare namespace g="http://www.opengis.net/gml" ;
<MultiSurface>
{for $m in //g:posList return 
<surfaceMember>
<Polygon>
<exterior>
<LinearRing>
{$m}
</LinearRing>
</exterior>
</Polygon>
</surfaceMember>
}
</MultiSurface>'), 0)


-- some municipalitets and local willages had "bad" borders :) we have to fix them

-- mingil põhjusel omavalitsuste ja asustusüksuste kaardid ei olnud päris korrektsed
-- muudame need korrektseks, et saaks pindalasid jms arvutada
update omavalitsus set geom = geom.MakeValid()
update austus set geom = geom.MakeValid()


-- NOW SOME TEST QUERIES

-- 1st COUNTY Name, Geometry (wery long string), Geometry type (MultiPolygin) and the space (in Square Meters)
-- väike testpäring, mis annab iga maakonna jaoks, lisaks maakonna koodile ja nimele

-- maakonna geomeetria stringina, geomeetria tüübi (MultiPolygon)
-- maakonna pindala
select MNIMI, MKOOD, Geom.ToString(), Geom.STGeometryType(), Geom.STArea()
from Maakond

-- 2nd municipalitets in one county ordered by space
-- Võrumaa omavalitsused, pindala järgi - pindala täis-ruutkilomeetrites
select MNIMI, VNIMI, cast((Geom.STArea() / 1000000) as int) from omavalitsus 
where MNIMI like 'Võru%'
order by Geom.STArea() 

-- willages by space
-- asustusüksused suuruse järgi
select *, Geom.STArea() from asustus
order by Geom.STArea() 

-- Create table for "Heroes"
-- teeme kangelaste tabeli (hetkel ilma pildita)
create table Heroes
(kood int identity primary key,
nimi nvarchar(50),
geom geometry
)

-- We have to insert some rows into table
-- nii näiteks saaks kangelaste tabelisse paigutada hennu
-- nii et tema asukoht oleks Hüti külas
insert Heroes (nimi, geom)
select 'henn', geom from asustus where AKood = 1972

select kood, nimi, geom.AsGml() asukoht from Heroes

-- other example to insert rows
-- siin oleks teine võimalus, paigutada Henn koos etteantud geograafiaga
-- (kasutatud Gml on saadud eelneva päringuga)
insert Heroes (nimi, geom)
values ('Henn',
geometry::GeomFromGml(N'
<MultiSurface xmlns="http://www.opengis.net/gml">
  <surfaceMembers>
    <Polygon>
      <exterior>
        <LinearRing>
          <posList>653447.361558597 6387804.0793415 653485.758485834 6387811.36151736 653596.64616363 6387856.70961246 653617.665171213 6387878.55614002 653634.38107488 6387910.66391538 653649.772946575 6387959.9841064 653662.020242331 6387967.10077826 653685.687313861 6387966.43876227 653708.692369404 6387976.03799408 653726.070289058 6387997.88452165 653747.585808631 6388007.98026545 653799.388559601 6387998.21552964 653812.297871345 6388000.03607361 653843.578126723 6388021.55159318 653868.90023822 6388029.16477703 653894.718861707 6388021.55159318 653948.011148648 6387965.94225028 653960.258444404 6387957.99805844 653977.470860062 6387965.94225028 653985.746059898 6387986.96125787 654002.627467562 6388005.99421749 654012.723211362 6388012.94538535 654029.770123023 6388011.95236137 654043.837962743 6387996.06397769 654061.877898385 6387967.92829825 654076.938762086 6387957.00503446 654097.957769668 6387955.0189865 654153.401608566 6387955.0189865 654178.39271207 6387957.99805844 654200.570247629 6387972.06589816 654224.071815162 6387999.20855362 654216.62413531 6388024.53066512 654205.53536753 6388048.03223265 654207.852423484 6388059.94852042 654215.134599339 6388070.04426422 654247.407878698 6388103.97258354 654282.329222004 6388098.67645565 654307.651333501 6388084.93962392 654334.131972975 6388074.34736813 654351.178884636 6388074.34736813 654377.825028106 6388079.97450402 654389.410307876 6388098.01443966 654401.326595639 6388108.77219945 654861.758714489 6387741.68433474 654962.219640493 6387867.96388423 654990.686327927 6387838.66967681 655018.822007368 6387822.78129313 655059.701494556 6387812.35454134 655112.497269506 6387829.070445 655136.99186102 6387831.22199696 655209.317107582 6387819.80222119 655245.396978865 6387826.75338905 655377.965680231 6387866.30884426 655456.911086662 6387934.8274989 655530.229357205 6387926.71780306 655614.139883537 6387953.86045852 655664.784106531 6387556.15435443 656102.873185825 6387573.69777808 656124.223201401 6387579.98692995 656104.528225792 6387675.15172806 656312.070237668 6387739.20177479 656616.432087619 6387689.55057578 656634.141015267 6387771.30955015 656902.919505926 6387647.18155262 656970.114128591 6387673.99320009 657046.742479068 6387651.31915254 657393.142344184 6387749.46302259 657682.112322441 6387574.35979407 656920.959441568 6386669.88045204 656911.691217752 6386652.99904437 656649.532886961 6386346.32013847 656659.297622767 6386277.13946784 656680.647638343 6386278.13249182 656707.9557978 6386283.42861972 656725.333717455 6386290.54529157 656732.61589331 6386291.04180356 656745.359701057 6386285.74567567 656786.901204232 6386232.94990072 656786.239188245 6386207.46228523 656778.129492406 6386175.35450986 656779.122516386 6386149.70139037 656786.735700235 6386122.72423891 656788.721748195 6386081.01723174 656798.320980005 6386064.46683207 656822.815571518 6386062.9772961 656827.946195416 6386067.776912 656905.567569873 6385863.21397206 656616.266583623 6385669.40879192 656457.38274678 6385728.65922274 656395.815260004 6385584.6707456 656100.390625874 6385714.26037502 656105.190241779 6385683.64213563 656094.59798599 6385575.40252178 656092.446434032 6385461.03926006 656130.181345282 6385268.39260788 656147.39376094 6385139.63049844 656156.165472766 6385065.98121991 656176.687968358 6384983.56022954 656016.811107535 6385032.05290058 655974.111076384 6385011.36490099 655914.364133571 6384972.14045377 655889.207526071 6384940.19818241 655815.558247535 6384753.67517811 655791.725672008 6384719.91236278 655752.832232781 6384686.48055545 655402.460271744 6385243.73251237 655362.40830454 6385509.3664271 655028.25573518 6385464.51484399 654906.279289605 6386121.40020693 654976.121976217 6386305.44065128 654837.595130969 6386512.65165516 654784.137340032 6386557.83424626 654771.890044275 6386546.0834625 654759.973756512 6386545.7524545 654696.751229768 6386639.42771664 654686.820989966 6386647.53741248 654662.988414439 6386655.31610033 654630.053119094 6386649.85446844 654600.096895689 6386629.33197284 654558.224384521 6386562.63386217 654538.198400919 6386547.07648648 654508.076673518 6386547.57299847 654483.251074011 6386560.15130222 654463.721602399 6386579.01875784 654449.157250689 6386611.95405319 654439.227010886 6386672.03200399 654447.667714718 6386697.18861149 654505.594113567 6386739.39213066 654516.682881347 6386753.95648237 654523.634049209 6386797.3185295 654514.034817399 6386815.02745715 654494.008833797 6386822.806145 654455.11539457 6386788.38131368 654430.620803057 6386783.91270577 654414.401411379 6386802.7801614 654407.781251511 6386808.73830528 654393.2168998 6386810.22784125 654357.302532514 6386782.4231698 654240.291206839 6386729.13088286 654213.976071362 6386727.64134689 654186.833415902 6386742.86771459 654162.338824389 6386781.76115381 654156.380680507 6386826.61273692 654141.319816806 6386839.85305666 654106.563977497 6386838.0325127 653987.070091872 6386898.4414715 653969.03015623 6386920.28799906 653969.361164224 6386952.56127842 653988.559627842 6386976.22834995 654010.406155408 6386989.96518168 654021.991435178 6387005.85356536 654021.991435178 6387017.76985312 654013.385227349 6387035.47878077 653983.429003944 6387049.71212449 653965.223564306 6387048.88460451 653895.54638169 6387007.34310133 653882.306061954 6387008.17062132 653853.011854536 6387058.98034831 653838.116494832 6387073.71020401 653812.959887332 6387082.97842783 653785.155215884 6387076.68927595 653763.970704305 6387082.31641184 653754.702480489 6387096.38425156 653751.392400555 6387128.16101893 653729.214864996 6387171.85407406 653726.897809042 6387189.72850571 653732.193936937 6387204.12735342 653753.212944519 6387223.82232903 653780.686607973 6387228.29093694 653793.264911723 6387239.71071271 653794.092431707 6387259.40568832 653781.017615966 6387270.16344811 653760.660624371 6387261.55724028 653749.902864585 6387262.71576826 653748.744336608 6387277.11461597 653770.25985618 6387292.00997568 653774.397456098 6387318.98712714 653768.439312216 6387327.26232698 653749.571856591 6387327.92434296 653735.835024864 6387319.81464712 653723.918737101 6387289.69291972 653706.87182544 6387284.56229582 653697.603601624 6387301.27819949 653701.906705539 6387326.43480699 653709.850897381 6387360.69413431 653690.817937759 6387405.38021342 653689.659409782 6387421.93061309 653695.452049667 6387442.94962068 653715.974545259 6387463.30661227 653712.498961328 6387474.39538005 653698.100113614 6387477.53995599 653688.500881805 6387491.77329971 653687.342353828 6387506.00664342 653699.920657578 6387515.10936324 653725.077265078 6387516.26789122 653753.047440522 6387495.74539563 653774.066448105 6387477.37445199 653799.554063598 6387483.00158788 653820.07655919 6387507.1651714 653817.759503236 6387525.37061104 653795.250959684 6387542.74853069 653736.993552841 6387545.23109064 653715.147025275 6387584.45553786 653709.354385391 6387616.39780923 653676.750098038 6387677.30328002 653661.358226344 6387690.21259176 653583.736851887 6387709.41105538 653569.00699618 6387725.96145505 653572.813588104 6387757.40721443 653567.682964206 6387771.64055815 653549.146516574 6387777.92971002 653531.27208493 6387767.00644624 653512.073621311 6387740.85681476 653494.03368567 6387745.32542267 653455.636758433 6387772.13707014 653447.361558597 6387804.0793415</posList>
        </LinearRing>
      </exterior>
    </Polygon>
  </surfaceMembers>
</MultiSurface>', 0)
)

-- query for programm - to generate the pieces for drawing the map

-- nyyd proovime tekitada päringu, mis annab iga maakonna tykid 
-- enamvähem talutav kiirus (0:06)
select
M.MNIMI, M.MKOOD,
g.p.value('.', 'nvarchar(MAX)') as pointList
from 
(select 
m.MNIMI, m.Mkood, m.geom.AsGml() gml
from Maakond m ) as M
cross apply M.gml.nodes('
declare namespace g="http://www.opengis.net/gml";
//g:posList') as g(p)

-- this is wariant but more slow - all pieces will be numbered (not neccessary)
-- aeglasem (2:30) aga tükkidel on jrknr
select
M.MNIMI, M.MKOOD,
ROW_NUMBER() over (partition by MNimi order by MNimi) as rnum,
g.p.value('.', 'nvarchar(MAX)') as pointList
from 
(select 
m.MNIMI, m.Mkood, m.geom.AsGml() gml
from Maakond m ) as M
cross apply M.gml.nodes('
declare namespace g="http://www.opengis.net/gml";
//g:posList') as g(p)


-- one more table - ESTONIA
-- teeme veel ühe tabeli - Eesti
-- sinna tuleb 1 kirje, ja sela hakkab paiknema Eesti tervikkaart
create table Eesti
(geom geometry)

-- Very nice 4-line query to create estonian map from counties

-- järgmine neljarealine script paneb eesti kaardi kokku maakondade kaardist
-- see on ka üks näide kuidas SQLi abil kursoreid kasutamata saada jooksvat agregaati
-- kokkupanemiseks kasutan STUnion meetodit, mida rakendan järjest igale maakonnale
declare @g as geometry
select top 1  @g =  geom from Maakond
select @g = @g.STUnion(geom) from Maakond
insert eesti values (@g)

-- Esonian area in sq meters
-- Eesti pindala
select geom.STArea() from Eesti

-- number of polygons in counties and in estonia
-- mitmest tükist koosneb iga maakond ja mitmest eesti
select MNIMI, geom.STNumGeometries() as tükke from Maakond
union all
select 'Eesti', geom.STNumGeometries() from Eesti

-- Max-Min coordinates of Estonia
-- eesti ala "ümbrik"
-- see on ristkülik, milles ta paikneb
select geom.STEnvelope().ToString() from Eesti

-- Few views for 3 table, with areas, centroids and distance from center
-- teeme paar vaadet

-- maakondade vaade
-- lisaks maakonna nimele ja koodile tema
-- pindala, keskkoht ja kaugus eesti keskpunktist
create view vMaakond
as
select M.MKood, M.Mnimi,
M.geom.STArea() as pindala,
M.geom.STCentroid() as keskkoht,
M.geom.STCentroid().STDistance(E.geom.STCentroid()) as kauguskeskelt
from Maakond M cross join Eesti E

-- MORE SAMPLE QUERIES

-- 5th - what county is more "centric" and what more "province"
-- vaatame, milline maakond on eesti mõttes "keskemal" ja mis "ääreala"
select * from vMaakond
order by kauguskeskelt --desc

-- samasugune vaade omavalitsuste jaoks
create view vOmavalitsus
as
select O.VKood, O.VNimi, M.Mkood, M.Mnimi,
O.geom.STArea() as pindala,
O.geom.STCentroid() as keskkoht,
O.geom.STCentroid().STDistance(M.geom.STCentroid()) as kauguskeskelt
from Omavalitsus O
join Maakond M on O.MKOOD = M.MKOOD


-- milline Harjumaa vald on "kesksem"
select * from vOmavalitsus 
where Mkood = '0037'
order by kauguskeskelt

-- veel üks vaade - seekord asustusüksuste kohta
create view vAsustus
as
select A.AKOOD, A.NIMI, A.ANIMI, O.VKOOD, O.VNIMI, M.MKOOD, M.MNIMI,
A.geom.STArea() as pindala,
A.geom.STCentroid() as keskkoht,
A.geom.STCentroid().STDistance(M.geom.STCentroid()) as kauguskeskelt
from Asustus A
join Omavalitsus O on A.VKOOD = O.VKOOD
join Maakond M on M.MKOOD = A.MKOOD

-- milline asustusüksus on Harjumaa keskmes
select * from vAsustus
where Mkood = '0037'
order by kauguskeskelt

-- milline asustusüksus on Eesti keskmes
select A.nimi, A.Vnimi, A.Mnimi,  A.keskkoht.STDistance(E.geom.STCentroid())
from vAsustus A 
cross join Eesti E
order by 4


-- 6th - in what place is residing "heroe" Henn
-- kus paikneb kangelane (henn)
select
nimi,
g.p.value('.', 'nvarchar(MAX)') as pointList
from 
(select 
nimi, geom.AsGml() gml
from heroes H ) as H
cross apply H.gml.nodes('
declare namespace g="http://www.opengis.net/gml";
//g:posList') as g(p)

-- 7th - in what willage is residing Henn
-- kus (asulas, omavalitsuses, maakonnas) paikneb Henn
-- kasutame meetodit STWithin
declare @g geometry
select @g = geom.STCentroid() from Heroes where nimi = 'henn'
select @g.ToString()
select MNIMI, VNIMI, NIMI
from Asustus
where @g.STWithin(geom) = 1

-- 8th - what counties are neibors
-- millised maakonnad on naabrid
-- kasutame meetodit STTouches
select 
m1.MNIMI, m2.MNIMI
from Maakond m1
join Maakond m2 on m1.geom.STTouches(m2.geom) = 1

-- 9th what size is city parts in Tallinn
-- kui suured on Tallinna linnaosad (ruutkilomeetrites)
select 
NIMI, geom.STArea() / 1000000
from Asustus
where VNIMI like 'Tallinn%'
order by 2 desc

-- kes (asustusüksused) on Tallinna linnaosade naabrid
select 
a1.NIMI, a2.NIMI, a2.VNIMI
from Asustus a1
join Asustus a2 on a1.geom.STTouches(a2.geom) = 1
where a1.VNIMI like 'Tallinn%'