[postgis-users] Postgis - Line to Point distance

Fri Jul 10 07:23:38 PDT 2009

Anyway, many thanks Pavel. You really helped me. I was looking at the
functions you used in the past, but it did not come to me to use them for
this problem.

2009/7/10 Pavel Iacovlev <iacovlev.pavel at gmail.com>

> Yes they are in "reverse order", there is no standard for lat or lon
> should be first (as I know), blame wkb/wkt :)
>
> 2009/7/10 Peter Kukuča <pjr.kukuca at gmail.com>:
> > Hi,
> >
> > Thank you a million times Pavel. I tried it out and it really works and
> > gives the distance from the line. It is absolutely great. I didn't think
> I
> > would have it running ever.
> >
> > But, now I have another problem. Look at these two select statements:
> >
> > select ST_distance_spheroid(pointfromtext('POINT(60 0)', 4326),
> > ST_line_interpolate_point(linestringfromtext('LINESTRING(0 0,80 0)',
> 4326),
> > ST_line_locate_point(linestringfromtext('LINESTRING(0 0,80 0)', 4326),
> > pointfromtext('POINT(60 1)', 4326))),
> > 'SPHEROID["GRS_1980",6378137,298.257222101]');
> >
> > returns the same distance (110574.38861168) as
> >
> > select ST_distance_spheroid(pointfromtext('POINT(0 1)', 4326),
> > ST_line_interpolate_point(linestringfromtext('LINESTRING(0 0,80 0)',
> 4326),
> > ST_line_locate_point(linestringfromtext('LINESTRING(0 0,80 0)', 4326),
> > pointfromtext('POINT(0 1)', 4326))),
> > 'SPHEROID["GRS_1980",6378137,298.257222101]');
> >
> > What is done in the example is a calculation of the distance represented
> by
> > 1 degree of longitude at two different latitudes. While the distance at
> zero
> > latitude is correct, the distance at 60° latitude should be exactly half
> > that. The funny part is, that I get the same result with simple point to
> > point calculations. And the absolutely best part is, when I swap all
> > latitudes and longitudes, it works. Now the only reasonable explanation
> is,
> > that all of the functions take longitude as their first parameter and
> > latitude as the second, but isn't this the other way around?
> >
> > 2009/7/10 Pavel Iacovlev <iacovlev.pavel at gmail.com>
> >>
> >> ST_line_locate_point returns the location (float) of the closest point
> >> on a line to a specified point, read the postgis manual it's all
> >> written there. After this you use ST_line_interpolate_point to
> >> transform that location to a point geometry.
> >>
> >> 2009/7/10 Peter Kukuča <pjr.kukuca at gmail.com>:
> >> > Ok, I will be more precise.
> >> >
> >> > A line in postgis is represented by a sequence of points (let's call
> >> > them
> >> > A,B,C,D...) connected with straight lines, right?
> >> >
> >> > Now, for me, the distance between a point (X) and a line in general is
> >> > the
> >> > smallest distance between any point on the line and the point X. But,
> by
> >> > any
> >> > point on the line, I do not mean any of the A,B,C,D... points, but
> also
> >> > any
> >> > point on the straight lines between A and B, B and C, C and D....
> >> >
> >> > So, the function you suggested - ST_line_locate_point(line, point),
> does
> >> > it
> >> > give me one of the A,B,C,D... points or does return really the closest
> >> > point, even if it has to find it somewhere on the connecting lines?
> This
> >> > is
> >> > crucial. If this can really find the closest point, then the rest is
> >> > great,
> >> > but I doubt that.
> >> >
> >> > 2009/7/10 Pavel Iacovlev <iacovlev.pavel at gmail.com>
> >> >>
> >> >> The length of the line, you can't calculate a distance between a
> point
> >> >> and a line in general. You can calculate a distance between a point
> >> >> and a point on a line.
> >> >>
> >> >> ST_distance_spheroid(point, ST_line_interpolate_point(line,
> >> >> ST_line_locate_point(line, point)),
> >> >> 'SPHEROID["GRS_1980",6378137,298.257222101]')
> >> >>
> >> >> ST_line_locate_point(line, point) - a point on the line that is most
> >> >> close to your initial point
> >> >>
> >> >> ST_line_interpolate_point - get that point as a point geometry
> >> >>
> >> >> ST_distance_spheroid - calculate the length between 2 points
> >> >>
> >> >> 2009/7/10 Peter Kukuča <pjr.kukuca at gmail.com>:
> >> >> > How is your query calculating the distance between a point and a
> >> >> > line?
> >> >> > Are
> >> >> > you not just calculating the length of the line?
> >> >> >
> >> >> > 2009/7/10 Pavel Iacovlev <iacovlev.pavel at gmail.com>
> >> >> >>
> >> >> >> Works on lines for me:
> >> >> >>
> >> >> >> QUERY:
> >> >> >> select ST_length_spheroid(st_transform(the_geom, 4326),
> >> >> >> 'SPHEROID["GRS_1980",6378137,298.257222101]') as ellps_distance,
> >> >> >> st_distance(the_geom) as normal_distance from
> fondcart_layers.roads
> >> >> >> limit 1
> >> >> >>
> >> >> >> RESULT:
> >> >> >> ellps_distance: 9214.65310341668
> >> >> >> normal_distance: 9214.10811677981
> >> >> >>
> >> >> >> 2009/7/10 Peter Kukuča <pjr.kukuca at gmail.com>:
> >> >> >> > ST_Length_spheroid is perfectly great, but it does not take a
> line
> >> >> >> > as
> >> >> >> > an
> >> >> >> > input :-(
> >> >> >> >
> >> >> >> > 2009/7/10 Pavel Iacovlev <iacovlev.pavel at gmail.com>
> >> >> >> >>
> >> >> >> >> And for geometry length use
> ST_length_spheroid(geometry,spheroid)
> >> >> >> >>
> >> >> >> >> On Fri, Jul 10, 2009 at 11:48 AM, Pavel
> >> >> >> >> Iacovlev<iacovlev.pavel at gmail.com> wrote:
> >> >> >> >> > you can take the middle of the line,
> >> >> >> >> > st_line_interpolate_point(the_geom,
> >> >> >> >> > 0.5)
> >> >> >> >> >
> >> >> >> >> > 2009/7/10 Peter Kukuča <pjr.kukuca at gmail.com>:
> >> >> >> >> >> Hi Pedro,
> >> >> >> >> >>
> >> >> >> >> >> thank you for your reply.
> >> >> >> >> >>
> >> >> >> >> >> I cannot use pointn, the line can be up to several hundred
> >> >> >> >> >> kilometers
> >> >> >> >> >> long
> >> >> >> >> >> and consits of up to 800 points, so this would be too rough.
> >> >> >> >> >>
> >> >> >> >> >> Regardinf the decond suggestion, that is exactly what I am
> >> >> >> >> >> doing
> >> >> >> >> >> now,
> >> >> >> >> >> but I
> >> >> >> >> >> don't know, which projection (srid) to transform the wgs84
> >> >> >> >> >> data
> >> >> >> >> >> into.
> >> >> >> >> >> It
> >> >> >> >> >> should work on all of the earth's surface, at least in all
> of
> >> >> >> >> >> europe
> >> >> >> >> >> for a
> >> >> >> >> >> start. And from what I found, the projections are usually
> tied
> >> >> >> >> >> to
> >> >> >> >> >> a
> >> >> >> >> >> small
> >> >> >> >> >> territory. I was thinking about mercator projection, but
> there
> >> >> >> >> >> is
> >> >> >> >> >> a
> >> >> >> >> >> zillion
> >> >> >> >> >> of them in postgis. And I found a post, that the one with
> srid
> >> >> >> >> >> 900913
> >> >> >> >> >> is
> >> >> >> >> >> working great, but I don't have that one in my postgis.
> >> >> >> >> >>
> >> >> >> >> >> 2009/7/10 Pedro Doria Meunier <pdoria at netmadeira.com>
> >> >> >> >> >>>
> >> >> >> >> >>> -----BEGIN PGP SIGNED MESSAGE-----
> >> >> >> >> >>> Hash: SHA1
> >> >> >> >> >>>
> >> >> >> >> >>> Hi Peter
> >> >> >> >> >>>
> >> >> >> >> >>> You could use the pointn(geometry, n) for the first point
> of
> >> >> >> >> >>> the
> >> >> >> >> >>> linestring as a first approach.
> >> >> >> >> >>> As far as meters are concerned here's a little example of
> >> >> >> >> >>> transforming
> >> >> >> >> >>> the geometry to the desired projected system:
> >> >> >> >> >>>
> >> >> >> >> >>> select distance(transform(u.coordinates,srid),
> >> >> >> >> >>> transform(p.geometry,srid)) AS thedistance
> >> >> >> >> >>>
> >> >> >> >> >>> HTH,
> >> >> >> >> >>>
> >> >> >> >> >>> Pedro Doria Meunier
> >> >> >> >> >>> GSM: +351 96 17 20 188
> >> >> >> >> >>> Skype: pdoriam
> >> >> >> >> >>>
> >> >> >> >> >>>
> >> >> >> >> >>>
> >> >> >> >> >>>
> >> >> >> >> >>> Peter Kukuča wrote:
> >> >> >> >> >>> > Dear sir,
> >> >> >> >> >>> >
> >> >> >> >> >>> > I am not sure if I am writing to the correct email
> address.
> >> >> >> >> >>> > I
> >> >> >> >> >>> > have
> >> >> >> >> >>> > found a post on the postgis-users formu from *Rich Gibson
> >> >> >> >> >>> > *and
> >> >> >> >> >>> > this
> >> >> >> >> >>> > address was next to it.
> >> >> >> >> >>> >
> >> >> >> >> >>> > I am using postgis for a while now and it is great. It
> >> >> >> >> >>> > solved
> >> >> >> >> >>> > a
> >> >> >> >> >>> > lot
> >> >> >> >> >>> > of problems for me.
> >> >> >> >> >>> >
> >> >> >> >> >>> > However, I am now facing a problem I cannot solve. I
> >> >> >> >> >>> > searched
> >> >> >> >> >>> > for
> >> >> >> >> >>> > almost two days now, but I still cannot find an answer.
> >> >> >> >> >>> > Here
> >> >> >> >> >>> > is
> >> >> >> >> >>> > my
> >> >> >> >> >>> > problem:
> >> >> >> >> >>> >
> >> >> >> >> >>> > I have a database of linestrings in wgs84 projection and
> >> >> >> >> >>> > points
> >> >> >> >> >>> > in
> >> >> >> >> >>> > wgs84 projection. I would like to determine the distance
> >> >> >> >> >>> > between
> >> >> >> >> >>> > a
> >> >> >> >> >>> > line and a point. The ST_distance function does this very
> >> >> >> >> >>> > well,
> >> >> >> >> >>> > but
> >> >> >> >> >>> > it does not take the wgs84 projection into account and
> >> >> >> >> >>> > also,
> >> >> >> >> >>> > the
> >> >> >> >> >>> > result is in degrees. On the other hand, the
> >> >> >> >> >>> > ST_Distance_Spheroid
> >> >> >> >> >>> > does take the wgs84 into accound and does give the result
> >> >> >> >> >>> > in
> >> >> >> >> >>> > meters,
> >> >> >> >> >>> > but it does not accept a linestring as an input
> parameter.
> >> >> >> >> >>> >
> >> >> >> >> >>> > Is there any way i can the distance between a line and a
> >> >> >> >> >>> > point
> >> >> >> >> >>> > in
> >> >> >> >> >>> > meters from the wgs84 projected input? I do not need grat
> >> >> >> >> >>> > accuracy.
> >> >> >> >> >>> > +-5 meters is still good enough. Thank you for your tips.
> >> >> >> >> >>> >
> >> >> >> >> >>> > --
> >> >> >> >> >>> > S pozdravom (regards)
> >> >> >> >> >>> > Ing. Peter Kukuča
> >> >> >> >> >>> >
> >> >> >> >> >>> >
> >> >> >> >> >>> >
> >> >> >> >> >>> >
> >> >> >> >> >>> >
> >> >> >> >> >>> >
> ----------------------------------------------------------------------
> >> >> >> >> >>> >
> >> >> >> >> >>> > _______________________________________________
> >> >> >> >> >>> > postgis-users mailing list
> >> >> >> >> >>> > postgis-users at postgis.refractions.net
> >> >> >> >> >>> >
> >> >> >> >> >>> >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >> >> >> >>> -----BEGIN PGP SIGNATURE-----
> >> >> >> >> >>> Version: GnuPG v1.4.7 (GNU/Linux)
> >> >> >> >> >>> Comment: Using GnuPG with Fedora -
> http://enigmail.mozdev.org
> >> >> >> >> >>>
> >> >> >> >> >>>
> >> >> >> >> >>>
> iD8DBQFKVv052FH5GXCfxAsRAqS1AJ0dfphFX3wrMuo+FdXVRTgmUzHRUACfUmvH
> >> >> >> >> >>> 146yGHfxoVmlnt9a91rpWmE=
> >> >> >> >> >>> =G70f
> >> >> >> >> >>> -----END PGP SIGNATURE-----
> >> >> >> >> >>>
> >> >> >> >> >>> _______________________________________________
> >> >> >> >> >>> postgis-users mailing list
> >> >> >> >> >>> postgis-users at postgis.refractions.net
> >> >> >> >> >>>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >> --
> >> >> >> >> >> S pozdravom (regards)
> >> >> >> >> >> Ing. Peter Kukuča
> >> >> >> >> >>
> >> >> >> >> >> _______________________________________________
> >> >> >> >> >> postgis-users mailing list
> >> >> >> >> >> postgis-users at postgis.refractions.net
> >> >> >> >> >>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> > --
> >> >> >> >> > http://iap.md, The future is open
> >> >> >> >> >
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> http://iap.md, The future is open
> >> >> >> >> _______________________________________________
> >> >> >> >> postgis-users mailing list
> >> >> >> >> postgis-users at postgis.refractions.net
> >> >> >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >> > --
> >> >> >> > S pozdravom (regards)
> >> >> >> > Ing. Peter Kukuča
> >> >> >> >
> >> >> >> > _______________________________________________
> >> >> >> > postgis-users mailing list
> >> >> >> > postgis-users at postgis.refractions.net
> >> >> >> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> http://iap.md, The future is open
> >> >> >> _______________________________________________
> >> >> >> postgis-users mailing list
> >> >> >> postgis-users at postgis.refractions.net
> >> >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >> >
> >> >> >
> >> >> >
> >> >> > --
> >> >> > S pozdravom (regards)
> >> >> > Ing. Peter Kukuča
> >> >> >
> >> >> > _______________________________________________
> >> >> > postgis-users mailing list
> >> >> > postgis-users at postgis.refractions.net
> >> >> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >> >> --
> >> >> http://iap.md, The future is open
> >> >> _______________________________________________
> >> >> postgis-users mailing list
> >> >> postgis-users at postgis.refractions.net
> >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >
> >> >
> >> >
> >> > --
> >> > S pozdravom (regards)
> >> > Ing. Peter Kukuča
> >> >
> >> > _______________________________________________
> >> > postgis-users mailing list
> >> > postgis-users at postgis.refractions.net
> >> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >
> >> >
> >>
> >>
> >>
> >> --
> >> http://iap.md, The future is open
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> >
> > --
> > S pozdravom (regards)
> > Ing. Peter Kukuča
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
>
>
>
> --
> http://iap.md, The future is open
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

-- 
S pozdravom (regards)
Ing. Peter Kukuča
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090710/0a5dcdf0/attachment.html>