cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Learn all about the Community Ranking System, a fun gamification element of the PTC Community. X

How to import google map location data from database

tguo
4-Participant

How to import google map location data from database

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ankigupta
5-Regular Member
(To:tguo)

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.

View solution in original post

15 REPLIES 15
ankigupta
5-Regular Member
(To:tguo)

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
4-Participant
(To:ankigupta)

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
4-Participant
(To:ankigupta)

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
4-Participant
(To: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
5-Regular Member
(To:tguo)

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
4-Participant
(To:ankigupta)

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:

WeChat Image_20170518172851.png

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

tguo
4-Participant
(To:tguo)

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

tguo
4-Participant
(To:ankigupta)

Error like this:

WeChat Image_20170518173952.png

really last step. I am trying.

ankigupta
5-Regular Member
(To:tguo)

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
4-Participant
(To:ankigupta)

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:

WeChat Image_20170519094705.png

Something is wrong but no idea.

Any other thingworx masters have solutions on this?

Thanks!!

ankigupta
5-Regular Member
(To:tguo)

Could you please share Screenshot of fields in MapData DataShape.

tguo
4-Participant
(To:ankigupta)

The MapData datashape is no problem. I think..

WeChat Image_20170519150038.png

yshen-2
5-Regular Member
(To:tguo)

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
19-Tanzanite
(To:tguo)

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
5-Regular Member
(To:supandey)

Sushant Pandey​ Sure!

Top Tags