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 <[email protected]>: > 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 <[email protected]> >> >> 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 <[email protected]>: >> > 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 <[email protected]> >> >> >> >> 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 <[email protected]>: >> >> > ST_Length_spheroid is perfectly great, but it does not take a line as >> >> > an >> >> > input :-( >> >> > >> >> > 2009/7/10 Pavel Iacovlev <[email protected]> >> >> >> >> >> >> And for geometry length use ST_length_spheroid(geometry,spheroid) >> >> >> >> >> >> On Fri, Jul 10, 2009 at 11:48 AM, Pavel >> >> >> Iacovlev<[email protected]> wrote: >> >> >> > you can take the middle of the line, >> >> >> > st_line_interpolate_point(the_geom, >> >> >> > 0.5) >> >> >> > >> >> >> > 2009/7/10 Peter Kukuča <[email protected]>: >> >> >> >> 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 <[email protected]> >> >> >> >>> >> >> >> >>> -----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 >> >> >> >>> > [email protected] >> >> >> >>> > 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 >> >> >> >>> [email protected] >> >> >> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> -- >> >> >> >> S pozdravom (regards) >> >> >> >> Ing. Peter Kukuča >> >> >> >> >> >> >> >> _______________________________________________ >> >> >> >> postgis-users mailing list >> >> >> >> [email protected] >> >> >> >> 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 >> >> >> [email protected] >> >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> > >> >> > >> >> > >> >> > -- >> >> > S pozdravom (regards) >> >> > Ing. Peter Kukuča >> >> > >> >> > _______________________________________________ >> >> > postgis-users mailing list >> >> > [email protected] >> >> > http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> > >> >> > >> >> >> >> >> >> >> >> -- >> >> http://iap.md, The future is open >> >> _______________________________________________ >> >> postgis-users mailing list >> >> [email protected] >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> > >> > >> > >> > -- >> > S pozdravom (regards) >> > Ing. Peter Kukuča >> > >> > _______________________________________________ >> > postgis-users mailing list >> > [email protected] >> > http://postgis.refractions.net/mailman/listinfo/postgis-users >> > >> > >> >> >> >> -- >> http://iap.md, The future is open >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > -- > S pozdravom (regards) > Ing. Peter Kukuča > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > -- http://iap.md, The future is open _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
