Querying a fusion table to see if a polygon contains a coordinate

Question

Is there a way to determine if a coordinate is inside a fusion table layer? I tried to do this with a few homegrown methods but around 6% of the locations I’m testing are returning false results. One of the results says that Glasgow is not in Scotland.

Can I query this Fusion Table and ask it if Glasgow is in Scotland? Preferably returning a boolean value.

1. Glasgow coords: 55.855513, -4.252649
2. Scotland query: Geometry where ‘NAME’ = ‘Scotland’
3. KML column: Geometry
4. Fusion Table Id: 1d5LmqKYvkTDxmYK3-9-aQLNz3Z0QAEwqHusAm5w

I think I have to use ST_CONTAINS or ST_INTERSECTS but I'm not sure how, if they work with polygon shapes.

Overall Solution

Using the query in the given answer you can get the name of the polygon using the FusionTablesAPI.

lat = 55.855513
lng = -4.252649
table = '1d5LmqKYvkTDxmYK3-9-aQLNz3Z0QAEwqHusAm5w'
query = "SELECT NAME FROM #{table} WHERE ST_INTERSECTS(geometry, CIRCLE(LATLNG(#{lat}, #{lng}),1))"
url = "https://www.googleapis.com/fusiontables/v1/query?sql=#{URI.encode(query)}&key=AIzaSyAm9yWCV7JPCTHCJut8whOjARd7pwROFDQ"

Here is the output.

This is actually a better solution than I set out to find.

Using the code in the the given answer highlights the polygon on a map which is awesome. Also, postgres can be used too as suggested by Dr.Molle in the comments.

Answer 1

You can do something like this (FusionTableLayer example):

        layer = new google.maps.FusionTablesLayer({
          query: {
            select: 'geometry',
            from: tableId,
            where: 'ST_INTERSECTS(geometry, CIRCLE(LATLNG(' + lat + ', ' + lng + '),1))',
            limit: 1
          }

Checks for the intersection of the polygon defined in the geometry column and a circle at lat,lng with a diameter of 1 meter.

FusionTablesLayer exmaple