Sketchup8 Geo-location linking to a database via rubyscript

Ken007

Hey!
I have a problem that's been bothering me for quite a while now. Its like this that I have created a few 3D models in Sketchup 8 (the freely downloadable version) and now I want to geo locate them as per their GPS Co-ordinates (so i'm not looking to go through the usual 'add location button'. Now that thing is, I have written a script for sketchup to open the .skp files that contain the 3D models that I've created and I've linked it to another script that adds the GPS location as well as exports it to the desired location. The problem is that all of this happens through a GUI that the script generates. I want all this to occur directly through a database (for example MS Excel) such that the "name of the model", the "GPS co-ordinates/lat-longs" are taken as an input from the Excel database. The code I've written is as follows: (It is fully functional, though, I'm relatively new to sketchup so dont mind if it's a wee bit shabby)

This is the code that opens the 3D model::

prompts = ["Status"]
 defaults = [""]
 list = ["House|Garden|Palace|Dog house"]
 input = UI.inputbox prompts, defaults, list, "Choose a template" 
 
 if input[0].to_s.eql? "Garden" 
 result = Sketchup.open_file "E;\\GoogleEarth\\FINALFINAL\\NewFolder\\garden.skp"
 
 elsif input[0].to_s.eql? "House" 
 result = Sketchup.open_file "E;\\GoogleEarth\\FINALFINAL\\NewFolder\\house.skp"
 elsif input[0].to_s.eql? "Palace" 
 result = Sketchup.open_file "E;\\GoogleEarth\\FINALFINAL\\NewFolder\\palace.skp"
 elsif input[0].to_s.eql? "Dog house" 
 result = Sketchup.open_file "E;\\GoogleEarth\\FINALFINAL\\NewFolder\\dog_house.skp"
 
 end
 
 load 'C;\Documents and Settings\91014276\Desktop\untitled(3).rb'

and this is 'untitled(3).rb' that actually geo locates the 3D model and exports it to the desired location::

prompts = ["Latitude", "Longitude", "Location", "Destination Drive"]
 defaults = ["", "", "xyz", "E;\\Examples"]
 list = ["", "", ""]
 input = UI.inputbox prompts, defaults, list, "About the WEC"
 


model = Sketchup.active_model

# This is the bit that actually sets the geo-location.
  shadowinfo = model.shadow_info
  shadowinfo["City"] = "Unknown"
  shadowinfo["Country"] = "Unknown"
  shadowinfo["Latitude"] = input[0].to_f
  shadowinfo["Longitude"] = input[1].to_f

georef = model.georeferenced?

key = "GeoReference"
  model.set_attribute key, "GeoReferenceNorthAngle", 358.646700673226
  model.set_attribute key, "UsesGeoReferencing", true
  model.set_attribute key, "Longitude" , input[1].to_f
  model.set_attribute key, "Latitude" ,  input[0].to_f
  model.set_attribute key, "ModelTranslationX" ,  -12901579.2256146
  model.set_attribute key, "ModelTranslationY" , -214086056.635273
  model.set_attribute key, "ModelHereState", ""
  model.set_attribute key, "ModelHereZoom", 19
  model.set_attribute key, "ModelTranslationZ" , 0.0
  model.set_attribute key, "LocationSource" , "Google Earth"
  model.set_attribute key, "ZValueCentered" ,  0.0
  model.set_attribute key, "TimeStamp", 1296598526
  model.set_attribute key, "ModelHereState", ""
  model.set_attribute key, "ModelHereZoom", 19

# Export model to kmz file for Google Earth
	
  model.export "#{input[3]}\\ #{input[2]}.kmz", true
  
  result = UI.messagebox "Is that all or would you like to try another?", MB_YESNO
 if result == 6 # Yes
   load 'C;\Documents and Settings\91014276\Desktop\untitled(5).rb'
 
 elsif result == 7 # No
   UI.messagebox ("Alrighty then!")
   
   end

In the above code 'input[0].to_f' and 'input[1].to_f' are the Latitude and Longitude co-ordinates where the model has to be placed.

Im in need of some desperate help here so please...err..help!

TIG

Data in a readable/writable text-based file format [for example CSV or TSV files] is readily parsed in Ruby using IO..., File.open... etc...
There are many examples of doing that, e.g. tools that import X,Y,Z values as cpoints from CSV... do a search for those - I have made some...
Unfortunately Excel files [XLS] are only readable/writable on a PC and need to use a .so link... again there have been some threads on that - it is much more complicated than using a simple format but I have written bespoke XLS read/write tools, but none quite like you envisage...

Ken007

@tig said:

