Created
September 27, 2016 01:04
-
-
Save anonymous/b059242c9aa5e131567c105c6681c19c to your computer and use it in GitHub Desktop.
Check if address is inside KML polgyon google sheets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// replace with your fusion table's id (from File > About this table) | |
var TABLE_ID = 'xxxxxxxxxx'; | |
// first row that has data, as opposed to header information | |
var FIRST_DATA_ROW = 2; | |
var FIRST_DATA_COLUMN = 11; | |
var LAT_COLUMN = 1; | |
var LNG_COLUMN = 2; | |
var SA2_COLUMN = 6; | |
var SA3_COLUMN = 7; | |
/** | |
* Uses a lat and lng data in google sheets to check if an address is within a kml polygon | |
* in a list of KML polygons in fusion (in this case ABS/ASGC SA2 and SA3 regions, but could be any polygon) | |
* the function then stores the ID/name of the relevant polygon in google sheets | |
* I could check this data in realtime as I render maps, but as it doesn't changed, figure its better to just record | |
* which polygon each address pertains to so its quicker and easier to search (in particular it mades it easier to write a query | |
* which identifies all the address within multiple polygons) | |
* in this case I had 3000 rows so it exceeded maximum execution times, so I just updated the first data row a couple of times | |
* when the execution time exceeded. | |
*/ | |
function updateSA2ID() { | |
var tasks = FusionTables.Task.list(TABLE_ID); | |
var sqlResponse = ''; | |
// Only run if there are no outstanding deletions or schema changes. | |
if (tasks.totalItems === 0) { | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var latLngData = sheet.getRange(FIRST_DATA_ROW, FIRST_DATA_COLUMN, sheet.getLastRow(), sheet.getLastColumn()); | |
i = 1; | |
// Loop through the current current sheet | |
for (i = 1; i <= latLngData.getNumRows(); i++) { | |
// cross reference to Fusion table | |
lat = latLngData.getCell(i,LAT_COLUMN).getValue(); | |
lng = latLngData.getCell(i,LNG_COLUMN).getValue(); | |
sqlString = "SELECT 'SA2 Code', 'SA3 Code' FROM " + TABLE_ID + " WHERE ST_INTERSECTS(geometry, CIRCLE(LATLNG(" + lat + ", " + lng + "),1)) "; | |
//Browser.msgBox('Lat ' + lat + ' Lng ' + lng + '; ' + sqlString, Browser.Buttons.OK); | |
sqlResponse = FusionTables.Query.sql(sqlString); | |
//Browser.msgBox('SQL Response ' + sqlResponse, Browser.Buttons.OK); | |
latLngData.getCell(i,SA2_COLUMN).setValue(sqlResponse.rows[0][0]); // set SA2 | |
latLngData.getCell(i,SA3_COLUMN).setValue(sqlResponse.rows[0][1]); // set SA3 | |
} | |
} | |
else { | |
Logger.log('Skipping row replacement because of ' + tasks.totalItems + ' active background task(s)'); | |
} | |
}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment