Transcript Flora Case

Flora Case
Distribution data on plants in Cleveland
area are held on a simple database.
Basically records of names and locations.
plant data held is similar to
http://scmintranet.tees.ac.uk/users/u0000667/cweb2
003/slides/flora_plant.ppt
Distribution data is held as (tetrad
records-column misnamed)reference_no
grid1km
101
NZ640220
102
NZ420220
106
NZ480340
107
NZ440160
107
NZ560240
108
NZ400140
108
NZ400220
108
NZ440140
A low cost mapping system exists.
However, it is not capable of reading data directly
from the database and requires data in specific
formats.
SQL queries or transactions are required which
produce the following data which can exported into
the mapping systemSpecies Data
A species list consisting of a reference number
and a species naming information.
e.g.
2 Nymphaea alba White Water Lily
11 Pseudofumaria lutea Yellow Corydalis
3 Nuphar lutea Yellow Water Lily
Boundary Data (of map)
A list of eight figure map references defining the
site boundary terminated by -1
NZ489367
NZ485367
NZ483364
NZ480368
-1
Species data easily generated using SQL
using simple query.
Boundary data are base table data i.e.
held in database.
Species Distribution Data
A distribution list consisting of a list of species
reference number and map references,
terminated by –1.
e.g.
2
NZ400140
NZ480140
NZ520120
NZ580140
-1
3
NZ440220
NZ480140
NZ500140
NZ520120
NZ580140
-1
Requires a transaction
to generate this data
This data is used by the mapping system to produce output
that looks like the following-
Blechnum spicant Hard Fern
Create Procedure distdmapdata
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
As
/* set nocount on */
declare @x int
select @x=1
while @x<342
begin
insert into dmapdatadist values(@x)
insert into dmapdatadist select grid1km from
record where reference_no =@x
insert into dmapdatadist values('-1')
select @x=@x+1
end
return
Creates a table with one column. Contains
the mapping data which can them be
exported to the mapping system.
Alter Procedure distdmapdata_rareplants
/*
(@parameter1 datatype = default value,
@parameter2 datatype OUTPUT)
*/
As
/* set nocount on */
declare @x int
select @x=1
while @x<342
begin
if exists(select grid1km from record,plant where
record.reference_no=plant.reference_no and
record.reference_no =@x and rare_or_scarce='y'
)
begin
insert into dmapdatadist values(@x)
insert into dmapdatadist select grid1km from
record,plant where
record.reference_no=plant.reference_no and
record.reference_no =@x and rare_or_scarce='y'
insert into dmapdatadist values('-1')
end
select @x=@x+1
end
return