SQL select rows where data matches multiple columns

Question

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.

As a tip: you can join a table with itself, it's allowed in SQL. — Radu Murzea
– Radu Murzea, Commented Jul 17, 2012 at 6:22

pimarc · Accepted Answer · 2018-04-03 15:47:50Z

1

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.

edited Apr 3, 2018 at 15:47

pimarc

4,2739 gold badges46 silver badges73 bronze badges

answered Jul 17, 2012 at 4:09

rkosegi

14.8k5 gold badges57 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

3030 Over a year ago

Thanks, I think that will work. That is basically what I had but without the brackets between the AND statement, so I was getting some rouge results. My (lack of) SQL syntax has always been the problem :)

3030 Over a year ago

Hi again. I will test tomorrow but I am pretty sure it won't work. That is the statement I had (but without the brackets). The problem is, that code is fine if the polygon is a square, but it's an irregular polygon (a diamond). So I am getting some rouge results with that code, ie: a point may be between the X axis (min - max) and the Y axis (min - max) values, but is actually outside of the true polygon. I think I need to rethink my logic completely.

3030 Over a year ago

Hi again. Just to make it clear. I end up with 4 sets of coordinates from the user input [x=10,y=10] [x=10,y=20] [x=15,y=15] [x=5,y=5]. I need to order them into two groups, X_max, X_min, and Y_max, Y_min. Then test each row of the database. So if the X value of a point (in the db) is between X_max and X_min, the Y value of that point must be between Y_max and Y_min. That is the logic that works for a square, but does not work for an irregular polygon. At the moment I think the solution is to add an extra test in php to the SQL result, based on a calculated vector. IF CALC_VECTOR IS BETWEEN..

3030 Over a year ago

The user has to input an Azimuth bearing into the initial input, so I can easily test the results based on this. IF CALC_VECTOR > INPUT_AZIMUTH + 1 min OR < INPUT_AZIMUTH - 1 min THEN OMIT THE RESULT. I think this is the easiest solution. cheers.

3030 Over a year ago

Hi to answer your edit. I am not very skilled in using MYSQL. I am using an older version of MYSQL (released < 2 years ago), but which apparently supports the geometry modules. I am using MYSQL administrator GUI (freeware) and selected ISAM for the table.. I have created the table using both the gui interface and SQL code multiple times. I have imported the data from a CSV multiple times with different syntax (trial and error). Every time it is successful I seem to get the same result when trying to display the "coord" column (point(x y) column) in php, a bunch of computer code characters..

Collectives™ on Stack Overflow

SQL select rows where data matches multiple columns

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related