pcplantdb

[Richard Morris <webmaster@pfaf.org>]

Hi Folks,
        The scripts I'm using at home for playing with
pcplantdb. can be found in 
        http://www.ibiblio.org/pfaf/new/php.tar.gz
This consists of a number of files of sql code, a perl
script and php scripts.

The database is self documenting, i.e. I've used a special
comment structure on the sql code and there is a table in the database
which contains all documentation info. In general scripts should be
run through
        perl documenttables.pl <  plantdbsetup.sqlr >  plantdbsetup.sql
        
The sqlscripts should be run in order specified below.
        sqlc < setuppfaf.sql # creates legacy tables for pfaf data
        sqlc < importpfaf.sql # loads the pfaf data from pfaf ascii files
                                # might need to edit to change file locations
        sqlc < setuppfafrich.sql # does some modification 
                                # on pfaf tables, various pattern matching
                                # uses udsa data to get taxonomy tree

        sqlc < plantdbsetup.sql # sets up main table for plant db
        sqlc < pcpdbLoadPfaf.sql # fills the tables in pcplantdb
        sqlc < pcpdbLoadPfaf2.sql # fills the tables in pcplantdb
        sqlc < pcpdbLoadPfaf3.sql # fills the tables in pcplantdb
        sqlc < pcpdbLoadPfaf4.sql # fills the tables in pcplantdb
        sqlc < plantdbsetup9.sql # creates indexes after all data imported

Don't know if any of it will make sense to you, if it will work
etc. The README.{html|php} files might help make some sense.

You may also need the 
        http://www.ibiblio.org/pfaf/new/rjmfamilies.txt
file to get the taxonomy tree.

And have a look at 
        http://www.ibiblio.org/pfaf/new/samplepage.html
which is sample output page illustrating footnotes.

        Good luck

                Rich

--
Plants for a Future: 7000 useful plants
Web:       http://www.pfaf.org/  :=  http://www.comp.leeds.ac.uk/pfaf/
Snail:     Blagdon Cross, Ashwater, Beaworthy, Devon, EX21 5DF
Tel:       01208 872 963 / 0845 458 4719        
Email:     webmaster@pfaf.org 
PFAF electronic mailing list http://groups.yahoo.com/group/pfaf
>From pyg@galatea.org  Thu Mar 20 12:39:10 2003
Return-Path: <pyg@galatea.org>
Delivered-To: pcplantdb@lists.ibiblio.org
Received: from galatea (unknown [216.146.252.213])
        by happyhouse.metalab.unc.edu (Postfix) with ESMTP
        id 01D2F20018; Thu, 20 Mar 2003 12:39:08 -0500 (EST)
Received: from pyg by galatea with local (Exim 3.36 #1 (Debian))
        id 18w33k-0007nz-00; Thu, 20 Mar 2003 11:38:28 -0500
From: Chad Knepp <pyg@galatea.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Message-ID: <15993.61059.609289.718817@galatea.org>
Date: Thu, 20 Mar 2003 11:38:27 -0500
To: pcplantdb@lists.ibiblio.org, permaculture@lists.ibiblio.org,
        webmaster@pfaf.org, toby@tobychampion.co.uk
X-Mailer: VM 7.07 under Emacs 20.7.2
Subject: [pcplantdb] new PC plant database
X-BeenThere: pcplantdb@lists.ibiblio.org
X-Mailman-Version: 2.1.1
Precedence: list
Reply-To: pcplantdb@lists.ibiblio.org
List-Id: <pcplantdb.lists.ibiblio.org>
List-Unsubscribe: <http://lists.ibiblio.org/mailman/listinfo/pcplantdb>,
        <mailto:pcplantdb-request@lists.ibiblio.org?subject=unsubscribe>
List-Archive: <https://lists.ibiblio.org/sympa/arc/pcplantdb>
List-Post: <mailto:pcplantdb@lists.ibiblio.org>
List-Help: <mailto:sympa@lists.ibiblio.org?subject=HELP>
List-Subscribe: <http://lists.ibiblio.org/mailman/listinfo/pcplantdb>,
        <mailto:pcplantdb-request@lists.ibiblio.org?subject=subscribe>
X-List-Received-Date: Thu, 20 Mar 2003 17:39:11 -0000

Hello all,

I would like to announce a new plant database based on data from
PFAF 2000.  It's temporarily available (with a few caveats) at:

<http://sandhillfarm.org/pcpdb/>

This is not a well tested product; expect errors and general
brokenness (incorrect data? talk to PFAF).  If it doesn't seem to be
available or goes away suddenly it could be because it 1) exceeded
[cpu/disk/bandwidth] quota 2) crashed 3) was removed because Google is
threatening a lawsuit ;-).  The above URL is temporary, but should be
up till the end of March.

