How to tell if a point is in a kilometer or in shape
I have form data (series of kml files in a column of a merge and / or database table) and I want to merge it with another table that contains latitude longitude points. Basically I want some way to determine if a given lat lon point is contained within kml shapes, and if so, keep a reference to that line. I though there may have been a way to do this from the merge tables, but if not, there may be a way to skip every kilometer and check if it contains the lat lon point. I understand this is not very effective. Any help, algorithm, service, etc. Would be great.
source to share
Fusion tables. The SQL API has an ST_INTERSECTS statement, but only finds points within CIRCLE or RECTANGLE. GMap V3 has a geometry library that has a poly.containsLocation () method which I think will work for arbitrary polygons. See: Geometry / Poly Library GoogleMap
PS I understand this doesn't work for KML files, but they contain polygonal points that can be turned into GMAP polygons
source to share
This is probably long ago solved, but since I had a similar problem I thought I'd post my solution.
I had two Fusion tables: i) address and data and ii) polygons and data. I wanted to be able to easily query all addresses in one or more polygons. I could do this in real time via my map web page, but I figured it would be better to first find the corresponding polygon and then use that data to execute my map queries (it is faster and easier to specify multiple polygons in the web interface).
So I exported my i) table with addresses to google sheets and created a short simple script that checked which of the polygons was in the address and wrote it back to the google sheet. Then I updated the fusion table i).
I had a dataset of almost 3000 addresses and 2000 polygons, so it timed out a few times and had a couple of address errors, but it was easy to fix and I just set up a script to run from the first line that was not updated. Note that this will not work if the polygons overlap each other, but mine is not the same as they were geographic boundaries; -)
The code I used below and also on the gist here . You obviously need to update the table id and probably need to do something with OAuth (which I didn't quite understand, but followed Google's instructions and did it).
// replace with your fusion table 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)');
}
};
source to share