Please consider renaming 'geography' so it will play well with PostGIS

[thay...@yahoo.com]

Mario,

Hopefully it will be helpful to point this out. The Ghini webpage states as one of your goals for Ghini is to add spatial capability. The spatial extension to PostgreSQL is called PostGIS. It uses an object called 'geography' (for data that are spread out far enough that the curvature of the earth plays a major role). So currently it is not possible to add PotGIS to a PostgreSQL Ghini database. I believe that 'geometry' is also part of the Open Geospatial Consortium's Simple Features SQL standard.

I was able to make my Ghini database spatial by using pg_dump to backup an existing Ghini database and then using search and replace to rename all objects using 'geography' to 'geographyt'. I was then able to import that backup into a freshly created database with PostGIS installed, and able to open that database in Ghini. For now I plan to use QGIS to help manage the spatial aspects, and to use Ghini for everything else.

Thanks for all your great work on Ghini! 

-Thayer

[Mario Frasca]

Hi Thayer

thank you for your feedback and for being among ghini users!

we have met your issue already, and solved it in ghini.desktop 3.1

https://github.com/Ghini/ghini.desktop/issues/326

bauble/ghini "geography" contains geographic areas, and that is the new
name of the table and class: geographic_area / GeographicArea.

ghini-3.1 isn't really released yet, but already successfully installed
at a couple locations.  we miss an installation script, and I didn't yet
manage to sit down and get travis-ci to correctly run our test suite. 
I'm quite sure the two things are closely related.

if you want to step to 3.1, please do, and let me know, I'll be quite
happy to get some geography-related feedback!  as of now, ghini uses
notes to hold plant coordinates.  it does show them in the infobox, for
plants, accessions, locations (you see a dot at the plant coordinates,
in a tiny non-dynamic OSM map, zoom 18 I think).  to put the coordinates
in the sqlalchemy model, we would need digging into the gis extensions. 
or we could mark the note of type geography and use WKT for storing it. 
also possible, more flexible, and does not require any extra change in
the data model.  either way: not much time for that, and no users asking
for it yet.

ciao,

M

[Thayer Young]

Thanks Mario, I missed that. I only searched the Google group, so I did not do much 'due diligence' before posting. I have been thinking about how best to do spatial and 'WKT' in the location_notes was on that list. 

Thanks for drawing my attention to the Ghini 3_dev improvements. If I get the time to troubleshoot compiling from source I may look into it further, but for now I am more worried about getting a working script for installing on MacOS. The two times I have tried Homebrew it has not worked. Do you have any advice on that? 

Again I have not tried too hard to get the MacOS installation working, pushing and pulling plant lists and accession data into the normal form has occupied most of my efforts so far with Ghini. But it has been worth the effort, the program is very elegant once the data are in, assuming of course I can write enough saved queries so I do not have to teach people how to do that.

-Thayer

M



--
You received this message because you are subscribed to a topic in the Google Groups "ghini/bauble" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/bauble/1-Cx3nr1w6Y/unsubscribe.
To unsubscribe from this group and all its topics, send an email to bauble+unsub...@googlegroups.com.
To post to this group, send email to bau...@googlegroups.com.
Visit this group at https://groups.google.com/group/bauble.
For more options, visit https://groups.google.com/d/optout.

[Mario Frasca]

good day Thayer,

let me understand what hardware you are using, and on what operating systems you have your ghini clients.  and by the way, are you writing from private premises, or from a botanic garden?

installing on the latest OSX version, I did that several times.  it's not hard, but you need a complete installation of the development tools, or you can better give up.  every time I run the installation, I review the instructions, and last time I did not need any correction. 

but that's for ghini-1.0

I just pushed a revised ghini-3.1 devinstall script. 

https://raw.githubusercontent.com/Ghini/ghini.desktop/ghini-3.1-dev/scripts/devinstall.sh