Like it?  Well there's more.  I've improved the simple search engine
to respond well to phrases like "a fast growing shade tolerant vine
hardy to zone 5", yielding results like Virginia Creeper.
Unfortunately these *improvements* are far to CPU intensive to put out
in their unoptimized form and the search still generates more false
positives that I like.

Since this is both the first major python project and first major
database project I've done, the time spent so far (100-200 hours) has
been extremely fun.  Unfortunately, in my current living situation,
those around me are not excited to have me sit in front of the
computer all day hacking just for fun.  To summarize in a punny way,
FUNding has run out.

I would still like to work on this project and have a number of ideas
to create what I think would be a very valuable asset to the
permaculture community.  This post is in part to ask for leads on
foundations, grant programs, etc. that might be interested in
supporting further development of this project.

Here are some ideas about what further development might look like.  I
don't have attachment to a particular name but I will be refering to
this project as PCDB (PermaCulture DataBase) to distinguish it from
PCPDB and PFAF.

   o Collective Collaboration: The collective knowledge of a group is
     always larger than any subset of that group.  This is [partly]
     why Open Source Software (OSS), like Linux, is growing faster
     than it's proprietary counterparts.  See
     <http://catb.org/~esr/writings/cathedral-bazaar/> for some
     theory.  PCDB would PRIMARILY aquire and change its information
     by handling user inputs/edits/contributions automagically.  This
     is the essence of a wiki, but wikis [so far] are only capable of
     handling very general sorts of information.

   o Multi-media, Mutlti-subject: PCDB has no P in it.  Why limit this
     information to plants?  PCDB could easily comprise all of the
     sorts of information that make up a good PC book such as theory
     in the form of text, images, plant lists, etc... think really
     really big book.

   o Informal and Alternative Content Formats: The Internet has given
     rise to many ways of sharing information that are unique to the
     internet.  Mailing lists, web logs, wikis, discussion forums,
     etc. are some examples.  Incorporating some of these content
     formats should be quite valuable.  For example attaching a
     moderated discussion to an article or plant would allow for
     others to share their personal experiences.

   o Climatic Distinctions: Plants behave differently in different
     climates and information for a particular climate doesn't always
     apply to another.  IMHO, it would be a *good thing* to actively
     note these differences and allow users to filter information
     based on a particular locale.

   o Powerful and Easy: In order for collective collaboration to work,
     it has to be easy to use.  Good interface design is an important
     part.  This is why I think that currently PCPDB is somewhat
     better than PFAF (sorry Richard).

   o Active Content Solicitation: I think actually going out and
     collecting existing appropriate data, as opposed to waiting for
     user submission, would increase the value rapidly.  This would be
     similar to assimilating the PFAF data.

   o Free and Redistributable: This is mostly just my own personal
     stance on intellectual property.  PCDB would always be free in
     the OSS sense.  This would require that contributers agree to
     copyleft their submissions. 

Anyway, putting 500-1000 hours into this project it could be pretty
interesting.  I think it would take around 300 hours to get to a
collaborative state allowing others to grow and modify the data
itself, which as I see it, is the most important part.


Enjoy,
Chad Knepp
>From webmaster@pfaf.org  Thu Mar 20 15:45:28 2003
Return-Path: <webmaster@pfaf.org>
Delivered-To: pcplantdb@lists.ibiblio.org
Received: from mail1.srv.poptel.org.uk (mail1.srv.poptel.org.uk [213.55.4.13])
        by happyhouse.metalab.unc.edu (Postfix) with SMTP id 73C7E2002D
        for <pcplantdb@lists.ibiblio.org>;
        Thu, 20 Mar 2003 15:45:27 -0500 (EST)
Received: (qmail 13419 invoked by uid 0); 20 Mar 2003 20:38:20 -0000
Received: from host212-140-112-195.webport.bt.net (HELO pfaf.org)
        (212.140.112.195)
        by mail1.srv.poptel.org.uk with SMTP; 20 Mar 2003 20:38:20 -0000
Message-ID: <3E7A2846.4050601@pfaf.org>
Date: Thu, 20 Mar 2003 20:44:54 +0000
From: Richard Morris <webmaster@pfaf.org>
Organization: Plants For A Future
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; rv:1.3b) Gecko/20030210
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Chad Knepp <pyg@galatea.org>
References: <15993.61059.609289.718817@galatea.org>
In-Reply-To: <15993.61059.609289.718817@galatea.org>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
cc: pfaf_developers <pfaf_developers@yahoogroups.com>
cc: pcplantdb@lists.ibiblio.org
cc: Ken Fern <ken.fern@lineone.net>
cc: Frank Schuurmans <chaewen@yahoo.com>
Subject: [pcplantdb] Re: new PC plant database
X-BeenThere: pcplantdb@lists.ibiblio.org
X-Mailman-Version: 2.1.1
Precedence: list
Reply-To: pcplantdb@lists.ibiblio.org
List-Id: <pcplantdb.lists.ibiblio.org>
List-Unsubscribe: <http://lists.ibiblio.org/mailman/listinfo/pcplantdb>,
        <mailto:pcplantdb-request@lists.ibiblio.org?subject=unsubscribe>
List-Archive: <https://lists.ibiblio.org/sympa/arc/pcplantdb>
List-Post: <mailto:pcplantdb@lists.ibiblio.org>
List-Help: <mailto:sympa@lists.ibiblio.org?subject=HELP>
List-Subscribe: <http://lists.ibiblio.org/mailman/listinfo/pcplantdb>,
        <mailto:pcplantdb-request@lists.ibiblio.org?subject=subscribe>
X-List-Received-Date: Thu, 20 Mar 2003 20:45:28 -0000

Chad Knepp wrote:

Nice one.

I do like the way the advance search has been laid out.
Very clear.

Is source code available?

> I would like to announce a new plant database based on data from
> PFAF 2000.  It's temporarily available (with a few caveats) at:
> 
> <http://sandhillfarm.org/pcpdb/>
> 
> This is not a well tested product; expect errors and general
> brokenness (incorrect data? talk to PFAF). 

All we need now is a method to edit the data and the community
can fix the incorect data!

 > If it doesn't seem to be
> available or goes away suddenly it could be because it 1) exceeded
> [cpu/disk/bandwidth] quota 2) crashed 3) was removed because Google is
> threatening a lawsuit ;-).  The above URL is temporary, but should be
> up till the end of March.
> 
> Like it?  Well there's more.  I've improved the simple search engine
> to respond well to phrases like "a fast growing shade tolerant vine
> hardy to zone 5", yielding results like Virginia Creeper.
> Unfortunately these *improvements* are far to CPU intensive to put out
> in their unoptimized form and the search still generates more false
> positives that I like.

I've been playing with an interesting search metric reciently
given two strings A and B count the number of sub string in
A which match sub strings in B. This seems to be working
remakably well. If the two string are identicle then a large
number of sub strings will match.

It has the property of catching miss spelled words very easily
which is especially important for latin names which often
have different suffixes  i.e. end in 'um' or 'us' or 'a'.

Its also good for common names which often have different
punctuation i.e. using a space or a hyphen between two words.
'St. John's wort' can also be wrtten as
'Common St Johnswort', or 'St John-wort'
(this works substring 'John' and 'wort' both match).

Only problem is its pretty slow testing two strings have
order O(len(A)*len(B)).

> Since this is both the first major python project and first major
> database project I've done, the time spent so far (100-200 hours) has
> been extremely fun.  Unfortunately, in my current living situation,
> those around me are not excited to have me sit in front of the
> computer all day hacking just for fun.  To summarize in a punny way,
> FUNding has run out.

Tell me about it! I dread to think of man hours gone into
pfaf database by me.

> I would still like to work on this project and have a number of ideas
> to create what I think would be a very valuable asset to the
> permaculture community.  This post is in part to ask for leads on
> foundations, grant programs, etc. that might be interested in
> supporting further development of this project.

We are thinking of applying for a grant for DB development.

