Hi I am a civil engineer with some experience of programming but I am not familiar with the vast range of options available today. Hope you could give me any pointers the best way to proceed.
I want to make and query a database of ground level survey measurements in a grid format. There will be many measurements for each grid location at various times through the earthmoving job so there is a 4th dimension of time.
The observations will most likely be read in from a text file. In each record there will be an (2 x integer) grid position (row and column) a (floating point) ground level and various string information codes (maybe up to 30 characters in total).
The grids could be about 10000 rows x 10000 columns. Not every location on the grid would be have a record in each survey, but they would typically have up to a hundred records. A lot of the grid locations would have no records at all (the site will not be perfectly rectangular).
I want to search the records, extract data and do calculations eg calculate the lowest or highest ground level for each grid location. I am fairly confident I would have the ability to program this fairly simply in a language like FORTRAN, BASIC or C using arrays. A lot of the array elements would be empty though and I am guessing that this is not the right way to do it though and big databases like this need special tools that I will have to learn how to use.
I am thinking of possible options for the platform -
1) Use a database program. I am not familiar with how powerful these can be but I imagine they would have a lot of overhead with GUI.
2) Use SQL? This I don't know much about but it seems to be the language for databases. I have always used imperative languages rather than declarative and as I understand from wikipedia that SQL is declarative I am a bit nervous of the change. I don't fully understand the process for using it. Is there a compiler that makes console programs? Is the database stored on disk? Sorry for such stupid questions.
3) Use an API like c-treeACE? I think this may be the way to go offering me the familiarity of a "do this, then do that" language (unfortunately this is the way I think as an engineer!). But I am hoping that the behind the scenes memory and processing management offered by the API would be superior to what I could achieve with huge arrays.
4) Or could i do it with an object orientated language and let the computer worry about the storage requirements. eg if i stored the records as objects with methods and properties that would help me get the results I need out of each record - would it be a huge bloated program compared to 3)
There are likely to be hundreds of millions of records and I want to be able to query and process them in minutes not hours (preferably seconds!) on a modern PC running windows. To be more specific mine is an i7 processor with 6Gb ram and 120Gb SSD running Windows 7 64 bit.
Hope someone has time to share a couple of words of wisdom with a newbie. Thanks Roger