I do not have access to macintosh machines, I've got an old raspberrypi which is now running the 3.1 installation script, and it's going smooth up to now.  but I'm behind a 15kb/s network, so it's taking its time (repository grew to more than 40Mbytes).

give me more literal details, what troubles you encounter on your machines, I might be able to give hints.  the whole precise sequence of what you did, and the whole precise sequence of what happened.

just let me know, and we'll make sure you can run the software on the machines you chose to use.  (¡¡within reason!!)

Mario

p.s.:

if you're writing from a botanic garden, and you are _almost_ satisfied with the software, you might be interested in financing some features you miss in ghini.  think of it, you can call any time.  there's quite a list of issues on https://github.com/Ghini/ghini.desktop/issues, and you are very welcome adding your own, regardless whether you're financing the project, or not.

also, I can offer some visibility to your collection by adding your plants to the http://web.ghini.me/ site.  this I can offer free of charge.  feedback on site capabilities very welcome!  about the tiles: only one garden (Cuchubo) has nice tiles for deeper zoom levels, others just use magnified level 19 tiles, or have nothing at all.  producing pretty tiles requires quite some work, so we only have it for the pilot garden.  I have only automated the production of magnified level 19 tiles, the result is only slightly better than nothing, and it weighs heavily on space consumption on my server. 

again, best regards, MF

--
You received this message because you are subscribed to the Google Groups "ghini/bauble" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bauble+un...@googlegroups.com.

[Thayer Young]

Mario,

Thank you for your help with this. I will get back to you on the MacOS install. 

I am a grant funded contractor for a native plant arboretum. Right now I am pretty much done transitioning data from an old MS Access database and shapefiles into PostgeSQL/PostGIS and plan to manage that with Ghini. Eventually that database will be moved onto a web host and it will provide edit access for members of the Arboretum staff and contractors, as well as provide data to a public interactive web mapping application with search capability.

I need to be able to install Ghini on Windows 10, Windows 7. I am not sure what versions of Mac OS I need. I am using Ubuntu 14 and Win 7 right now for developing the database. I will ask later this week about that. 

I will get back to you about any potential for funding.

One thing you can answer for me right now though, is if you agree with my thinking on how to handle a living collections database to catalogue existing wild native and nonnative plants. There are some plantings and propagations but the vast majority is plants that grew in situ without the intervention of people. 

I have created a single accession per species per year according to when they were surveyed in the field, not planted. I then assigned the provenance as being 'wild source', and the type of material as 'Unknown', since I do not know how the plant arose. The wild native/nonnative provenance is based on the 'note' from species_note.category = 'native'. In the plant table the plant material is Other. 

The current database of 'specimens' has about 900 plants with GPS points for each. The map of the arboretum is divided into polygons for 49 plant communities. I am using the communities for the 'location' table, with the 'geometry' field being held in a separate spatial table. I have done a spatial join to assign specimens to plant communities, this is the main reason for needing PostGIS.  

My questions are, whether or not it is whether or not it is appropriate to use accession & plant like this or do I need a separate track for specimens if I want to describe existing wild grown plants the arose in situ? Is it possible to add a 'type of material' that describes plants that grew up wild in the spot in which they are being catalogued?

-Thayer

To unsubscribe from this group and all its topics, send an email to bauble+un...@googlegroups.com.

[Mario Frasca]

Hi Thayer

On 08/06/2018 02:10 PM, 'Thayer Young' via ghini/bauble wrote:

I need to be able to install Ghini on Windows 10, Windows 7

for this, you will probably need a customized 1.0 version (with your own edit, where geography is renamed to geographyt).  do you manage to do it there, running `pip install` locally?  otherwise I can have AppVeyor produce the `setup.exe` for you, just fork the project, put your modifications there, and open an issue.  I will then see how we do that.  possibly a branch, and register it in appveyor, I'm not sure, but it should not be complicated.

14.04?  sounds like a rather old ubuntu version.  any specific reason?