> Here are some ideas about what further development might look like.  I
> don't have attachment to a particular name but I will be refering to
> this project as PCDB (PermaCulture DataBase) to distinguish it from
> PCPDB and PFAF.
> 
>    o Collective Collaboration: The collective knowledge of a group is
>      always larger than any subset of that group.  This is [partly]
>      why Open Source Software (OSS), like Linux, is growing faster
>      than it's proprietary counterparts.  See
>      <http://catb.org/~esr/writings/cathedral-bazaar/> for some
>      theory.  PCDB would PRIMARILY aquire and change its information
>      by handling user inputs/edits/contributions automagically.  This
>      is the essence of a wiki, but wikis [so far] are only capable of
>      handling very general sorts of information.
> 
>    o Multi-media, Mutlti-subject: PCDB has no P in it.  Why limit this
>      information to plants?  PCDB could easily comprise all of the
>      sorts of information that make up a good PC book such as theory
>      in the form of text, images, plant lists, etc... think really
>      really big book.
> 
>    o Informal and Alternative Content Formats: The Internet has given
>      rise to many ways of sharing information that are unique to the
>      internet.  Mailing lists, web logs, wikis, discussion forums,
>      etc. are some examples.  Incorporating some of these content
>      formats should be quite valuable.  For example attaching a
>      moderated discussion to an article or plant would allow for
>      others to share their personal experiences.
> 
>    o Climatic Distinctions: Plants behave differently in different
>      climates and information for a particular climate doesn't always
>      apply to another.  IMHO, it would be a *good thing* to actively
>      note these differences and allow users to filter information
>      based on a particular locale.
> 
>    o Powerful and Easy: In order for collective collaboration to work,
>      it has to be easy to use.  Good interface design is an important
>      part.  This is why I think that currently PCPDB is somewhat
>      better than PFAF (sorry Richard).

Yes I agree the pfaf website db interface has got a little bloated.
But it was done well before google came about.
Its all due for a clean up but time is limited.

>    o Active Content Solicitation: I think actually going out and
>      collecting existing appropriate data, as opposed to waiting for
>      user submission, would increase the value rapidly.  This would be
>      similar to assimilating the PFAF data.

Vital. The projects only going to work if people know about
it.

Design wise, I think it should be easy to incorperate tables
from other sources easily.

>    o Free and Redistributable: This is mostly just my own personal
>      stance on intellectual property.  PCDB would always be free in
>      the OSS sense.  This would require that contributers agree to
>      copyleft their submissions. 

Just one point our license has now changed to the Creatibve Commons
(Atribution Non-commercial, sharealike) liscence. And require the
following to appear:

This work is licensed under a Creative Commons License.
        http://creativecommons.org/licenses/by-nc-sa/1.0
(You can copy, distribute, display this works but: Attribution is 
required, its for Non-Commercial purposes, and it's Share Alike 
(GNUish/copyleft) i.e. has an identical license.)
We also ask that you let us know (webmaster@pfaf.org) if you link to, 
redistribute, make a derived work or do anything groovy with this 
information.

> Anyway, putting 500-1000 hours into this project it could be pretty
> interesting.  I think it would take around 300 hours to get to a
> collaborative state allowing others to grow and modify the data
> itself, which as I see it, is the most important part.

Minor bugs:

Doing and advanced search on checking for a word in the
culture tab gave the results
http://sandhillfarm.org/cgi-bin/pcpdb.py?advanced_search=yes&output=Tabled&hit_limit=10&family=&genus=&species=&ssp=&common_name=&cultivar=&zone=&habit=&foliage=&tall=&short=&wide=&narrow=&rating=&cultivation_details=hill&propagation_details=&uses=&known_hazards=

Latin names were not clickable. Clicking on family name gave a cgi
error and clicking on describe gave a blank page.

Also got
File Not Found: /pcpdb_glossary.html

--
Plants for a Future: 7000 useful plants
Web:   http://www.pfaf.org/ same as http://www.comp.leeds.ac.uk/pfaf/
Post:  1 Lerryn View, Lerryn, Lostwithiel, Cornwall, PL22 0QJ
Tel:     01208 872 963 / 0845 458 4719  
Email: webmaster@pfaf.org
PFAF electronic mailing list http://groups.yahoo.com/group/pfaf