---
title: "Using Collect Earth with PostgreSQL and PostGIS"
url: "https://openforis.support/questions/2358/using-collect-earth-with-postgresql-and-postgis"
tags: ["collectearth"]
status: "answered"
score: 0
answer_count: 5
asked: "2019-05-08T13:30:28"
last_activity: "2019-05-30T14:05:46"
source: "Open Foris Support - Q&A Forum"
license: "Copyright Open Foris, 2026. Some rights reserved under creative commons license."
---

# Using Collect Earth with PostgreSQL and PostGIS

Asked by Remote_sensor on 2019-05-08

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


## Answer (score 2, by SanchezPausPro on 2019-05-09)

Link: https://openforis.support/questions/2358/using-collect-earth-with-postgresql-and-postgis/2362

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][1] 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.


  [1]: https://gis.stackexchange.com/questions/24486/inserting-point-into-postgis/24487#24487


## Answer (score 0, by collectearth on 2019-05-08)

Link: https://openforis.support/questions/2358/using-collect-earth-with-postgresql-and-postgis/2359

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][1] 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][2]

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?


  [1]: https://www.jooq.org/
  [2]: http://www.openforis.org/support/upfiles/pgAdmin3_2019-05-08_13-41-41.png


## Answer (score 0, by Remote_sensor on 2019-05-08)

Link: https://openforis.support/questions/2358/using-collect-earth-with-postgresql-and-postgis/2360

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.


## Answer (score 0, by Remote_sensor on 2019-05-09)

Link: https://openforis.support/questions/2358/using-collect-earth-with-postgresql-and-postgis/2364

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?


## Answer (score 0, by jurafejfar on 2019-05-30)

Link: https://openforis.support/questions/2358/using-collect-earth-with-postgresql-and-postgis/2376

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][1] step Configure the Database)
 * distribute that .cep file to "operators" computers and Import it to CollectEarth (see [question 17][1] 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][2]) 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?í.

  [1]: http://www.openforis.org/support/questions/17/how-do-you-setup-collect-earth-in-postgresql-mode
  [2]: http://www.openforis.org/support/questions/1793/using-postgresql-db-schema-created-with-collect-for-storing-collect-earth-interpretations