How to enter GPS location attributes manually

david.smith · December 4, 2014, 4:05pm

Hi

I have created a card with a Geographical Attribute of type POINT. This lets me choose a Card's position on a map by pointing and clicking.

However what I want to do is also have the ability to enter the GPS longitude and longitude either manually or by uploading it from XLS file.

Is there anyway to do this?

Kind regards,

David/

tecnoteca · December 5, 2014, 7:54am

Hi David,

here is the procedure for manually entering geographical attributes.

First of all, execute this select on your database:

select * from spatial_ref_sys where srid = 4326;

If it returns no rows, then execute this insert:

INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","srtext","proj4text") VALUES (4326,'EPSG',4326,'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],

UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]','+proj=longlat +datum=WGS84 +no_defs ');

Then you can insert the position of a card using a statement like this:

insert into gis."Detail_Building_Position" ("Master","Geometry")

values(the_id_of_the_card,ST_transform(ST_SetSRID(ST_MakePoint(13.223612, 46.108525),4326),900913));

where

- Building is the name of the class

- Position is the name of the geographical attribute

- the value in the Master column must be the Id of the card (in the corresponding tatble in the public schema)

- 13.223612, 46.108525 are longitude and latitude of the position of the card

Best regards

CMDBuild Team

david.smith · December 5, 2014, 11:58am

Thanks for answer, but when I said manually I meant when a user creates the Card on the browser GUI.

So for example, can we allow the users to enter longitude and latitude attributes on the GUI and then these are used display the Card position on the map.

Previously Tecnoteca wrote:

Hi David,

here is the procedure for manually entering geographical attributes.

First of all, execute this select on your database:

select * from spatial_ref_sys where srid = 4326;

If it returns no rows, then execute this insert:

INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","srtext","proj4text") VALUES (4326,'EPSG',4326,'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],

UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]','+proj=longlat +datum=WGS84 +no_defs ');

Then you can insert the position of a card using a statement like this:

insert into gis."Detail_Building_Position" ("Master","Geometry")

values(the_id_of_the_card,ST_transform(ST_SetSRID(ST_MakePoint(13.223612, 46.108525),4326),900913));

where

- Building is the name of the class

- Position is the name of the geographical attribute

- the value in the Master column must be the Id of the card (in the corresponding tatble in the public schema)

- 13.223612, 46.108525 are longitude and latitude of the position of the card

Best regards

CMDBuild Team

tecnoteca · December 5, 2014, 7:11pm

You could add in your class two custom attributes “Longitude” and “Latitude” and then create a database trigger that executes the insert (or update) command that we have suggested in the previous answer

.

CMDBuild Team

david.smith · December 8, 2014, 1:55pm

This does seem rather complicated for a simple data entry, also it appears to me we would to duplicate this trigger for every Card that has a GPS location.

In addition would this method continue to work if someone chose to change the location using standard method of selecting a point on the map?

Previously Tecnoteca wrote:

You could add in your class two custom attributes "Longitude" and "Latitude" and then create a database trigger that executes the insert (or update) command that we have suggested in the previous answer
.

CMDBuild Team

tecnoteca · December 8, 2014, 3:11pm

Previously David wrote:

This does seem rather complicated for a simple data entry, also it appears to me we would to duplicate this trigger for every Card that has a GPS location.

Absolutely not, the database triggers work on all the records that are inserted, modified, deleted in a table:
http://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html
But if this is too complicated, you can use the standard CMDBuild function.

In addition would this method continue to work if someone chose to change the location using standard method of selecting a point on the map?

Obviously yes.

CMDBuild Team

david.smith · December 10, 2014, 11:49am

The triggers aren't complicated, just seems a complicated solution for entering a simple attribute.

Anyway lets not argue about that because I have created the trigger, but I've found some very strange behaviour when I try to set the card attributes using the manually entered attributes.

First it all works fine if I use psql or pgadmin to INSERT or UPDATE the card's attribute. However when I try to use the CMDBuild GUI to update the Card the corresponding record was not created in the gis 'Detail' table. At first the error appeared to be trigger wasn't being fired. The only way I could get it to work was by first clicking the 'Add' button on the little pop up window that appears on the map.

I then set about tracing the flow through the numerous triggers in CMDB and it looks like what is happening is when I use the GUI is:

My trigger is fired and it inserts the record. This is as expected.
Then afterwards _cm_trigger_sanity_check_simple is triggered for some reason by a DELETE OP to delete the gis table records associated to the card. Note - this does NOT happen if do the insert or update from pg_admin.

So all I can think is that somewhere in CMDB there is some logic which says if you haven't clicked the 'Add' button then to automatically delete any gis 'Detail' records related to the card.

So I'm wondering if there is something else I need to do in the trigger to stop this happening?

Below is the logging, see the last 2 statements for the unexplained DELETE operations. The Card name is AccessPoint, the attribute I added for the entering the GPS coordinates is gpsCoordinates and the Card id I was updating was 79.

<code>

2014-12-10 11:14:38 GMT LOG: ===AFTER INSERT=====> Inserting record access point 79, POINT(4349478.70649849 -31660.9516805029), newRecId 359

2014-12-10 11:14:38 GMT CONTEXT: SQL statement "SELECT ap_updateGisRecordFromGPSCoordStr(NEW."Id", NEW."gpsCoordinates")"

PL/pgSQL function _update_access_point_gis_from_gps_location() line 18 at PERFORM

2014-12-10 11:14:38 GMT STATEMENT: UPDATE "AccessPoint" SET "Notes" = $1, "Description" = $2, "SSID" = $3, "MaxSpeedMBPS" = $4, "Contact" = $5, "Hotspot" = $6, "DeviceType" = $7, "Code" = $8, "gpsCoordinates" = $9, "User" = $10 WHERE "Id" = $11

2014-12-10 11:14:38 GMT LOG: *** AFTER UPDATE. Id 79, new gps S0.284414 , E39.072032, old gps S0.284414 , E39.072032

2014-12-10 11:14:38 GMT STATEMENT: UPDATE "AccessPoint" SET "Notes" = $1, "Description" = $2, "SSID" = $3, "MaxSpeedMBPS" = $4, "Contact" = $5, "Hotspot" = $6, "DeviceType" = $7, "Code" = $8, "gpsCoordinates" = $9, "User" = $10 WHERE "Id" = $11

2014-12-10 11:14:38 GMT LOG: ===_cm_trigger_sanity_check_simple====> DELETE OP, return OLD

2014-12-10 11:14:38 GMT STATEMENT: DELETE FROM "gis"."Detail_AccessPoint_gpsLocation" WHERE "Master" = 79

</code>

Finally here is logging for updating the same card using pgadmin, notice there is not delete operation.

<code>

014-12-10 11:42:59 GMT LOG: ===_cm_trigger_sanity_check_simple====> MUST BE AN INSERT 79

2014-12-10 11:42:59 GMT CONTEXT: SQL statement "INSERT INTO gis."Detail_AccessPoint_gpsLocation" ("Master","Geometry")

VALUES(accessPointId, apPoint)

RETURNING "Id""

PL/pgSQL function ap_updategisrecordfromgpscoordstr(bigint,character varying) line 46 at SQL statement

SQL statement "SELECT ap_updateGisRecordFromGPSCoordStr(NEW."Id", NEW."gpsCoordinates")"

PL/pgSQL function _update_access_point_gis_from_gps_location() line 18 at PERFORM

2014-12-10 11:42:59 GMT STATEMENT: UPDATE "AccessPoint" SET "gpsCoordinates"='S1.28356533 ,E39.07370596' WHERE "Id" = 79;

2014-12-10 11:42:59 GMT LOG: ===AFTER INSERT=====> Inserting record access point 79, POINT(4349665.0508733 -142897.792106271), newRecId 366

2014-12-10 11:42:59 GMT CONTEXT: SQL statement "SELECT ap_updateGisRecordFromGPSCoordStr(NEW."Id", NEW."gpsCoordinates")"

PL/pgSQL function _update_access_point_gis_from_gps_location() line 18 at PERFORM

2014-12-10 11:42:59 GMT STATEMENT: UPDATE "AccessPoint" SET "gpsCoordinates"='S1.28356533 ,E39.07370596' WHERE "Id" = 79;

2014-12-10 11:42:59 GMT LOG: *** AFTER UPDATE. Id 79, new gps S1.28356533 ,E39.07370596, old gps S1.28356533 ,E39.07370596

2014-12-10 11:42:59 GMT STATEMENT: UPDATE "AccessPoint" SET "gpsCoordinates"='S1.28356533 ,E39.07370596' WHERE "Id" = 79;

</code>

Previously Tecnoteca wrote:

Previously David wrote:

This does seem rather complicated for a simple data entry, also it appears to me we would to duplicate this trigger for every Card that has a GPS location.

Absolutely not, the database triggers work on all the records that are inserted, modified, deleted in a table:
http://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html
But if this is too complicated, you can use the standard CMDBuild function.

In addition would this method continue to work if someone chose to change the location using standard method of selecting a point on the map?

Obviously yes.

CMDBuild Team

david.smith · December 12, 2014, 5:06pm

Hi

A few days ago I posted in a reply saying that the gis 'Detail_*' table for card was being cleared after the trigger inserted the row. This was due to the browser frontend thinking there was no gis features for the card (due to the fact that a point wasn't selected on the map), and the gis 'Detail_*' table rows associted to card were deleted.

Did you receive the post? If not I can post it again, or raise it with your support.

Otherwise if you did receive it, can you tell me if there is any progress on an answer to the issue.

Kind regards

David Smith

Previously Tecnoteca wrote:

Previously David wrote:

This does seem rather complicated for a simple data entry, also it appears to me we would to duplicate this trigger for every Card that has a GPS location.

Absolutely not, the database triggers work on all the records that are inserted, modified, deleted in a table:
http://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html
But if this is too complicated, you can use the standard CMDBuild function.

In addition would this method continue to work if someone chose to change the location using standard method of selecting a point on the map?

Obviously yes.

CMDBuild Team

tecnoteca · December 12, 2014, 5:15pm

Sorry, we had not received the notification mail of your previous post and we had not published it.
Now we have published it and we can forward your question to our technicians.
CMDBuild Team

tecnoteca · December 15, 2014, 3:37pm

We have carried out the operations described in your post and everything worked properly.
In order to repeat the situation that you described we would need to have a copy of your database.
CMDBuild Team

david.smith · December 17, 2014, 7:37pm

Did you try entering the co-ordinates while the map view was up on the display?

If you have the map display on the browser, and do Add card, enter the long and lat, and then Ok. You will find the card record is not in the card gis Detail" table.

If that doesn't work tell me how to send you the db and I'll be happy to send it.

Or I could demo you the issue via skype call, let me know what you prefer.

Previously Tecnoteca wrote:

We have carried out the operations described in your post and everything worked properly.
In order to repeat the situation that you described we would need to have a copy of your database.
CMDBuild Team

tecnoteca · December 18, 2014, 1:21pm

Hi,

now I have managed to reproduce the problem. It appears only if the map is open, doesn't it? If the map is closed everything should work as expected. This is a side-effect of the logic of the gis module. We will think about some workaround for your problem and we will let you know if there is a solution.

Best regards

CMDBuild Team

david.santos · September 1, 2016, 2:48pm

Hi,

Regardin this subject there is any way of insert/update or delete a geographical attribute using Rest web service? If yes how?

Thanks,

David Santos

Previously Tecnoteca wrote:

Hi,
now I have managed to reproduce the problem. It appears only if the map is open, doesn't it? If the map is closed everything should work as expected. This is a side-effect of the logic of the gis module. We will think about some workaround for your problem and we will let you know if there is a solution.

Best regards

CMDBuild Team

tecnoteca · September 1, 2016, 6:50pm

As you can read in the last CMDBuild newsletter ( http://www.cmdbuild.org/en/diffusione/newsletter/cmdbuild-newsletter-n.-56 ) the next version 2.4.2 of CMDBuild, expected in about 1 month, will include REST methods to manage GIS data.

CMDBuild Team

david.santos · September 2, 2016, 1:57pm

Hi,

OK many thanks for you clarification.

Regards,

David Santos

Previously Tecnoteca wrote:

As you can read in the last CMDBuild newsletter ( http://www.cmdbuild.org/en/diffusione/newsletter/cmdbuild-newsletter-n.-56 ) the next version 2.4.2 of CMDBuild, expected in about 1 month, will include REST methods to manage GIS data.

CMDBuild Team