Hello,
I am interested in using Collect Earth in conjunction to a postgresql database.
I have successfully saved some records created in Collect Earth to a postgresql database on a remote server. The problem I am having is that I cannot find where the geometry of the point locations is stored in the database.
Is there something special I have to do in Survey Designer to make sure that Collect Earth saves a geometry column via PostGIS?
Many thanks in advance for your reply
asked
Remote_sensor 11●1●2 accept rate: 0%
Accepted answer
Well, if you really want to use PostGIS then you could go this route:
Once all the plots are collected use the Tools->Start Saiku Analysis to force the generation of the RDB tables inside the afore mentioned "rdbcollectSaiku" schema in the DB.
Now you have all of the tables with the data, where the "main" table is called plot. That table has latitude and longitude columns ( location_y and location_x) and the info on the coordinate system used (should always be 4326 anyway)
If you have PostGIS you could easily make a point using that information using the ST_SetSRID command. See this forum post to get more info on how to do that.
So at the end you will have your data in a PostGIS kind of format. The main caveat of this approach is that if your data changed in Collect Earth you will need to repeat the steps, although this doesn't seem specially complicated.
answered
SanchezPausPro 31●2 accept rate: 0%
Accepted answer
The simple answer is that in Collect Earth we are not using the PostGIS functionalities, just plain old PostgreSQL.
The way that data is stored in the database is a bit complex, as the plot data does not follow your standard Relational Database approach. Instead the plot level data is stored using an API called jOOQ that stores the data as binary chunks, which are basically unreadable to humans. If you export the data to Saiku (Tools->Start Saiku Analysis), and you open PGAdmin you will see that a new schema is created in the PostgreSQL DB (called rdbcollectsaiku) and this one contains actual human readable data were the coordinates for the center of each plot can be obtained. The plot location would be in the "plot" table.
In the Saiku Analytics tool you will also be able to see the location of each plot by using the Plot - Location (or location) dimension.
In any case, perhaps the easiest solution is to export the data as a CSV ( Tools->Data Import/Export->Export data to CSV ) file and there you ca find the location_x and location_y (longitude and latitude) for each plot.
Does this answer your question?
answered
collectearth ♦ 1.0k●1●6 accept rate: 17%
Accepted answer
Thank you very much, that is very clear. I have now used the Saiku Analysis to find the coords generated in the plot table. It's a shame that there's no PostGIS functionality from my point of view, because I also wanted to read and write to the database from QGIS.
answered
Remote_sensor 11●1●2 accept rate: 0%
Accepted answer
Thanks for your answer. I remember that I have generated geometry columns in this way before within PostgrSQL/PostGIS using x and y columns within pre-existing tables. So we could indeed use that method.
I'm very new to CollectEarth. Myself and my colleagues have a PostGIS database which stores information about the condition of archaeological sites. We've been very interested in developing the functionality of Collect Earth to conduct data entry within the Google Earth Pro UI. My colleague developed a method which exports data to a .csv from CollectEarth, alters it with an Excel Macro to reach a template format which we can then bulk upload to our PostGIS database.
We were very interested in the possibility that we might be able to use the survey designer to read and write data to this PostGIS database directly using Collect Earth, but it seems it probably won't be possible to achieve this.
One issue that we have is that sometimes, during the process of data entry, we find that a number of sites within a particular data set have not been located properly and need moving several hundred metres. We haven't yet found a way of moving them during the data entry process in CollectEarth. I thought a live PostGIS connection might solve this but it seems CollectEarth, via Saiku, only writes information to a database, and never the other way around. I.e. we cannot update or populate CollectEarth plots from locational data stored in a PostGIS database. Is that correct?
answered
Remote_sensor 11●1●2 accept rate: 0%
Accepted answer
Hi,
it seems to me, that your "archeological" Use-case doesn't fit well land-use (forest) inventory for what is CollectEarth (CE) designed for... location of plots in such inventory is given by some algorithm and is definitely not changed afterwards. Also it is not good idea to change any other properties of survey design during the survey... collected data may become hard to analyze or interpret.
So the workflow we are following (for the forest inventory) is basically like:
Probably, it is also possible to not use .cep file (see question 1793) and "synchronize" project edited in Collect with central DB schema without the third step (when cep file is imported to CE and it populate empty schema in central DB) but AFAIK this is not tested nor documented. But you still need somehow tell CE "what to do" ... probably only way is through importing .cep file? I am quite interested in this solution, we can share our experience, if you decide to go this way...
With best regards, Ji?í.
answered
jurafejfar 142●9●15 accept rate: 0%
Once you sign in you will be able to subscribe for any updates here
