Re: I need a 4D (may be 5D or 6D) data space

Martin Hvidberg <Martin_at_Hvidberg.net> wrote in message news:<3C3C1B70.1C35E5A_at_Hvidberg.net>...
> Newbee - Please bare...
>
> I need to establish a database. I know what I want, but not how to get
> there.

No problem.

>
> I need a database to hold a number of parameters that I will access from
> another program. The other program is a GIS (Geographical Information
> System), but I don't think that is very important for my question.
>
> I have a visual idea about the database that I need.
> Considering a ordinary table as a two dimensional (2D) data space, with
> the two dimensions represented by rows and cols, respectively. I need a
> 4D (may be 5D or 6D) data space. I should be able to hold a small number
> of information for each of a limited number of situations in a forest
> environment. The situation in the forest is defined by four parameters
> each representing the four dimensions. A "soil type" (dimension 1), a
> "tree species" (dimension 2), a "management type" (dimension 2) and a
> "precipitation value" (dimension 2).
>
> Getting information out of the database will (as I see it) require
> looking up a location in a 4D data space. I know the soil type, tree
> species, the management and precipitation. I need the data base to
> return a number of values (less than ten floats) which are associated
> with that given forest situation. I can think of this also as a function
> returning a block of values e.g. F(soil, species, management,
> precipitation)=Result. Where Result are a array or some other data
> structure holding the relevant values.
>
> The 4D data space are not even that big. We expect to be using 7
> soiltypes, 5 tree species, 3 managements and 9 precipitation intervals.
> This gives less than 1000 possible locations in the 4D data space.
>
> The tricky part
> There is a catch, of cause, why I don't implement this as a simple .CSV
> file.
> 1) I need this to be accessible, in an easy way, from the GIS system.
> Specifically, the programming language Avenue, from ESRI.
> This in terms mean that a ODBC and SQL enabled access would be
> preferable.
> 2) We need SPEED. To meet acceptable reply times for the system as such
> we would like close to 10.000 records / second, out of the data base.
>
> Q: Is this possible - How do I do - What data structure is optimal?
>
> All comments and suggestions are welcomed...
>
> Best Regards
> Martin Hvidberg (mhv_at_fsl.dk)
>
> PS. We have access to ms-access, a ms-SQL-server and a MySQL server.

OK. Assuming the approximately ten floats are consistent over any combination of forest situation parameters (i.e. you are interested in the same qualitative set of data no matter what values the forest situation values assume), and that you must also specify all four forest situation parameters to identify a desired set of your float data, you can easily set this up as a single table in SQL Server.

Despite what you posted above about a table in an RDB being a two-dimensional data space, you can easily represent data with higher dimension by using a composite primary key. I recommend studying a good book on basic relational database principles; it will pay great dividends as you represent more complicated data.

Here is the DDL for something similar to what you will need under SQL Server. I did not know the types of your situation parameters, so I guessed. On any kind of machine capable of running SQL Server, with only around 1000 records in this table, you will find the speed you can access this data to be greatly faster than your requirements, and you will be able to perform all sorts of interesting analysis of your data using pretty basic SQL. Hope this helps!

CREATE TABLE dbo.Forest_data

	(
 	Soil_type int NOT NULL,
	Tree_species int NOT NULL,
	Management_type int NOT NULL,
	Precipitation_value int NOT NULL,
	Data_1 float(53) NULL,
	Data_2 float(53) NULL,
	Data_3 float(53) NULL,
	Data_4 float(53) NULL,
	Data_5 float(53) NULL,
	Data_6 float(53) NULL,
	Data_7 float(53) NULL,
	Data_8 float(53) NULL,
	Data_9 float(53) NULL,
	Data_10 float(53) NULL
	) ON [PRIMARY]

	PK_Forest_data PRIMARY KEY NONCLUSTERED 
	(
	Soil_type,
	Tree_species,
	Management_type,
	Precipitation_value
	) ON [PRIMARY]