do I understand correctly, you are inserting plants with coordinates but lacking the reference to the location, and letting postgis geographic engine calculate the id of the location.  sounds fine, but I'm afraid that ghini will not help you do this from the graphical interface.

the point you make about wild/non wild and the wild status, that has to do with parts of the ITF2 standard, and it's not correctly implemented in bauble/ghini.  I'm quite sure we have issues about it…  https://github.com/Ghini/ghini.desktop/issues/36 …, but what you describe sounds fine to me.

about GPS positions, you say you're using QGIS.  have you noticed my (still experimental) plugin, https://plugins.qgis.org/plugins/DistanceMatrixToCoords/ ?  it is meant as a help to correct pure GPS coordinates with mutual distances.  do you only collect GPS coordinates, or you also place the points in a Cartesian grid, or measure mutual distances?

finally to your questions, I would probably be more specific with accessions, and split them a bit further than you do now.  my hint would be not to allow accessions cross population borders.  but if I understand correctly your situation, you are sitting at a desk, and only seeing information from your old database, not observing plants directly, then I'd say you are doing the most reasonable thing.

it is possible to add a new type of material, yes… https://github.com/Ghini/ghini.desktop/issues/9

too bad I did not put this issue into ghini-3.1.  so: it's possible, but we need to alter the program, and I'd rather change it only once, adding the described flexibility.  it's not a big issue, but someone has to program it.

Eventually that database will be moved onto a web host and it will provide edit access for members of the Arboretum staff and contractors, as well as provide data to a public interactive web mapping application with search capability.

where you run your database server with relation to the web server, that's not such a big deal.  you can easily access the same ghini.desktop database from a web server, servicing either something like ghini.web (http://web.ghini.me/) or something like one of the several past attempts we made at a web based interface to the bauble/ghini desktop database.  one of the ghini.web prototypes even had postgresql triggers, so you change something via desktop, and changes get pushed to connected web clients.

if you and your contracting garden think so, we could realize a new nodejs server offering limited, simplified database insertion interface.  something we can think of, any time.

but let me ask please, have you seen and considered ghini.pocket?

it's not yet perfect, but it allows you collect data in the field, and leave the import task to the database manager, or outsource the task.  in my experience, databases get quickly outdated because data insertion tools require botanists to stare at a computer screen.)

then your access to data would be in three versions: desktop (direct r/w), web (direct r/o), pocket (batch r/w) 

(oh, please make sure that nobody in garden even thinks of asking me to develop ghini.pocket for iPhone. all of it is far too expensive.)

best regard,

Mario

[Mario Frasca]

Hi Thayer

On 08/06/2018 02:10 PM, 'Thayer Young' via ghini/bauble wrote:

I need to be able to install Ghini on Windows 10, Windows 7

for this, you will probably need a customized 1.0 version (with your own edit, where geography is renamed to geographyt).  do you manage to do it there, running `pip install` locally?  otherwise I can have AppVeyor produce the `setup.exe` for you, just fork the project, put your modifications there, and open an issue.  I will then see how we do that.  possibly a branch, and register it in appveyor, I'm not sure, but it should not be complicated.

14.04?  sounds like a rather old ubuntu version.  any specific reason?

do I understand correctly, you are inserting plants with coordinates but lacking the reference to the location, and letting postgis geographic engine calculate the id of the location.  sounds fine, but I'm afraid that ghini will not help you do this from the graphical interface.

the point you make about wild/non wild and the wild status, that has to do with parts of the ITF2 standard, and it's not correctly implemented in bauble/ghini.  I'm quite sure we have issues about it…  https://github.com/Ghini/ghini.desktop/issues/36 …, but what you describe sounds fine to me.

about GPS positions, you say you're using QGIS.  have you noticed my (still experimental) plugin, https://plugins.qgis.org/plugins/DistanceMatrixToCoords/ ?  it is meant as a help to correct pure GPS coordinates with mutual distances.  do you only collect GPS coordinates, or you also place the points in a Cartesian grid, or measure mutual distances?

finally to your questions, I would probably be more specific with accessions, and split them a bit further than you do now.  my hint would be not to allow accessions cross population borders.  but if I understand correctly your situation, you are sitting at a desk, and only seeing information from your old database, not observing plants directly, then I'd say you are doing the most reasonable thing.

it is possible to add a new type of material, yes… https://github.com/Ghini/ghini.desktop/issues/9

too bad I did not put this issue into ghini-3.1.  so: it's possible, but we need to alter the program, and I'd rather change it only once, adding the described flexibility.  it's not a big issue, but someone has to program it.

Eventually that database will be moved onto a web host and it will provide edit access for members of the Arboretum staff and contractors, as well as provide data to a public interactive web mapping application with search capability.

where you run your database server with relation to the web server, that's not such a big deal.  you can easily access the same ghini.desktop database from a web server, servicing either something like ghini.web (http://web.ghini.me/) or something like one of the several past attempts we made at a web based interface to the bauble/ghini desktop database.  one of the ghini.web prototypes even had postgresql triggers, so you change something via desktop, and changes get pushed to connected web clients.

[Thayer Young]

Currently the spatial data about 'plant' positions are stored in a PostGIS point geometry column, the communities are multipolygons also in a geometry column. In both cases the spatial tables have been added to the Ghini schema and reference the ids for the Ghini 'plant' (points) or the 'location' (multipolygons) table. Both are in a projected coordinate reference system. The area of interest for the database is about 130 hectares. About 2/3 of that is closed canopy forest. So for the majority of plants the QGIS plugin would not be helpful as the distances to good reception are too far.

I saw Ghini Pocket on the website but have not tried it yet. I have been evaluating Open Data Kit and Geo Paparazzi for field data collection, neither is a perfect fit to our needs. The mobile app needs to work offline, and data flow from the mobile app to the database needs to be automatic. I will only be able to do limited work to make sure that things are running smoothly after the grant period is over, they do not have an IT person to hand it off to. Ideally the mobile app needs to have a map showing GPS location and existing accession.plant records.

My thinking is to use Android cell phones for field data collection, as that seems to be where the open source data collection software concentrates. And if one goes missing or breaks it is not a big loss.

-Thayer

 

[Mario Frasca]

Hi Thayer,

answering/commenting/asking in-line …

On 08/06/2018 05:43 PM, 'Thayer Young' via ghini/bauble wrote:

Currently the spatial data about 'plant' positions are stored in a PostGIS point geometry column, the communities are multipolygons also in a geometry column.

👍 (+1)

In both cases the spatial tables have been added to the Ghini schema and reference the ids for the Ghini 'plant' (points) or the 'location' (multipolygons) table.

let me get this straight. you did not add an extra column to the existing plant and location tables.  why not?  the two extra columns, not used by sqlalchemy, should go unnoticed (default=None, Nullable=True).

Both are in a projected coordinate reference system.

👍 (+1)

The area of interest for the database is about 130 hectares. About 2/3 of that is closed canopy forest.

ACK

So for the majority of plants the QGIS plugin would not be helpful as the distances to good reception are too far.

well, that was the whole idea that motivated me to writing this plugin: to allow us use GPS under a thick canopy.  the idea is not to reference every single point to three or more good GPS reception points (which I am very well aware they would be inaccessible from the depths of a thick forest), but to construct a dense network of mutual distances of points.  each point in the network/grid/frame may be associated to an error affected GPS reading, or to an inaccessible but easily recognizable spots.  ideally, you would use a laser beam meter to measure such distances: measuring distances with a measuring tape in a forest comes close to mission impossible.

