Link to home
Start Free TrialLog in
Avatar of Gary
GaryFlag for Ireland

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)
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

What are you trying to do (in plain language)?  It may be easier to skip the MySQL Geometry and do it differently.
http://dev.mysql.com/doc/refman/5.1/en/geometry-property-functions.html
Avatar of Gary

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
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
Avatar of Gary

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.
Avatar of Gary

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?
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.
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

Open in new window

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.
Avatar of Gary

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.
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'

Open in new window

Avatar of Gary

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.
being revised - sorry.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
;

Open in new window

Avatar of Gary

ASKER

So the trick was to use CONCAT, never even thought of it.

Thanks.
:) no problem, sorry about that misunderstanding!
Avatar of Gary

ASKER

No problem, thanks