How to import google map location data from database

[tguo]

Hi all,

    I have a problem on how to import google map location data from database.

    The longitude, latitude, elevation are stored in different column in the postgresql database. I want to connect the database to the google map widget.

    How can I do this?

    BTW:

    I know how to insert location data through Thing->Services (javascript) and extract location data also through Thing->Services to a datashape and bind the datashape to the google map. But now the location data will from columns of database? I am a little confusing.

[ankigupta]

Hi Tiantian Guo​,

Per my understanding; this will require conversion of longitude, latitude, elevation data to a Location Object.

Steps:

1. Read longitude, latitude, elevation Data from DataBase in an infotable.

2. Loop through the Table and convert longitude, latitude, elevation data to Location Object infotable. Example:

// location:LOCATION
var location = new Object();
location.latitude = latitudeFromDataBase;
location.longitude = longitudeFromDataBase;
location.elevation = elevationFromDataBase;
location.units = "WGS84";

3. Bind the new infotable to the Google widget.

I hope it helps.

[tguo]

Thanks Ankit. I am now doing like this.

But I now want to get a new column "location" from other three columns. I found some solutions. The grammar for thingworx needs to get used to..

Currently my code is like:

var params = {
infoTableName : "MapDataWithLocation",
dataShapeName : "MapData"
};

// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(MapData)
var result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);

var buildinglocation = new Object();
buildinglocation.name = "buildinglocation";
buildinglocation.baseType = 'location';

result

got some errors.. still working..

[tguo]

And some loop like this:

for (i = 0; i < result.getRowCount(); i++) {

    myfield.latitude = result.getRow(i).latitude;

    myfield.longitude = result.getRow(i).longitude;

    myfield.elevation = 0;

    myfield.units ="WGS84";

    result.getRow(i).buildinglocation = myfield;

}

[tguo]

Finally the code is no bug:

var params = {
 infoTableName : "MapDataWithLocation",
 dataShapeName : "MapData"
};
// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(MapData)
var result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);
var newField = new Object();
newField.name = "buildinglocation";
newField.baseType = 'LOCATION';
result.AddField(newField);
var tableLength = result.rows.length;
for (var x = 0; x < tableLength; x++) {
 var row = yourInfotableHere.rows;
 //Your code here
    row.buildinglocation.latitude = row.latitude;
    row.buildinglocation.longitude = row.longitude;
    row.buildinglocation.elevation = 0;
    row.buildinglocation.units ="WGS84";
}

My input is a datashape.

The datashape is a output from the database.

But this code output is just fields of a new datashape. Where I get the data?... Really a newbie, sorry and thanks!

[ankigupta · Accepted solution]

Hi Tiantian Guo​,

Please try something like:

// result: INFOTABLE  dataShapeName : "MapData"
var result = Things["DBThing"].Service1(); // Change the ThingName and Service name here
var newField = new Object(); 
newField.name = "buildinglocation"; 
newField.baseType = 'LOCATION'; 
result.AddField(newField);
// location:LOCATION 
var location = new Object();
var tableLength = result.rows.length; 
for (var x = 0; x < tableLength; x++) { 
var row = result.rows; 
    location.latitude = row.latitude; 
    location.longitude = row.longitude; 
    location.elevation = 0; 
    location.units = "WGS84"; 
    result.rows.buildinglocation = location;
}

I hope it helps.

[tguo]

Dear Ankit,

      It is really helpful. I think I start to understand the thingworx function now..

      I change your code like this:

// result: INFOTABLE  dataShapeName : "MapData"
var result = Things["ExtractData"].extractdata(); // Change the ThingName and Service name here
var newField = new Object(); 
newField.name = "location"; 
newField.baseType = 'LOCATION'; 
result.AddField(newField);
// location:LOCATION 
var temp = new Object();
temp.baseType = 'LOCATION';
var tableLength = result.rows.length; 
for (var x = 0; x < tableLength; x++) { 
    var row = result.rows; 
    temp.latitude = row.latitude; 
    temp.longitude = row.longitude; 
    temp.elevation = 0; 
    temp.units = "WGS84"; 
    result.rows.location = temp;
}

But the output like this:

something wrong here. But it really a big step for me. Thanks!

[tguo]

I think I get it. The result I get from table is "double". I need to convert it to double. Trying

[tguo]

Error like this:

really last step. I am trying.

[ankigupta]

Hi Tiantian Guo​,

Could you please share the MapData DataShape here. Try changing the Datatype of latitude and longitude to number.

I have tried the code on my system and it works fine.

Also, I hope you have set the DataShape in the ExtractData DataShape.

I hope it helps.

[tguo]

Dear Ankit,

My code is finally like this and compiled successfully:

// result: INFOTABLE  dataShapeName : "MapData"
var result = Things["EThingName"].ServicesName(); // Change the ThingName and Service name here
var newField = new Object(); 
newField.name = "location"; 
newField.baseType = 'LOCATION'; 
result.AddField(newField);
// location:LOCATION 
var temp = new Object();
temp.latitude = "";
temp.longitude = "";
temp.elevation = "";
temp.units = "WGS84";
temp.baseType = "LOCATION";
var tableLength = result.rows.length; 
for (var x = 0; x < tableLength; x++) { 
    var row = result.rows;
    temp.latitude = row.latitude;//result.rows.latitude; 
    temp.longitude = row.longitude;//result.rows.longitude; 
    temp.elevation = 0; 
    temp.units = "WGS84"; 
    row.location = temp;
}

But the output is like:

Something is wrong but no idea.

Any other thingworx masters have solutions on this?

Thanks!!

[ankigupta]

Could you please share Screenshot of fields in MapData DataShape.

[tguo]

The MapData datashape is no problem. I think..

[yshen-2]

Pleas move

var temp = new Object();

inside for loop

other wise you are setting all the location field to one object and do the change on one object

Thanks

YUanwu Shen

[supandey]

Ankit Gupta​ I see this thread has already been answered and Tiantian Guo​ has opened another for issue on NaN could you please have following discussion on that thread i.e. How to solve the NaN value in updated location field

Thanks

Sushant

[ankigupta]

Sushant Pandey​ Sure!