(an addition would be marking some of the GPS measured points as "good reception", and some as "under thick canopy".  I estimate it as not difficult, but your task, to provide the data, becomes a bit more complicated.  we can talk about it, I'd say.)

I saw Ghini Pocket on the website but have not tried it yet. I have been evaluating Open Data Kit and Geo Paparazzi for field data collection, neither is a perfect fit to our needs.

we evaluated ODK and I have heard of Geopaparazzi.  in fact, I added the data collecting option to ghini.pocket after some testing with ODK collect / aggregate at the JBQ (Jardín Botánico de Quito).  ODK performed decently, but there was little point in such a heavy interaction since it was possible to do all from one app.  moreover, we had some perplexity about remote data handling, and security, it all started to sound a bit too complex for the task.  the single app solution is also a lot faster, in particular the inventory review.

The mobile app needs to work offline,

👍 (+1) (definitely)

and data flow from the mobile app to the database needs to be automatic.

this isn't yet there, not completely at least, and the script implementing the pocket-to-desktop data flow is quite unmaintained.  not being part of the program, it is also not unit-tested.  but it is on the to-do list, and it is interesting that a garden needs this.  let me know about the time-span of your grant, and whether the garden wants to invest in external development.

I will only be able to do limited work to make sure that things are running smoothly after the grant period is over, they do not have an IT person to hand it off to.

ACK

Ideally the mobile app needs to have a map showing GPS location and existing accession.plant records.

this would be a valuable addition to ghini.pocket.  the JBQ did not need this, because most of their inventoried plants are in glasshouses or showrooms.  not such a big deal programming it, not much more than copying part of ghini.tour into ghini.pocket.  but 130ha is a large area and cached tiles would occupy quite some space.  and are you sure your garden does not have faaaaaaaar toooooooooo many plants and the map would become invisible below the plant markers?  unless plants appear or disappear depending on the zoom at which you are viewing the map, like it is in ghini.web (only used at our pilot garden, visible between zoom 22 and 21).

My thinking is to use Android cell phones for field data collection, as that seems to be where the open source data collection software concentrates. And if one goes missing or breaks it is not a big loss.

☺

ciao, reading you tomorrow,
Mario

[Thayer Young]

Mario,

With respect to why I did not add the geometry column to the Ghini table itself. I did not want to mess with the Ghini tables, in case that would lead to problems in the future, I figured that the least likely thing to change would be the table id.

You are correct that potentially there will be way too many points to have a meaningful display, as for example at Harvard University's Arnold Arboretum, or Missouri Botanical Garden. There is little risk of that here, the resources are not there to survey anywhere near all of the trees. The current goal is to get representative species in each of the plant communities. 

The laser measuring tool is a good idea, and we did think about it but did not go that way for some reason I can not remember. I did see your plugin by the way. I am not sure that the arboretum has the people resources or time to establish a matrix like you have described. We do have the advantage of 15 cm resolution leaf off color/color IR imagery covering the site, so maybe developing the matrix is not as hard as I think.

-Thayer

[Mario Frasca]

Hi Thayer,

On 08/07/2018 06:47 AM, 'Thayer Young' via ghini/bauble wrote:

Mario,

With respect to why I did not add the geometry column to the Ghini table itself. I did not want to mess with the Ghini tables, in case that would lead to problems in the future, I figured that the least likely thing to change would be the table id.

it's not likely to change, no, but notice how it is not included in the JSON export.

I consider the ID of any object as an internal matter.

anyhow: the policy within bauble and even more in ghini is to postpone all changes that require changes in database structure to the latest possible moment.  once we release a x.y version, you can stay on that line, updating to any newer x.y.z, without any need to migrate your data.

migrating to a newer line (like from 1.0 to 3.1) requires using your old installation to produce a backup in csv format, have it processed by a script, and restore it in your new installation.

in this process all id's are conserved, so your choice is reasonable as far as we only consider backups.  on the other hand, if you want your coordinates included in a JSON export, we need enhance the code, or you might do the following:

have your coordinates visible in ghini, write a stored procedure in your database server and associate it to a trigger to your geographic tables.  for example, whenever you alter the geographic field of a plant, your stored procedure could: () find the corresponding plant object; () find the corresponding <coords> `plant_note` record; () if not found, create it; () store in the `note` field of the `plant_note` record the WKT representation of your geographic coordinates, or maybe use the currently supported 'lat:%(lat)f, lon:%(lon)f' format.

a similar procedure could store the multipolygon definition into a location_note object.

[…] We do have the advantage of 15 cm resolution leaf off color/color IR imagery covering the site, so maybe developing the matrix is not as hard as I think.

possibly, if no other option is at hand.  do consider that photos of the top of the canopy don't reveal much of the situation on the ground.

best,

Mario

[Thayer Young]

OK, that all makes sense. Thanks for these suggestions. I will probably make some changes to the schema now that you have explained the future proofing. The triggers of coarse are a good idea, and including it in a <coords> note would also help. I am just not very sure that volunteers are going to get that level of database detail. Unfortunately the majority of the database input is probably going to be done by volunteers. We'll see how that goes. Hopefully I can leave good enough training materials so that things stay reasonable.

-Thayer

--

[Mario Frasca]

have a look at http://ghini.readthedocs.io/en/ghini-1.0-dev/use_cases.html

On 08/07/2018 10:15 AM, 'Thayer Young' via ghini/bauble wrote:
> Hopefully I can leave good enough training materials so that things
> stay reasonable.

and, oh please, if you're producing ghini-related documentation.
consider contributing it to the project, and have it included in the
above web site.

we can discuss the form, you can have a separate page, there's nothing
we can't adapt.  at the moment it's just two use cases.  it's a
relatively new page, less than 2 years, and previous training material
has not reached me when I started working at the project in 2014 nor
when I later adopted it.

anyhow, you get peer review, and the user community gets hints.

cheers,

Mario

[Thayer Young]

Mario,

I will definitely contribute whatever I come up with with respect to documentation.

I have seen the docs and recipes, and they have been helpful, but I think I will need to expand on them.

 

I intend to put together a more step by step procedure with screen shots. And to expand the section explaining queries to make it less abstract. I would also like to produce a glossary of the field names, it took me quite some time to decode them.

I don't know if Ghini 3 allows for descriptive aliases for the field names? With Ghini 1 with Postgres I get the raw field names in the query builder. Aliases would help, but of course require coding the join and creating a field name alias table.

Sorry again, if I have missed things in the docs.

-Thayer

Mario

--
You received this message because you are subscribed to a topic in the Google Groups "ghini/bauble" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/bauble/1-Cx3nr1w6Y/unsubscribe.

To unsubscribe from this group and all its topics, send an email to bauble+unsub...@googlegroups.com.

[Mario Frasca]

Hi Thayer,

thank you for your availability to contributing documentation, I'm
looking forward to seeing your pull request in github (or to receiving
your docs in whatever other format, but the pull request would be the
nicest!)

On 08/07/2018 04:41 PM, 'Thayer Young' via ghini/bauble wrote:
> I don't know if Ghini 3 allows for descriptive aliases for the field names

no, not really, or not in a user-configurable way.  you can add aliases,
in SQLAlchemy-talk they are called "synonyms", but you have to add them
in the code.

we could theoretically add these synonyms dynamically (thanks Python),
but the query builder would not see them, I'm afraid.  you would be able
to use them in the queries you write yourself, but the query builder
only sees 'real' fields.

I've promoted some aliases to field names and demoted field names to
aliases, from 1.0 to 3.1 (specifically: epithet and authorship).

maybe I completely misunderstood what you mean by "aliases" ???

the rest of your mail sounds like an interesting list of intended
contributions, which I'm definitely looking forward to see.  hope you do
manage!

thank you and cheers,

Mario

[Thayer Young]

Thanks for your input, talk to you as this develops.

-Thayer

Mario