COMPGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### GIS query

• Email
• Follow

```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"
```
 0
Reply jperelli (11) 2/17/2010 12:48:46 AM

See related articles to this posting

```    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"
```
 0

```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
==================
```
 0

```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
```
 0

```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.
```
 0

```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
==================
```
 0

```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
```
 0

```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

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
```
 0

```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

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

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
==================
```
 0

```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 :)
```
 0

9 Replies
365 Views

Similar Articles

12/4/2013 11:00:58 AM
page loaded in 2190964 ms -1

Similar Artilces:

query
hi all, Simple problem: I have a entry with an ID. What is the easiets way to get the row with an ID that is left (smaller) and right (higher) Example ID | filename 5 test.jpg 6 low.jpg 8 nice.jpg 9 cloud.jpg 10 rain.jpg 13 high.jpg I have nice.jpd (ID = 8) and want to get the 'right' ID (low.jpg) and the 'right' ID (cloud.jpg) IS there an easy way kind regards Stijn "Andy Hassall" <andy@andyh.co.uk> wrote in message news:is0h40tp7qo574h1ngip7g5qu54uohro4r@4ax.com... > On Fri, 05 Mar 2004 13:29:35 GMT, "Stijn Goris" <mepis

if in a query
Hello there. I've a query with returns 2 values: a code and a value. What I want is to have 3 columns: col1 is the code, col2 is the value if the code is=1 and col3 is the value if the code is=2. Also I'd like to have the total of col2 and col3. How to do so ? Thanks for helping. Bob On 21 Mar, 17:01, "Bob Bedford" <b...@bedford.com> wrote: > Hello there. > > I've a query with returns 2 values: a code and a value. > > What I want is to have 3 columns: col1 is the code, col2 is the value if the > code is=1 and col3 is the value if the code is=2. > > Also I'd like to have the total of col2 and col3. > > How to do so ? > > Thanks for helping. > > Bob What if the code is neither 1 nor 3? On Mar 21, 5:01 pm, "Bob Bedford" <b...@bedford.com> wrote: > Hello there. > > I've a query with returns 2 values: a code and a value. > > What I want is to have 3 columns: col1 is the code, col2 is the value if the > code is=1 and col3 is the value if the code is=2. > > Also I'd like to have the total of col2 and col3. > > How to do so ? > > Thanks

Query...
Why are flatfish flat? -- rapskat - 19:03:04 up 4 days, 20:50, 5 users, load average: 0.24, 0.39, 0.72 Clothes make the man. Naked people have little or no influence on society. -- Mark Twain begin trojan.vbs It was on Fri, 04 Mar 2005 19:03:26 -0500, that rapskat wrote: > Why are flatfish flat? 'Cos they get stamped on. -- M\$ Licensing:- http://www.cheshirewebmill.co.uk/microsoft/ rapskat wrote: > Why does Ralph reject my advances ? On Fri, 04 Mar 2005 19:03:26 -0500, rapskat wrote: > Why are flatfish flat? Because everyone knows "thin is in" and &

QUERY
Gene, In case you ever need a query like we discussed again. The following will work. The query below will sets the Bills.LetterStatus to C ( Canceled ) for each account that has a Letters.Letter=2 ordered UPDATE Bills SET Bills.LetterStatus = 'C' WHERE Bills.Bill in (SELECT Letters.Bill FROM Letters WHERE Letters.Letter = 2) -- Thanks, Phil Chapman Fax (413) 556-5674

IF and OR in a query
Having a little trouble returning the result I'm looking for in a query and thought I'd ask a question here. I have an input screen with a pull down menu to capture vacation activity within a given month as well as capture overlap onto another month. For example, if you're taking vacation between June 25 and July 4, the input would be 'junjul' if the days off were July 4 through 10, the input would be 'jul' and if the days off happened to be July 25 through August 5, the input would fall under 'julaug' with the idea being that the query captures any vacation for the month of July but also sends back info for any overlapping month vacation. Also, if you did a query for the month of June or August, that overlapping vacation would show as well. I did this very effectively in Access once and am trying duplicate it with PHP. So a question becomes, IF jul (is selected in the query for the month of July) THEN return any data for junjul AND julaug if they were used on the input form and have that data returned under a July query. Any thoughts? On Sun, 05 Jun 2005 04:11:25 -0700, Chris wrote: > So a question becomes, IF jul (is selected

