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 08 May '19, 13:30

Remote_sensor's gravatar image

Remote_sensor
1112
accept rate: 0%


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:

  • design the survey in Collect (create .cep file) -- this contains also grid of plot positions
  • create central database (empty schema) for storing interpretations (see question 17 step Configure the Database)
  • distribute that .cep file to "operators" computers and Import it to CollectEarth (see question 17 step SET UP COLLECT EARTH)
  • this will create tables in schema collect created in previous step if I remember correctly
  • collect data using CollectEarth (interpretaions of plots are inserted into central PostgreSQL DB)
  • "export" data to Saiku (from schema collect to schema rdbcollectsaiku)
  • this is done when saiku is started
  • this step can not be reverted

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?í.

permanent link

answered 30 May '19, 14:05

jurafejfar's gravatar image

jurafejfar
32310
accept rate: 0%

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?

permanent link

answered 09 May '19, 11:12

Remote_sensor's gravatar image

Remote_sensor
1112
accept rate: 0%

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.

permanent link

answered 09 May '19, 10:09

SanchezPausPro's gravatar image

SanchezPausPro
312
accept rate: 0%

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.

permanent link

answered 08 May '19, 17:25

Remote_sensor's gravatar image

Remote_sensor
1112
accept rate: 0%

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.

alt text

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?

permanent link

answered 08 May '19, 13:58

collectearth's gravatar image

collectearth ♦
1.0k16
accept rate: 17%

edited 08 May '19, 13:59

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×281

question asked: 08 May '19, 13:30

question was seen: 3,514 times

last updated: 30 May '19, 14:05