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 <[email protected]> > 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 <[email protected]>: > > 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 <[email protected]> > >> > >> 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 > > > > > > > > -- > > 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
