<html>
<head>
<title></title>
<meta name="GENERATOR" content="MSHTML 8.00.6001.18783"></meta>
</head>
<body>
<div align="left">I think you are discussing totally different things. As I understand it Peter has never asked for any interpolated points, just the diastance between opints in a point table and linestrings in a linetable. </div>
<div align="left"> </div>
<div align="left">The problem is, as you have found Peter that nothing but points is handled by the ST_Distance_Sphere and ST_Distance_Spheroid, but as I have understood it there is work going on by Paul Ramsey to implement support for more than points. </div>
<div align="left">Working with metric srids will not be a good idea if you are working over big areas.</div>
<div align="left"> </div>
<div align="left">I think that summarizes your problem, but I don't know how to solve it.</div>
<div align="left">sorry</div>
<div align="left">hopefully someone has a good solution.</div>
<div align="left">/Nicklas</div>
<div align="left"> </div>
<div align="left"> </div>
<div align="left"> </div>
<div align="left">2009-07-10 Pavel Iacovlev wrote:<br />
<br />
The length of the line, you can't calculate a distance between a point<br />
>and a line in general. You can calculate a distance between a point<br />
>and a point on a line.<br />
><br />
>ST_distance_spheroid(point, ST_line_interpolate_point(line,<br />
>ST_line_locate_point(line, point)),<br />
>'SPHEROID["GRS_1980",6378137,298.257222101]')<br />
><br />
>ST_line_locate_point(line, point) - a point on the line that is most<br />
>close to your initial point<br />
><br />
>ST_line_interpolate_point - get that point as a point geometry<br />
><br />
>ST_distance_spheroid - calculate the length between 2 points<br />
><br />
>2009/7/10 Peter Kukuča
<pjr.kukuca@gmail.com></pjr.kukuca@gmail.com>:<br />
>> How is your query calculating the distance between a point and a line? Are<br />
>> you not just calculating the length of the line?<br />
>><br />
>> 2009/7/10 Pavel Iacovlev
<iacovlev.pavel@gmail.com></iacovlev.pavel@gmail.com><br />
>>><br />
>>> Works on lines for me:<br />
>>><br />
>>> QUERY:<br />
>>> select ST_length_spheroid(st_transform(the_geom, 4326),<br />
>>> 'SPHEROID["GRS_1980",6378137,298.257222101]') as ellps_distance,<br />
>>> st_distance(the_geom) as normal_distance from fondcart_layers.roads<br />
>>> limit 1<br />
>>><br />
>>> RESULT:<br />
>>> ellps_distance: 9214.65310341668<br />
>>> normal_distance: 9214.10811677981<br />
>>><br />
>>> 2009/7/10 Peter Kukuča
<pjr.kukuca@gmail.com></pjr.kukuca@gmail.com>:<br />
>>> > ST_Length_spheroid is perfectly great, but it does not take a line as an<br />
>>> > input :-(<br />
>>> ><br />
>>> > 2009/7/10 Pavel Iacovlev
<iacovlev.pavel@gmail.com></iacovlev.pavel@gmail.com><br />
>>> >><br />
>>> >> And for geometry length use ST_length_spheroid(geometry,spheroid)<br />
>>> >><br />
>>> >> On Fri, Jul 10, 2009 at 11:48 AM, Pavel<br />
>>> >> Iacovlev
<iacovlev.pavel@gmail.com></iacovlev.pavel@gmail.com> wrote:<br />
>>> >> > you can take the middle of the line,<br />
>>> >> > st_line_interpolate_point(the_geom,<br />
>>> >> > 0.5)<br />
>>> >> ><br />
>>> >> > 2009/7/10 Peter Kukuča
<pjr.kukuca@gmail.com></pjr.kukuca@gmail.com>:<br />
>>> >> >> Hi Pedro,<br />
>>> >> >><br />
>>> >> >> thank you for your reply.<br />
>>> >> >><br />
>>> >> >> I cannot use pointn, the line can be up to several hundred<br />
>>> >> >> kilometers<br />
>>> >> >> long<br />
>>> >> >> and consits of up to 800 points, so this would be too rough.<br />
>>> >> >><br />
>>> >> >> Regardinf the decond suggestion, that is exactly what I am doing<br />
>>> >> >> now,<br />
>>> >> >> but I<br />
>>> >> >> don't know, which projection (srid) to transform the wgs84 data<br />
>>> >> >> into.<br />
>>> >> >> It<br />
>>> >> >> should work on all of the earth's surface, at least in all of europe<br />
>>> >> >> for a<br />
>>> >> >> start. And from what I found, the projections are usually tied to a<br />
>>> >> >> small<br />
>>> >> >> territory. I was thinking about mercator projection, but there is a<br />
>>> >> >> zillion<br />
>>> >> >> of them in postgis. And I found a post, that the one with srid<br />
>>> >> >> 900913<br />
>>> >> >> is<br />
>>> >> >> working great, but I don't have that one in my postgis.<br />
>>> >> >><br />
>>> >> >> 2009/7/10 Pedro Doria Meunier
<pdoria@netmadeira.com></pdoria@netmadeira.com><br />
>>> >> >>><br />
>>> >> >>> -----BEGIN PGP SIGNED MESSAGE-----<br />
>>> >> >>> Hash: SHA1<br />
>>> >> >>><br />
>>> >> >>> Hi Peter<br />
>>> >> >>><br />
>>> >> >>> You could use the pointn(geometry, n) for the first point of the<br />
>>> >> >>> linestring as a first approach.<br />
>>> >> >>> As far as meters are concerned here's a little example of<br />
>>> >> >>> transforming<br />
>>> >> >>> the geometry to the desired projected system:<br />
>>> >> >>><br />
>>> >> >>> select distance(transform(u.coordinates,srid),<br />
>>> >> >>> transform(p.geometry,srid)) AS thedistance<br />
>>> >> >>><br />
>>> >> >>> HTH,<br />
>>> >> >>><br />
>>> >> >>> Pedro Doria Meunier<br />
>>> >> >>> GSM: +351 96 17 20 188<br />
>>> >> >>> Skype: pdoriam<br />
>>> >> >>><br />
>>> >> >>><br />
>>> >> >>><br />
>>> >> >>><br />
>>> >> >>> Peter Kukuča wrote:<br />
>>> >> >>> > Dear sir,<br />
>>> >> >>> ><br />
>>> >> >>> > I am not sure if I am writing to the correct email address. I<br />
>>> >> >>> > have<br />
>>> >> >>> > found a post on the postgis-users formu from *Rich Gibson *and<br />
>>> >> >>> > this<br />
>>> >> >>> > address was next to it.<br />
>>> >> >>> ><br />
>>> >> >>> > I am using postgis for a while now and it is great. It solved a<br />
>>> >> >>> > lot<br />
>>> >> >>> > of problems for me.<br />
>>> >> >>> ><br />
>>> >> >>> > However, I am now facing a problem I cannot solve. I searched for<br />
>>> >> >>> > almost two days now, but I still cannot find an answer. Here is<br />
>>> >> >>> > my<br />
>>> >> >>> > problem:<br />
>>> >> >>> ><br />
>>> >> >>> > I have a database of linestrings in wgs84 projection and points<br />
>>> >> >>> > in<br />
>>> >> >>> > wgs84 projection. I would like to determine the distance between<br />
>>> >> >>> > a<br />
>>> >> >>> > line and a point. The ST_distance function does this very well,<br />
>>> >> >>> > but<br />
>>> >> >>> > it does not take the wgs84 projection into account and also, the<br />
>>> >> >>> > result is in degrees. On the other hand, the ST_Distance_Spheroid<br />
>>> >> >>> > does take the wgs84 into accound and does give the result in<br />
>>> >> >>> > meters,<br />
>>> >> >>> > but it does not accept a linestring as an input parameter.<br />
>>> >> >>> ><br />
>>> >> >>> > Is there any way i can the distance between a line and a point in<br />
>>> >> >>> > meters from the wgs84 projected input? I do not need grat<br />
>>> >> >>> > accuracy.<br />
>>> >> >>> > +-5 meters is still good enough. Thank you for your tips.<br />
>>> >> >>> ><br />
>>> >> >>> > --<br />
>>> >> >>> > S pozdravom (regards)<br />
>>> >> >>> > Ing. Peter Kukuča<br />
>>> >> >>> ><br />
>>> >> >>> ><br />
>>> >> >>> ><br />
>>> >> >>> > ----------------------------------------------------------------------<br />
>>> >> >>> ><br />
>>> >> >>> > _______________________________________________<br />
>>> >> >>> > postgis-users mailing list<br />
>>> >> >>> > postgis-users@postgis.refractions.net<br />
>>> >> >>> > http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
>>> >> >>> -----BEGIN PGP SIGNATURE-----<br />
>>> >> >>> Version: GnuPG v1.4.7 (GNU/Linux)<br />
>>> >> >>> Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org<br />
>>> >> >>><br />
>>> >> >>> iD8DBQFKVv052FH5GXCfxAsRAqS1AJ0dfphFX3wrMuo+FdXVRTgmUzHRUACfUmvH<br />
>>> >> >>> 146yGHfxoVmlnt9a91rpWmE=<br />
>>> >> >>> =G70f<br />
>>> >> >>> -----END PGP SIGNATURE-----<br />
>>> >> >>><br />
>>> >> >>> _______________________________________________<br />
>>> >> >>> postgis-users mailing list<br />
>>> >> >>> postgis-users@postgis.refractions.net<br />
>>> >> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
>>> >> >><br />
>>> >> >><br />
>>> >> >><br />
>>> >> >> --<br />
>>> >> >> S pozdravom (regards)<br />
>>> >> >> Ing. Peter Kukuča<br />
>>> >> >><br />
>>> >> >> _______________________________________________<br />
>>> >> >> postgis-users mailing list<br />
>>> >> >> postgis-users@postgis.refractions.net<br />
>>> >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
>>> >> >><br />
>>> >> >><br />
>>> >> ><br />
>>> >> ><br />
>>> >> ><br />
>>> >> > --<br />
>>> >> > http://iap.md, The future is open<br />
>>> >> ><br />
>>> >><br />
>>> >><br />
>>> >><br />
>>> >> --<br />
>>> >> http://iap.md, The future is open<br />
>>> >> _______________________________________________<br />
>>> >> postgis-users mailing list<br />
>>> >> postgis-users@postgis.refractions.net<br />
>>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
>>> ><br />
>>> ><br />
>>> ><br />
>>> > --<br />
>>> > S pozdravom (regards)<br />
>>> > Ing. Peter Kukuča<br />
>>> ><br />
>>> > _______________________________________________<br />
>>> > postgis-users mailing list<br />
>>> > postgis-users@postgis.refractions.net<br />
>>> > http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
>>> ><br />
>>> ><br />
>>><br />
>>><br />
>>><br />
>>> --<br />
>>> http://iap.md, The future is open<br />
>>> _______________________________________________<br />
>>> postgis-users mailing list<br />
>>> postgis-users@postgis.refractions.net<br />
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
>><br />
>><br />
>><br />
>> --<br />
>> S pozdravom (regards)<br />
>> Ing. Peter Kukuča<br />
>><br />
>> _______________________________________________<br />
>> postgis-users mailing list<br />
>> postgis-users@postgis.refractions.net<br />
>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
>><br />
>><br />
><br />
><br />
><br />
>-- <br />
>http://iap.md, The future is open<br />
>_______________________________________________<br />
>postgis-users mailing list<br />
>postgis-users@postgis.refractions.net<br />
>http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
></div>
</body>
</html>