I don't know if i'm doing ok posting here, maybe this goes in a GIS
group, sorry if this is not the correct place.

I'm working with mysql GIS functions, and I need to make a query that
returns the closest point in a LINESTRING from another point.

sketch:

x
|
x
|
|
o-------+
|
|
|
x

LINESTRING is composed by the 'x' points, the line is drawn "|"
"+" is the point I have as input
"o" is the nearest point of LINESTRING from the "+" point
"o" is the result I want

MySQL gives functions like distance(), that returns the distance
between "+" and "o" but I don't know how to get the coordinates from
that nearest point "o"

    All MySQL Spatial Extensions really support is efficiently
returning the points within a bounding rectangle.  That's
supported for MyISAM tables.  Any geometric calculations beyond
that can be done in your code; there's no big advantage in doing
them in MySQL.

    The calculation of the distance between a point and a line is
well-described here:

   http://ozviz.wasp.uwa.edu.au/~pbourke/geometry/pointline/

You can compute that in MySQL if you like, perhaps in a HAVING
clause. But make sure you use some kind of bounding rectangle to
cut down the number of points tested, or you'll apply your
calculation to the entire table.

				John Nagle

JPerelli wrote:
> I don't know if i'm doing ok posting here, maybe this goes in a GIS
> group, sorry if this is not the correct place.
> 
> I'm working with mysql GIS functions, and I need to make a query that
> returns the closest point in a LINESTRING from another point.
> 
> sketch:
> 
> x
> |
> x
> |
> |
> o-------+
> |
> |
> |
> x
> 
> LINESTRING is composed by the 'x' points, the line is drawn "|"
> "+" is the point I have as input
> "o" is the nearest point of LINESTRING from the "+" point
> "o" is the result I want
> 
> MySQL gives functions like distance(), that returns the distance
> between "+" and "o" but I don't know how to get the coordinates from
> that nearest point "o"

JPerelli wrote:
> I don't know if i'm doing ok posting here, maybe this goes in a GIS
> group, sorry if this is not the correct place.
> 
> I'm working with mysql GIS functions, and I need to make a query that
> returns the closest point in a LINESTRING from another point.
> 
> sketch:
> 
> x
> |
> x
> |
> |
> o-------+
> |
> |
> |
> x
> 
> LINESTRING is composed by the 'x' points, the line is drawn "|"
> "+" is the point I have as input
> "o" is the nearest point of LINESTRING from the "+" point
> "o" is the result I want
> 
> MySQL gives functions like distance(), that returns the distance
> between "+" and "o" but I don't know how to get the coordinates from
> that nearest point "o"

What's your table layout (CREATE TABLE statement)?



-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Feb 17, 1:55=A0am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> JPerelli wrote:
> > I don't know if i'm doing ok posting here, maybe this goes in a GIS
> > group, sorry if this is not the correct place.
>
> > I'm working with mysql GIS functions, and I need to make a query that
> > returns the closest point in a LINESTRING from another point.
>
> > sketch:
>
> > x
> > |
> > x
> > |
> > |
> > o-------+
> > |
> > |
> > |
> > x
>
> > LINESTRING is composed by the 'x' points, the line is drawn "|"
> > "+" is the point I have as input
> > "o" is the nearest point of LINESTRING from the "+" point
> > "o" is the result I want
>
> > MySQL gives functions like distance(), that returns the distance
> > between "+" and "o" but I don't know how to get the coordinates from
> > that nearest point "o"
>
> What's your table layout (CREATE TABLE statement)?
>
> --
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

My table has some data about the line, and then a linestring
containing the points of a path

