How do I Join Two MySQL Tables Using a Function

Question

Hi, I need some help joining two table. I've got:

my_type_table, which has columns:

• type (VARCHAR)
• latitude (decimal)
• longitude (decimal)

...and neighborhood_shapes, which has columns:

• neighborhoods (VARCHAR)
• neighborhood_polygons (geometry)

I've got a function called myWithin which checks the latitude and longitude to see whether they are in the neighborhood polygon. It takes the lat long and the neighborhood shape as parameters. The myWithin function returns 0 if the point is not in the polygon and 1 if it is within the polygon.

I can make a select statement as follows:

SELECT type, latitude, longitude, 'Newport' AS neighborhood
  FROM my_type_table
WHERE myWithin(POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , 
               (SELECT neighborhood_shapes.neighborhood_polygons
                  FROM neighborhood_shapes
                 WHERE neighborhood_shapes.neighborhoods = 'Newport')) = 1

The results of this select are for example:

type  |  latitude | longitude | neighborhood
---------------------------------------------
small |  30.3030  | -70.7070  | Newport

My problem is that I have a lot of neighborhoods. I don't want to have to input the neighborhood each time. Is there a way to remove "Newport"? Basically, I want the function to run on each point and give me the type, latitude, longitude, and which neighborhood the point is in?

I could copy the above select and join the select statements with UNION ALL, but it would be a nightmare typing in each neighborhood's name. There's got to be a better way.

Any suggestions?

Answer 1

Have you tried...

SELECT DISTINCT type,
                latitude,
                longitude,
                ns.neighborhoods AS neighborhood
    FROM my_type_table,
         neighborhood_shapes ns
    WHERE myWithin(POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , 
                   (SELECT neighborhood_shapes.neighborhood_polygons
                        FROM  neighborhood_shapes
                        WHERE neighborhood_shapes.neighborhoods = ns.neighborhoods)) = 1

Answer 2

i didnt test this of course... but it seems like it could be restructured similar to this:

SELECT t.type, t.latitude, t.longitude, s.neighborhood 
  FROM my_type_table t, neighborhood_shapes s
WHERE myWithin(POINTFROMTEXT( CONCAT( 'POINT(', t.latitude, ' ', t.longitude, ')' ) ) ,               s.neighborhood_shapes.neighborhood_polygons ) = 1

Answer 3

function getNeighborhood ($neighborhood) {

$sql = "SELECT type, latitude, longitude, 'Newport' AS neighborhood FROM my_type_table WHERE myWithin(POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , (SELECT neighborhood_shapes.neighborhood_polygons FROM neighborhood_shapes WHERE neighborhood_shapes.neighborhoods = $neighborhood)) = 1";

$result = /** Do Query Here**/

return $result; }