Gary
asked on
MySQL Linestring
How would I write the sql below so the calculations are performed, at the moment they are in the string as plain text
SELECT Region from table1 WHERE MBRContains(GeomFromText(
'LineString(
(22.30260+ 20 / ( 111.1 / cos(22.30260))) (22.30260+ 20 / 111.1)
,
(22.30260- 20 / ( 111.1 / cos(22.30260))) (114.19185- 20 / 111.1))
'), latlng)
SELECT Region from table1 WHERE MBRContains(GeomFromText(
'LineString(
(22.30260+ 20 / ( 111.1 / cos(22.30260))) (22.30260+ 20 / 111.1)
,
(22.30260- 20 / ( 111.1 / cos(22.30260))) (114.19185- 20 / 111.1))
'), latlng)
ASKER
Actually the code is a bit wrong should be like
select Latitude, Longitude, ....
(SELECT Region from table1 WHERE MBRContains(GeomFromText(
'LineString(
(Latitude+ 20 / ( 111.1 / cos(Latitude))) (Latitude+ 20 / 111.1)
,
(Latitude- 20 / ( 111.1 / cos(Latitude))) (Longitude- 20 / 111.1))
'), latlng))
from table1
This is a nested select and I want to pull the Latitude and Longitude into this select
At the moment I run a separate query for the lat/lon, perform the above calculations and insert the results directly into the sql.
latlng is a Spatial indexed GPS Point column.
This is taking a GPS Point and finding all matching GPS Points with a set distance
select Latitude, Longitude, ....
(SELECT Region from table1 WHERE MBRContains(GeomFromText(
'LineString(
(Latitude+ 20 / ( 111.1 / cos(Latitude))) (Latitude+ 20 / 111.1)
,
(Latitude- 20 / ( 111.1 / cos(Latitude))) (Longitude- 20 / 111.1))
'), latlng))
from table1
This is a nested select and I want to pull the Latitude and Longitude into this select
At the moment I run a separate query for the lat/lon, perform the above calculations and insert the results directly into the sql.
latlng is a Spatial indexed GPS Point column.
This is taking a GPS Point and finding all matching GPS Points with a set distance
Have a look at this article. It should be updated to use MySQLi or PDO, but the principle of computing distances and finding geographically close points is still valid.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_4276-What-is-near-me-Proximity-calculations-using-PHP-and-MySQL.html
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_4276-What-is-near-me-Proximity-calculations-using-PHP-and-MySQL.html
ASKER
I don't need to know how to do the calculations, my code already does it correctly and quickly.
I want to know how to do the calculations within the sql using the parent select's values.
I want to know how to do the calculations within the sql using the parent select's values.
ASKER
I read through your topic and it doesn't make sense to me.
You are using an offset to get a temporary table of possible matches then you run an update against this with the actual distance, then you run a select against it for the real matches
There are easier ways to do this.
My table has nearly 7 million rows - how would your script hold up against it?
You are using an offset to get a temporary table of possible matches then you run an update against this with the actual distance, then you run a select against it for the real matches
There are easier ways to do this.
My table has nearly 7 million rows - how would your script hold up against it?
I'm not at all familiar with those functions. Will the subquery (lines 5 to 10) produce a single value? i.e. just one region. If it produces more than one row then it can't be used that way.
If your question is about how to pass the lat/lon values from the outer table into the function then perhaps "Q2" below might be helpful - basically it uses 2 aliases againt against table1 so that you can differentiate them and then pass the wanted values into what would become a correlated subquery - and I've tossed in the limit 1 too.
If your question is about how to pass the lat/lon values from the outer table into the function then perhaps "Q2" below might be helpful - basically it uses 2 aliases againt against table1 so that you can differentiate them and then pass the wanted values into what would become a correlated subquery - and I've tossed in the limit 1 too.
SELECT Latitude
, Longitude
,....
, (
SELECT Region
FROM table1
WHERE MBRContains(GeomFromText('LineString(
(Latitude+ 20 / ( 111.1 / cos(Latitude))) (Latitude+ 20 / 111.1)
, (Latitude- 20 / ( 111.1 / cos(Latitude))) (Longitude- 20 / 111.1))
'), latlng)
)
FROM table1
-- Q2
SELECT Latitude
, Longitude
,....
, (
SELECT Region
FROM table1 AS T2
WHERE MBRContains(GeomFromText('LineString(
(T1.Latitude+ 20 / ( 111.1 / cos(T1.Latitude))) (T1.Latitude+ 20 / 111.1)
, (T1.Latitude- 20 / ( 111.1 / cos(T1.Latitude))) (T1.Longitude- 20 / 111.1))
'), T1.latlng) -- ??
LIMIT 1
)
FROM table1 AS T1
I guess if that subquery does return more than 1 region you might be able to use group_concat (? maybe, might not be desirable output or performance) - or you would need to move that subquery into a join somehow.
ASKER
It's nothing to do with aliases.
The LineString is expecting a 4 values not formulas.
I want to know if I can rejig it to execute the formulas, I was playing around with running the calculations on the Lat/Lon directly as I select them.
The LineString is expecting a 4 values not formulas.
I want to know if I can rejig it to execute the formulas, I was playing around with running the calculations on the Lat/Lon directly as I select them.
select
mysteryfunction(rejig.p1,rejig.p2) as do_it_upside_down
from (
select
(T1.Latitude+ 20 / ( 111.1 / cos(T1.Latitude))) (T1.Latitude+ 20 / 111.1) as p1
, (T1.Latitude- 20 / ( 111.1 / cos(T1.Latitude))) (T1.Longitude- 20 / 111.1)) as p2
, 'just do the calculations' as here
from table1 as T1
) as rejig
where 'aliases are irrelevant' = 'apparently'
ASKER
You obviously have no idea what the built in MySQL functions are doing so why are you making snide comments.
>>"I'm not at all familiar with those functions"
I believe I stated that quite clearly.
It's true, I have not used every function provided by MySQL, and didn't even know they were inbuilt, now I do. Not that I am likely to use them as I don't deal in geospatial topics much, but at least I might recognize them in future.
If you want a method for calculation of the 4 values, then do them in a nested subquery, then pass them by the field aliases that you assign to them.
"snide"?
where 'aliases are irrelevant' = 'apparently' (= always false)
which I thought you might appreciate.
I believe I stated that quite clearly.
It's true, I have not used every function provided by MySQL, and didn't even know they were inbuilt, now I do. Not that I am likely to use them as I don't deal in geospatial topics much, but at least I might recognize them in future.
If you want a method for calculation of the 4 values, then do them in a nested subquery, then pass them by the field aliases that you assign to them.
"snide"?
where 'aliases are irrelevant' = 'apparently' (= always false)
which I thought you might appreciate.
being revised - sorry.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
mmm, seems you may need 2 WKT's for MBRContains, and in looking at those calculations I'm not sure of the precedence that should apply to the + or - 20. Anyway at least with the following I can get MBRContains to return a zero, so I suspect you need to apply GeomFromText() to `latlng` also
/* not sure what precedence should apply to +- 20 e.g.
select ((114.19185 - 20) / 111.1), (114.19185 - 20 / 111.1)
;
/* ?? change precedence of the +-20 */
select
GeomFromText(
concat(
'LineString('
, ((22.30260 + 20) / ( 111.1 / cos(22.30260)))
, ' '
, ((22.30260 + 20) / 111.1)
, ', '
, ((22.30260 - 20) / ( 111.1 / cos(22.30260)))
, ' '
, ((114.19185 - 20) / 111.1)
, ')'
)
) as GFT_WKT
;
/* MBRContains requires the second parameter as geometry too */
select
MBRContains(
GeomFromText(
concat(
'LineString('
, ((22.30260 + 20) / ( 111.1 / cos(22.30260)))
, ' '
, ((22.30260 + 20) / 111.1)
, ', '
, ((22.30260 - 20) / ( 111.1 / cos(22.30260)))
, ' '
, ((114.19185 - 20) / 111.1)
, ')'
)
), GeomFromText('Point(0.847811431 0.847811431)')
) as MBR_GFT_WKT
;
ASKER
So the trick was to use CONCAT, never even thought of it.
Thanks.
Thanks.
:) no problem, sorry about that misunderstanding!
ASKER
No problem, thanks
http://dev.mysql.com/doc/refman/5.1/en/geometry-property-functions.html