Tomcat5.5 query
Hi,I have installed tomcat5.5.In the tomcat docs it is recommended that i install Ant build tool aswell as Concurrent Version System (CVS) too.Can someone tell me, how much neccesary are these to install and use?How it can help me for developing my application? ruds wrote:> I have installed tomcat5.5.> In the tomcat docs it is recommended that i install Ant build tool as> well as Concurrent Version System (CVS) too.> Can someone tell me, how much neccesary are these to install and use?> How it can help me for developing my application?Not necessary - you can run without.But I wo

font query
Several years ago I created a load of graphics, such as these: http://congokid.com/graphics/gallery.gif http://congokid.com/graphics/thai.gif http://congokid.com/graphics/fishchip.gif Now I'd like to create some more but I the font I used, which I presume came with Windows 95, isn't available on my current machine, bought in 2003 with Windows XP. I've given away the old computer and disks. I wonder if anyone recognises the font. I have a feeling it's one of the Franklin Gothic family, but can't recreate it from those I currently have. They're either not bold, too

Query on explode()
); -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Ikciu | gg: 718845 | yahoo: ikciu_irsa | www: www.e-irsa.pl 2be || !2be \$this => mysql_query(); Try this one, this will work : --------------------------------------------------- \$test = explode('-','one-two-three-four-five'); \$view = new Array(); foreach(\$test as \$boo => \$foo){ \$view[\$foo] = \$foo; // typing error, do it like this } print_r(\$view); ------------------------------------------------------ For php/ajax/javascript tutorials and tips, visit me on my blog at http://www.nurazije.co.nr Hmm NurAzije <nurazije@gmail.com> wrote: > Try this one, this will work : > --------------------------------------------------- > \$test = explode('-','one-two-three-four-five'); > \$view = new Array(); > foreach(\$test as \$boo => \$foo){ > \$view[\$foo] = \$foo; // typing error, do it like this > } > > print_r(\$view); still too much of lines ... try array_combine -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Ikciu | gg: 718845 | yahoo: ikciu_irsa | www: www.e-irsa.pl 2be || !2be \$this => mysql_query(); ..:[ ikciu ]:. wrote: > Hmm NurAzije <nurazije@gmail.com>

mysql_query
I have a SQL Script with about 5000 SQL Commands. How can I send it to SQL Server at once? I see mysql_query() fails on first semicolon (;) who delimits the SQL Commands. Another question: I send to a MySQL server via fast Internet connection, about 500 INSERT commands one by one via mysql_query(). It takes much time. Why? I suspect that, mysql_query() after send the query to server, waits for a "OK" response. If it's true, how can I send and send and send SQL commands and MySQL server reply "OK" after send all these things? (or no wait at all?) First of all, you'd better ask in comp.databases.mysql You can insert more than one row with an insert command: INSERT INTO tblname(...) VALUES(...), (...), (...); Depending on the engine used, you could send it without being interested in the result: see http://dev.mysql.com/doc/refman/4.1/en/insert-delayed.html Best regards Chameleon wrote: > I have a SQL Script with about 5000 SQL Commands. > > How can I send it to SQL Server at once? > > I see mysql_query() fails on first semicolon (;) who delimits the SQL > Commands. > > > Another question

QUERY #2
I need to run a Query that returns Products wirh a Sale Date Older Than 20 Days and doesn't have a Sale Date of earlier than 20 Days, Any Suggestions. I hope this clearer than my last post. Thnaks DS DS wrote: > I need to run a Query that returns Products wirh a Sale Date Older Than > 20 Days and doesn't have a Sale Date of earlier than 20 Days, Any > Suggestions. I hope this clearer than my last post. > Thnaks > DS -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Not sure what you mean, but... SELECT * FROM Sales WHERE SalesDate < Date() - 20 Date() - 20...: > I've seen this in the MS Access Cookbook, published by O'Reilly. (I > recommend this book highly!) I'll dig it out and post in an hour or > so... > Thanks, I'm going crazy here! DS MGFoster wrote: > DS wrote: > >> I need to run a Query that returns Products wirh a Sale Date Older >> Than 20 Days and doesn't have a Sale Date of earlier than 20 Days, Any >> Suggestions. I hope this clearer than my last post. >> Thnaks >> DS > > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Not sure what