View source for RedLUG:WetlandDatabase

-- Main:[[JamesAlspach]] - 27 Jun 2005
Ok, so here goes.  My first wiki anything :-)

cc from my [[DiscussionBoard|[[DissussionBoard]] post]] for setup:

A few meetings ago Steve Moffett asked for some suggestions as to setting up a database of wetland inhabitants (or something similar). I went home and thought about it and sent him my ideas on a database layout. I do not know if this still is (or ever was) an actual project for him but, I was curious if anyone has any thoughts on my suggestions or other ideas that may help him if it is still a need.

=<code> Wetlands Database Ideas </code>=

I suggested to Steve that he break the tables along the existing biology [[http://library.thinkquest.org/11771/english/hi/biology/taxonomy.shtml|taxonomy]].  This is probably one of the hardest parts.  I have seen looking around that the groups are not as clean as the above link would have you believe.  There are subgroups that not all species will have.  I had to ignore those as they do not fit into this scheme.  I do not know how to have genus linked to family for one species and genus linked to a sub group which is linked to family, for another and still keep things related.

I suggested a starting table layout something like this:

* a table giving each kingdom a unique number and description.
* one table each to relate each of the groups to the one above it.
** phylum/division to kingdom 
** class to phylum
** order to class
** family to order
** genus to family
** species to genus
*** Each of these tables has a unique id, a col each for the child and the parent group, and a description of the child group.
            * 1001, chordates, Animalia, "animals with notochords"  

These last three will mostly be important on the species group since you will not have a specific instance that covers other groups.  However, it is possible to have them on other groups, I just can not see why you would need to.
* one each (as needed) to relate a group to a photo
* one each (as needed) to relate a group to a common name
* one each (as needed) to relate a group to any other info (a tagged example of the group i.e. a bird that was banded, may have its own table(s) somewhere but the band number would be stored here to link it to a specific species.)

If I am not too messed up in this layout, it allows you to:
* enter information as efficiently as possible
** As you enter more data the 'top end' fills in fast so that going forward, more and more often, the higher groups are already entered.
* add new descriptive info as necessary to any level just by adding tables to the periphery.
* use a tool such as [[http://grass.itc.it/|GRASS]] to visualize the data. 
* create web pages that can do full searches, display images, etc...

That is about it.  Some of my thoughts on this wetlands database and my trial of wiki stuff.  I must say, this was fairly painless.  Perhaps I will toss my [[http://www.asteriskpbx.org|Asterisk]] setup howto up here when I get it moved beyond my scribblings.

==<code> Further Thoughts </code>==
Ok, here goes with a more defined schema.  It is pretty much what I have already mentioned only in a better format.
Here are some notes realted to the further thoughts  They are in no particular order.
* I used [[http://www.fabforce.net/|DBDesigner4]] to create all of this info.  If anyone wants to d/l it, I have linked my saved file for your viewing pleasure.  I have also made an image of the schema and an SQL create file available.  
	* [[%ATTACHURL%/wetlandsdb.sql|wetlandsdb.sql]]: Create Script
	* [[%ATTACHURL%/wetlandsDB.xml|wetlandsDB.xml]]: My DBDesigner file
* !DBDesigner is focused on !MySQL which is fine. However, in this case, I would look more to !PostgreSQL since it supports views and geometric data types. 
** A view would be a handy way to set up a full join of these tables right off the bat.  This would make data entry and searches less painfull.
** The geometric data types would be a cool way of storing a location of interest.  In theory, once the data is populated, you could do a query asking for all trails that come within 100 yards of a specific point (say a beaver dam or a birds nest).
* I know that one could store the image in the db but I have never had a reason to go that route.  Going this way, you leave the raw images available for other uses.
* The lengths on the schema I posted are arbatrary.  The person building the db will know more than I do about what is needed here.
* The image and common tables attached to species could be attached to any of the groups.  They list the images and any common names used to describe an entry.

Let me know what you think.

Schema Image: <br /><img src<code>"%ATTACHURLPATH%/wetlandsdb.png" alt</code>"Schema Image"  />

%COMMENT%
	* I just came across these and thought I would post them.  First is the [[http://postgis.refractions.net/documentation/|PostGIS]] website.  This is software that helps !PostgreSQL deal with geospatial data. Second (working in concert with this) is the [[http://www.google.com/apis/maps/|[[GoogleMaps]] API]]  If you have not played with [[http://maps.google.com/maps?q<code>technology+schools+near+anderson,ca&hl</code>en|GoogleMaps]] You really should.  Now immagine your data layered over these maps / sat images.  What a great way to view the data. -- Main:[[JamesAlspach]] - 15 Jul 2005 %TIME%
	* I just found this 'Future releases of !PostgreSQL are likely to use a separate OID counter for each table, so that tabloid must be included to arrive at a globally unique identifier.' It would probably be prudent to use either the above suggestion or use an id + timestamp or id + time (timestamp would make the id all numeric whereas time is much more human readable).  You just have to make sure that your id's do not update (or if they do they cascade down the chain) if you make an edit.  
     * None of this will matter if we do not require a unique id for each entry across the entire db.  I can not see where a globaly unique id would be necessary but, I would rather plan for it now that wish we had down the road. -- Main:[[JamesAlspach]] - 10 Jul 2005 %TIME%
	* I was able to link some schema info to this page.  I edited the page to place it above...ps whats up with the time stamps on the comments?  The date is correct but the time seems to be the same for all entries on the page.-- Main:[[JamesAlspach]] - 04 Jul 2005 %TIME%
	* I will put some more thought into this and try to get something firmer posted Sunday evening or so, for people to chew on. -- Main:[[JamesAlspach]] - 02 Jul 2005 %TIME%
	* Hi, do you have an (even rough) db schema set out that you could post for some of us to view and see if we have any suggestions or note any issues? -- Main:[[TimGreer]] - 02 Jul 2005 %TIME%
	* I am glad it helped.  I have thought about this project off and on and wondered how it may be going.
* I was also wondering what other people thought of the layout.  I still can not see any big holes but perhaps someone else will.
* I will keep an eye out here for follow up and thoughts.  I will also post addl thoughts as they come to me. -- Main:[[JamesAlspach]] - 29 Jun 2005 %TIME%
	* James, I was just browsing the twiki site and found your summary here.  It's good to see your ideas on screen -- helps me to be able to see it organized this way.  I sent a link to this page to the teacher here that is responsible for the wetlands project so he can review your ideas.  Thanks.  -- Steve Moffett -- Main:[[SteveMoffett]] - 28 Jun 2005 %TIME%