CODEWITHSUNDEEP
mysql
3030
3030

Reputation: 1

SQL select rows where data matches multiple columns

I have multiple pairs of cartesian coordinates (X,Y) calculated by user input (php), which form a range (picture a polygon).

From my database (600 points), I need to find any points that fall within that range (within the polygon), and exclude any that are outside that range.

I would like to do it all in one SQL statement, but I can't seem to get my head around the logic of matching two different values - to two different columns - of the same row.

I suppose I split the data between two tables and use an inner join? But this seems a bit over the top.

I have tried playing with the geometry part of MYSQL (trying to search coord pairs using the "point" datatype), but I cannot see the data I have imported (after a successful import). And when I select all data from a row try and display $row["coords"] (which should be the point data), all I get is a bunch of weird ASCII characters and squares...

So hopefully there is an easy way to do it using regular SQL.

Upvotes: 0

Views: 1972

Answers (1)

rkosegi
rkosegi

Reputation: 14628

Let say you have table like this:

TABLE_1:

ID     |    INT
X_POS  |    INT
Y_POS  |    INT

ID - autoincremented primary key X_POS - X-coordinate of point Y_POS - Y-coordinate of point

Now you have sample data such as these:

ID | X_POS | Y_POS
1  | 3.25  | 1.75
2  |-0.5   | 2.17

etc..

Now, you want to select all rows where point are on X-axis between -1.34 and 1.28 and on Y-axis between -5.63 and0.98

You query should be as follows:

SELECT * FROM TABLE_1 
WHERE 
(X_POS BETWEEN -1.34 AND 1.28) AND
(Y_POS BETWEEN -5.63 AND 0.98)

You can test this sample here: SQLFIDDLE

UPDATE:

You should definitely use MySQL Spatial Extension

I have tried playing with the geometry part of MYSQL (trying to search coord pairs using the "point" datatype), but I cannot see the data I have imported

Please provide more details about your tables (SHOW CREATE TABLE) and the way you used to import data.

Then I may help you.

Upvotes: 1

Related Questions