Data in a readable/writable text-based file format [for example CSV or TSV files] is readily parsed in Ruby using IO..., File.open... etc...
There are many examples of doing that, e.g. tools that import X,Y,Z values as cpoints from CSV... do a search for those - I have made some...
Unfortunately Excel files [XLS] are only readable/writable on a PC and need to use a .so link... again there have been some threads on that - it is much more complicated than using a simple format but I have written bespoke XLS read/write tools, but none quite like you envisage...

Hmm...first of all, thanks for a such a prompt response TIG.
ok, now I have a few questions:

This might seem like a stupid/juvenile question, but I am new to rubyscript and its applications is sketchup; how do I use the aforementioned IOs like File.open. I did a google search but couldn't end up finding anything that I could understand
Can you give me an example of the XLS read/write tools that you speak of please?
Microsoft Excel has the provision of developer tools if I'm not wrong. In fact I've used VBA in MS excel a couple of times for certain small tasks. Any idea if its possible to generate a CSV file from data in excel. I'm basically trying to create a link between the geo-location tool in sketchup and a database.

For example, the Excel data reads:
[pre:2o2zulb1]Model Lat Long Status
House 15.24566 75.12643 CNF[/pre:2o2zulb1]

Skechup must read the model name 'Hoose' and open the respective .skp file. Then it must read the 'Latitude and Longitude' and make the required changes in the geo location.
Now as soon as Sketchup reads 'CNF' (i.e. confirmed) from the database, it must export the 3D model and place it in a default folder (the code I've mentioned in my original post does all that but I need it to be done dynamically from a database)

TIG

Rather than trying to learn how to read binary XLS files [very complex to do compared to parsing a text format] can I suggest you simply do a save_as on the XLS file as a CSV...
You then have a readily readbale file containing lines saying:
Model,Lat,Long,Status
Using:

csv = 'path_to_csv'
IO.readlines(csv).each{|line| ### read the file line by line
  line.chomp! ### removes \n
  next if line.empty? ### empty lines are not used
  next if line=~/^[#]/ ### starts with '#' so it is ignored - e.g. the header
  model,lat,long,status = line.split(',')
  ### now you have the model/lat/long/status as strings
  ### perhaps use model = model+'.skp' ??
  ### convert the lat/long to float using lat=lat.to_f etc...
  ### ..............
  ### do your processing on these values... 
  ### then the next line is ready...
  ### ..............
}#end of the block

There are many good sources of Ruby look in the Developer's section - Dan has links off to many... leike this http://www.ruby-doc.org/docs/ProgrammingRuby/html/builtins.html

Ken007

@tig said:

Rather than trying to learn how to read binary XLS files [very complex to do compared to parsing a text format] can I suggest you simply do a save_as on the XLS file as a CSV...
You then have a readily readbale file containing lines saying:
Model,Lat,Long,Status
Using:

csv = 'path_to_csv'
> IO.readlines(csv).each{|line| ### read the file line by line
>   line.chomp! ### removes \n
>   next if line.empty? ### empty lines are not used
>   next if line=~/^[#]/ ### starts with '#' so it is ignored - e.g. the header
>   model,lat,long,status = line.split(',')
>   ### now you have the model/lat/long/status as strings
>   ### perhaps use model = model+'.skp' ??
>   ### convert the lat/long to float using lat=lat.to_f etc...
>   ### ..............
>   ### do your processing on these values... 
>   ### then the next line is ready...
>   ### ..............
> }#end of the block

There are many good sources of Ruby look in the Developer's section - Dan has links off to many... leike this http://www.ruby-doc.org/docs/ProgrammingRuby/html/builtins.html

Thanks TIG! your idea worked wonders!.. The code I've written is a little shabby at the moment but it works just fine. I'll sort it out and then post it here as soon as possible in case someone needs it. Thanks once again!

spireup

Does anyone know how the Model Translation Attributes affect the eventual location in Google Earth?

Im trying to get code to geolocate a model too however just entering a longitude and latitude manually in sketch up is not working. Im getting the long and lat from a place mark in Google Earth but when I put those values in the Sketchup model manually the model is placed far away from that place.

[Edit: My problem was not converting from the degrees, minutes and seconds format in Google Earth to the decimal format used by Sketchup.
e.g. https://www.fcc.gov/encyclopedia/degrees-minutes-seconds-tofrom-decimal-degrees]

Im still interested to know the purpose of these attributes i.e.
model.set_attribute key, "ModelTranslationX" , -12901579.2256146
model.set_attribute key, "ModelTranslationY" , -214086056.635273

Thanks...

Paul Francis