the create statement, i don't think that helps but is this
CREATE TABLE  `colectivos`.`Grecorrido` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nombre` varchar(20) NOT NULL,
  `zona_inicio` varchar(30) NOT NULL,
  `zona_fin` varchar(30) NOT NULL,
  `descripcion` text NOT NULL,
  `id_ramal` int(11) NOT NULL,
  `color_polilinea` varchar(15) NOT NULL,
  `camino` linestring NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=3DMyISAM AUTO_INCREMENT=3D43 DEFAULT CHARSET=3Dlatin1

On Feb 17, 1:25=A0am, John Nagle <na...@animats.com> wrote:
> =A0 =A0 All MySQL Spatial Extensions really support is efficiently
> returning the points within a bounding rectangle. =A0That's
> supported for MyISAM tables. =A0Any geometric calculations beyond
> that can be done in your code; there's no big advantage in doing
> them in MySQL.
>
> =A0 =A0 The calculation of the distance between a point and a line is
> well-described here:
>
> =A0 =A0http://ozviz.wasp.uwa.edu.au/~pbourke/geometry/pointline/
>
> You can compute that in MySQL if you like, perhaps in a HAVING
> clause. But make sure you use some kind of bounding rectangle to
> cut down the number of points tested, or you'll apply your
> calculation to the entire table.
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 John Nagl=
e
>
> JPerelli wrote:
> > I don't know if i'm doing ok posting here, maybe this goes in a GIS
> > group, sorry if this is not the correct place.
>
> > I'm working with mysql GIS functions, and I need to make a query that
> > returns the closest point in a LINESTRING from another point.
>
> > sketch:
>
> > x
> > |
> > x
> > |
> > |
> > o-------+
> > |
> > |
> > |
> > x
>
> > LINESTRING is composed by the 'x' points, the line is drawn "|"
> > "+" is the point I have as input
> > "o" is the nearest point of LINESTRING from the "+" point
> > "o" is the result I want
>
> > MySQL gives functions like distance(), that returns the distance
> > between "+" and "o" but I don't know how to get the coordinates from
> > that nearest point "o"
>
>

I tried to do it by myself, as the link you posted, but the query took
a lot of time (like 3 sec) compared to mysql's distance() function
(like 0.5 sec)

So maybe I do it myself, but after I make a smaller resultset,
filtering by distance, and some fields.

I thought that mysql found the nearest point and then calculated the
distance, so mysql had the coords of the point I needed, then, maybe
there was (or could be) a function that returns that point I need.

So, the thing here is that, if I filter by distance() first, and I
keep the closer results and later I calculate the nearest point, then
I'm making again some of the work that mysql did earlier. I don't like
that, so, that's why I'm searching for help.

I was looking for postgreSql and SQlite, they have GIS extensions too
(and I think they have more functions that mysql), but I really want
to do this with mysql.

JPerelli wrote:
> On Feb 17, 1:55 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> JPerelli wrote:
>>> I don't know if i'm doing ok posting here, maybe this goes in a GIS
>>> group, sorry if this is not the correct place.
>>> I'm working with mysql GIS functions, and I need to make a query that
>>> returns the closest point in a LINESTRING from another point.
>>> sketch:
>>> x
>>> |
>>> x
>>> |
>>> |
>>> o-------+
>>> |
>>> |
>>> |
>>> x
>>> LINESTRING is composed by the 'x' points, the line is drawn "|"
>>> "+" is the point I have as input
>>> "o" is the nearest point of LINESTRING from the "+" point
>>> "o" is the result I want
>>> MySQL gives functions like distance(), that returns the distance
>>> between "+" and "o" but I don't know how to get the coordinates from
>>> that nearest point "o"
>> What's your table layout (CREATE TABLE statement)?
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
> 
> My table has some data about the line, and then a linestring
> containing the points of a path
> 
> the create statement, i don't think that helps but is this
> CREATE TABLE  `colectivos`.`Grecorrido` (
>   `id` int(11) NOT NULL AUTO_INCREMENT,
>   `nombre` varchar(20) NOT NULL,
>   `zona_inicio` varchar(30) NOT NULL,
>   `zona_fin` varchar(30) NOT NULL,
>   `descripcion` text NOT NULL,
>   `id_ramal` int(11) NOT NULL,
>   `color_polilinea` varchar(15) NOT NULL,
>   `camino` linestring NOT NULL,
>   PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=43 DEFAULT CHARSET=latin1

Ok, now I understand better what you're trying to do.  Sometimes the 
code makes it more clear :)

Unfortunately, I don't know of any way to do it just in MySQL.  While 
you can get the distance, as you found out, AFAIK there aren't any 
functions to get the exact point.

So, let's take another tack on this.  Are you trying to search the 
database for the line which comes closest to a point?  Or just what are 
you trying to do.  Maybe we can find a different way.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Feb 17, 5:07=A0pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> JPerelli wrote:
> > On Feb 17, 1:55 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> JPerelli wrote:
> >>> I don't know if i'm doing ok posting here, maybe this goes in a GIS
> >>> group, sorry if this is not the correct place.
> >>> I'm working with mysql GIS functions, and I need to make a query that
> >>> returns the closest point in a LINESTRING from another point.
> >>> sketch:
> >>> x
> >>> |
> >>> x
> >>> |
> >>> |
> >>> o-------+
> >>> |
> >>> |
> >>> |
> >>> x
> >>> LINESTRING is composed by the 'x' points, the line is drawn "|"
> >>> "+" is the point I have as input
> >>> "o" is the nearest point of LINESTRING from the "+" point
> >>> "o" is the result I want
> >>> MySQL gives functions like distance(), that returns the distance
> >>> between "+" and "o" but I don't know how to get the coordinates from
> >>> that nearest point "o"
> >> What's your table layout (CREATE TABLE statement)?
>
> >> --
> >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> > My table has some data about the line, and then a linestring
> > containing the points of a path
>
> > the create statement, i don't think that helps but is this
> > CREATE TABLE =A0`colectivos`.`Grecorrido` (
> > =A0 `id` int(11) NOT NULL AUTO_INCREMENT,
> > =A0 `nombre` varchar(20) NOT NULL,
> > =A0 `zona_inicio` varchar(30) NOT NULL,
> > =A0 `zona_fin` varchar(30) NOT NULL,
> > =A0 `descripcion` text NOT NULL,
> > =A0 `id_ramal` int(11) NOT NULL,
> > =A0 `color_polilinea` varchar(15) NOT NULL,
> > =A0 `camino` linestring NOT NULL,
> > =A0 PRIMARY KEY (`id`)
> > ) ENGINE=3DMyISAM AUTO_INCREMENT=3D43 DEFAULT CHARSET=3Dlatin1
>
> Ok, now I understand better what you're trying to do. =A0Sometimes the
> code makes it more clear :)
>
> Unfortunately, I don't know of any way to do it just in MySQL. =A0While
> you can get the distance, as you found out, AFAIK there aren't any
> functions to get the exact point.
>
> So, let's take another tack on this. =A0Are you trying to search the
> database for the line which comes closest to a point? =A0Or just what are
> you trying to do. =A0Maybe we can find a different way.
>
> --
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Ok, let me explain then

I have many linestrings, they all go form the first point to the last
point in that way. This are paths, and they are one way only.

On the other hand I have a point, (another input) in the sketch is the
"+". Well, really there are two points, It's the same problem, but
easier with one point.

(you know were i'm going)

So I want to find all the paths that are at 100 meters or less (from
both points), and all that paths, have to take me from point A to B.

I only got "all the paths that are at 100 meters or less (from both
points)", and recently I made a stored procedure that returns only the
directed paths.

One problem was to make that stored procedure, I didn't want to make
it, but I can't do it in only one query... It uses like 6 temporary
tables, and I had to make something weird because there is no function
that return all points in a linestring (that was another thing...)

But now, and here is the real problem that started the thread, I want
to have not the entire path, but only the part that I use to go from A
to B.

sketch:

     :
     :
A....|
     |
     |
     v
     |
     |.....B
     :
     :
     :


A initial point
B final point
":" path I don't want
"|" path I want
".." 100 meters or less
"v" path direction

I don't think that this is a super-rare-weird query, it's really very
common if you are doing a transport system. Why is so difficult? Am I
taking a bad approach?

On Feb 19, 7:41=A0pm, JPerelli <jpere...@gmail.com> wrote:
> On Feb 17, 5:07=A0pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>
>
>
> > JPerelli wrote:
> > > On Feb 17, 1:55 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> > >> JPerelli wrote:
> > >>> I don't know if i'm doing ok posting here, maybe this goes in a GIS
> > >>> group, sorry if this is not the correct place.
> > >>> I'm working with mysql GIS functions, and I need to make a query th=
at
> > >>> returns the closest point in a LINESTRING from another point.
> > >>> sketch:
> > >>> x
> > >>> |
> > >>> x
> > >>> |
> > >>> |
> > >>> o-------+
> > >>> |
> > >>> |
> > >>> |
> > >>> x
> > >>> LINESTRING is composed by the 'x' points, the line is drawn "|"
> > >>> "+" is the point I have as input
> > >>> "o" is the nearest point of LINESTRING from the "+" point
> > >>> "o" is the result I want
> > >>> MySQL gives functions like distance(), that returns the distance
> > >>> between "+" and "o" but I don't know how to get the coordinates fro=
m
> > >>> that nearest point "o"
> > >> What's your table layout (CREATE TABLE statement)?
>
> > >> --
> > >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > >> Remove the "x" from my email address
> > >> Jerry Stuckle
> > >> JDS Computer Training Corp.
> > >> jstuck...@attglobal.net
> > >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> > > My table has some data about the line, and then a linestring
> > > containing the points of a path
>
> > > the create statement, i don't think that helps but is this
> > > CREATE TABLE =A0`colectivos`.`Grecorrido` (
> > > =A0 `id` int(11) NOT NULL AUTO_INCREMENT,
> > > =A0 `nombre` varchar(20) NOT NULL,
> > > =A0 `zona_inicio` varchar(30) NOT NULL,
> > > =A0 `zona_fin` varchar(30) NOT NULL,
> > > =A0 `descripcion` text NOT NULL,
> > > =A0 `id_ramal` int(11) NOT NULL,
> > > =A0 `color_polilinea` varchar(15) NOT NULL,
> > > =A0 `camino` linestring NOT NULL,
> > > =A0 PRIMARY KEY (`id`)
> > > ) ENGINE=3DMyISAM AUTO_INCREMENT=3D43 DEFAULT CHARSET=3Dlatin1
>
> > Ok, now I understand better what you're trying to do. =A0Sometimes the
> > code makes it more clear :)
>
> > Unfortunately, I don't know of any way to do it just in MySQL. =A0While
> > you can get the distance, as you found out, AFAIK there aren't any
> > functions to get the exact point.
>
> > So, let's take another tack on this. =A0Are you trying to search the
> > database for the line which comes closest to a point? =A0Or just what a=
re
> > you trying to do. =A0Maybe we can find a different way.
>
> > --
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > Remove the "x" from my email address
> > Jerry Stuckle
> > JDS Computer Training Corp.
> > jstuck...@attglobal.net
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> Ok, let me explain then
>
> I have many linestrings, they all go form the first point to the last
> point in that way. This are paths, and they are one way only.
>
> On the other hand I have a point, (another input) in the sketch is the
> "+". Well, really there are two points, It's the same problem, but
> easier with one point.
>
> (you know were i'm going)
>
> So I want to find all the paths that are at 100 meters or less (from
> both points), and all that paths, have to take me from point A to B.
>
> I only got "all the paths that are at 100 meters or less (from both
> points)", and recently I made a stored procedure that returns only the
> directed paths.
>
> One problem was to make that stored procedure, I didn't want to make
> it, but I can't do it in only one query... It uses like 6 temporary
> tables, and I had to make something weird because there is no function
> that return all points in a linestring (that was another thing...)
>
> But now, and here is the real problem that started the thread, I want
> to have not the entire path, but only the part that I use to go from A
> to B.
>
> sketch:
>
> =A0 =A0 =A0:
> =A0 =A0 =A0:
> A....|
> =A0 =A0 =A0|
> =A0 =A0 =A0|
> =A0 =A0 =A0v
> =A0 =A0 =A0|
> =A0 =A0 =A0|.....B
> =A0 =A0 =A0:
> =A0 =A0 =A0:
> =A0 =A0 =A0:
>
> A initial point
> B final point
> ":" path I don't want
> "|" path I want
> ".." 100 meters or less
> "v" path direction
>
> I don't think that this is a super-rare-weird query, it's really very
> common if you are doing a transport system. Why is so difficult? Am I
> taking a bad approach?

Now I'm thinking another way... there is a function called
Intersection

#  INTERSECTION(g1 geometry, g2 geometry) returns a Geometry that is
the set intersection of g1 and g2

Maybe if I can surround the path in a polygon and then take the
intersection between the polygon and the linestring, then I take the
slice of path that I need... but then the problem is to buid the
polygon

JPerelli wrote:
> On Feb 17, 5:07 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> JPerelli wrote:
>>> On Feb 17, 1:55 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>> JPerelli wrote:
>>>>> I don't know if i'm doing ok posting here, maybe this goes in a GIS
>>>>> group, sorry if this is not the correct place.
>>>>> I'm working with mysql GIS functions, and I need to make a query that
>>>>> returns the closest point in a LINESTRING from another point.
>>>>> sketch:
>>>>> x
>>>>> |
>>>>> x
>>>>> |
>>>>> |
>>>>> o-------+
>>>>> |
>>>>> |
>>>>> |
>>>>> x
>>>>> LINESTRING is composed by the 'x' points, the line is drawn "|"
>>>>> "+" is the point I have as input
>>>>> "o" is the nearest point of LINESTRING from the "+" point
>>>>> "o" is the result I want
>>>>> MySQL gives functions like distance(), that returns the distance
>>>>> between "+" and "o" but I don't know how to get the coordinates from
>>>>> that nearest point "o"
>>>> What's your table layout (CREATE TABLE statement)?
>>>> --
>>>> ==================
>>>> Remove the "x" from my email address
>>>> Jerry Stuckle
>>>> JDS Computer Training Corp.
>>>> jstuck...@attglobal.net
>>>> ==================
>>> My table has some data about the line, and then a linestring
>>> containing the points of a path
>>> the create statement, i don't think that helps but is this
>>> CREATE TABLE  `colectivos`.`Grecorrido` (
>>>   `id` int(11) NOT NULL AUTO_INCREMENT,
>>>   `nombre` varchar(20) NOT NULL,
>>>   `zona_inicio` varchar(30) NOT NULL,
>>>   `zona_fin` varchar(30) NOT NULL,
>>>   `descripcion` text NOT NULL,
>>>   `id_ramal` int(11) NOT NULL,
>>>   `color_polilinea` varchar(15) NOT NULL,
>>>   `camino` linestring NOT NULL,
>>>   PRIMARY KEY (`id`)
>>> ) ENGINE=MyISAM AUTO_INCREMENT=43 DEFAULT CHARSET=latin1
>> Ok, now I understand better what you're trying to do.  Sometimes the
>> code makes it more clear :)
>>
>> Unfortunately, I don't know of any way to do it just in MySQL.  While
>> you can get the distance, as you found out, AFAIK there aren't any
>> functions to get the exact point.
>>
>> So, let's take another tack on this.  Are you trying to search the
>> database for the line which comes closest to a point?  Or just what are
>> you trying to do.  Maybe we can find a different way.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
> 
> Ok, let me explain then
> 
> I have many linestrings, they all go form the first point to the last
> point in that way. This are paths, and they are one way only.
> 
> On the other hand I have a point, (another input) in the sketch is the
> "+". Well, really there are two points, It's the same problem, but
> easier with one point.
> 
> (you know were i'm going)
> 
> So I want to find all the paths that are at 100 meters or less (from
> both points), and all that paths, have to take me from point A to B.
> 
> I only got "all the paths that are at 100 meters or less (from both
> points)", and recently I made a stored procedure that returns only the
> directed paths.
> 
> One problem was to make that stored procedure, I didn't want to make
> it, but I can't do it in only one query... It uses like 6 temporary
> tables, and I had to make something weird because there is no function
> that return all points in a linestring (that was another thing...)
> 
> But now, and here is the real problem that started the thread, I want
> to have not the entire path, but only the part that I use to go from A
> to B.
> 
> sketch:
> 
>      :
>      :
> A....|
>      |
>      |
>      v
>      |
>      |.....B
>      :
>      :
>      :
> 
> 
> A initial point
> B final point
> ":" path I don't want
> "|" path I want
> ".." 100 meters or less
> "v" path direction
> 
> I don't think that this is a super-rare-weird query, it's really very
> common if you are doing a transport system. Why is so difficult? Am I
> taking a bad approach?

Well, I can see where it would be common in a transport system.  But 
that's a quite specialized field, and in the overall picture of database 
usage, it would be quite rare.

You could use intersects() and/or intersection() to limit the results 
returned, but as you indicated, building the polygon would be difficult 
(you need a circle).

I guess my suggestion would be a SP which creates a square or hexagon 
surrounding the circle you want and use that for initial selection. 
 From there, use general math to determine the minimum distance from 
your point to the line.

It's not going to be fast if you have a large table, but I think it's 
going to be the best you can get.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Feb 20, 3:24=A0am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> JPerelli wrote:
> > On Feb 17, 5:07 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> JPerelli wrote:
> >>> On Feb 17, 1:55 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >>>> JPerelli wrote:
> >>>>> I don't know if i'm doing ok posting here, maybe this goes in a GIS
> >>>>> group, sorry if this is not the correct place.
> >>>>> I'm working with mysql GIS functions, and I need to make a query th=
at
> >>>>> returns the closest point in a LINESTRING from another point.
> >>>>> sketch:
> >>>>> x
> >>>>> |
> >>>>> x
> >>>>> |
> >>>>> |
> >>>>> o-------+
> >>>>> |
> >>>>> |
> >>>>> |
> >>>>> x
> >>>>> LINESTRING is composed by the 'x' points, the line is drawn "|"
> >>>>> "+" is the point I have as input
> >>>>> "o" is the nearest point of LINESTRING from the "+" point
> >>>>> "o" is the result I want
> >>>>> MySQL gives functions like distance(), that returns the distance
> >>>>> between "+" and "o" but I don't know how to get the coordinates fro=
m
> >>>>> that nearest point "o"
> >>>> What's your table layout (CREATE TABLE statement)?
> >>>> --
> >>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >>>> Remove the "x" from my email address
> >>>> Jerry Stuckle
> >>>> JDS Computer Training Corp.
> >>>> jstuck...@attglobal.net
> >>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >>> My table has some data about the line, and then a linestring
> >>> containing the points of a path
> >>> the create statement, i don't think that helps but is this
> >>> CREATE TABLE =A0`colectivos`.`Grecorrido` (
> >>> =A0 `id` int(11) NOT NULL AUTO_INCREMENT,
> >>> =A0 `nombre` varchar(20) NOT NULL,
> >>> =A0 `zona_inicio` varchar(30) NOT NULL,
> >>> =A0 `zona_fin` varchar(30) NOT NULL,
> >>> =A0 `descripcion` text NOT NULL,
> >>> =A0 `id_ramal` int(11) NOT NULL,
> >>> =A0 `color_polilinea` varchar(15) NOT NULL,
> >>> =A0 `camino` linestring NOT NULL,
> >>> =A0 PRIMARY KEY (`id`)
> >>> ) ENGINE=3DMyISAM AUTO_INCREMENT=3D43 DEFAULT CHARSET=3Dlatin1
> >> Ok, now I understand better what you're trying to do. =A0Sometimes the
> >> code makes it more clear :)
>
> >> Unfortunately, I don't know of any way to do it just in MySQL. =A0Whil=
e
> >> you can get the distance, as you found out, AFAIK there aren't any
> >> functions to get the exact point.
>
> >> So, let's take another tack on this. =A0Are you trying to search the
> >> database for the line which comes closest to a point? =A0Or just what =
are
> >> you trying to do. =A0Maybe we can find a different way.
>
> >> --
> >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> > Ok, let me explain then
>
> > I have many linestrings, they all go form the first point to the last
> > point in that way. This are paths, and they are one way only.
>
> > On the other hand I have a point, (another input) in the sketch is the
> > "+". Well, really there are two points, It's the same problem, but
> > easier with one point.
>
> > (you know were i'm going)
>
> > So I want to find all the paths that are at 100 meters or less (from
> > both points), and all that paths, have to take me from point A to B.
>
> > I only got "all the paths that are at 100 meters or less (from both
> > points)", and recently I made a stored procedure that returns only the
> > directed paths.
>
> > One problem was to make that stored procedure, I didn't want to make
> > it, but I can't do it in only one query... It uses like 6 temporary
> > tables, and I had to make something weird because there is no function
> > that return all points in a linestring (that was another thing...)
>
> > But now, and here is the real problem that started the thread, I want
> > to have not the entire path, but only the part that I use to go from A
> > to B.
>
> > sketch:
>
> > =A0 =A0 =A0:
> > =A0 =A0 =A0:
> > A....|
> > =A0 =A0 =A0|
> > =A0 =A0 =A0|
> > =A0 =A0 =A0v
> > =A0 =A0 =A0|
> > =A0 =A0 =A0|.....B
> > =A0 =A0 =A0:
> > =A0 =A0 =A0:
> > =A0 =A0 =A0:
>
> > A initial point
> > B final point
> > ":" path I don't want
> > "|" path I want
> > ".." 100 meters or less
> > "v" path direction
>
> > I don't think that this is a super-rare-weird query, it's really very
> > common if you are doing a transport system. Why is so difficult? Am I
> > taking a bad approach?
>
> Well, I can see where it would be common in a transport system. =A0But
> that's a quite specialized field, and in the overall picture of database
> usage, it would be quite rare.
>
> You could use intersects() and/or intersection() to limit the results
> returned, but as you indicated, building the polygon would be difficult
> (you need a circle).
>
> I guess my suggestion would be a SP which creates a square or hexagon
> surrounding the circle you want and use that for initial selection.
> =A0From there, use general math to determine the minimum distance from
> your point to the line.
>
> It's not going to be fast if you have a large table, but I think it's
> going to be the best you can get.
>
> --
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Well, thank you, at least I know I'm not doing so crazy things to find
a solution :)