[postgis-users] Postgis - Line to Point distance

Fri Jul 10 07:17:55 PDT 2009

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