<html style="direction: ltr;">
<head>
<meta content="text/html; charset=windows-1255"
http-equiv="Content-Type">
<style>body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="preferred-charset"
bgcolor="#FFFFFF" text="#000000">
Hi Mike:<br>
<br>
Here's a solution using spatialite. <br>
I start with a table (non-spatial) with six columns: <br>
id1, lon1, lat1, id2, lon2, lat2<br>
which represent the id's for each point in the pair, and each
point's longitude and latitude.<br>
<br>
spatialite> SELECT * FROM dual_points;<br>
<br>
id1 lon1 lat1 id2 lon2
lat2 <br>
---------- ---------- ---------- ---------- ----------
----------<br>
1 0.01 51.5 1 -2.5
51.4 <br>
2 -1.8 52.3 2 -1.5
53.6 <br>
3 -0.1 50.8 3 1.0 50.9 <br>
<br>
I can create a get a LINESTRING geometry for each pair of points as
follows:<br>
<br>
spatialite> SELECT AsText(GeomFromText('LINESTRING('||lon1||'
'||lat1||', '||lon2||' '||lat2||')',4326)) AS LineStrings FROM
dual_points;<br>
<br>
LineStrings <br>
--------------------------------<br>
LINESTRING(0.01 51.5, -2.5 51.4)<br>
LINESTRING(-1.8 52.3, -1.5 53.6)<br>
LINESTRING(-0.1 50.8, 1 50.9) <br>
<br>
I need all the pipe characters (||) and the single quotes (') to
correctly concatenate the text part of the LINESTRING and the lon
and lat column names. And I specify the EPSG code 4326 to insure
that the line is in a geographic (non-projected) CRS.<br>
<br>
Now, with that, it's simple to get the cartesian distances, and
geodesic distances between each pair of points:<br>
<br>
spatialite> SELECT<br>
...> GLength(GeomFromText('LINESTRING('||lon1||'
'||lat1||', '||lon2||' '||lat2||')',4326))<br>
...> AS "Cartesian Length (deg)",<br>
...>
GeodesicLength(GeomFromText('LINESTRING('||lon1||' '||lat1||',
'||lon2||' '||lat2||')',4326))<br>
...> AS "Geodesic Length (m)",<br>
...>
GreatCircleLength(GeomFromText('LINESTRING('||lon1||' '||lat1||',
'||lon2||' '||lat2||')',4326))<br>
...> AS "Great Circle Length (m)"<br>
...> FROM dual_points;<br>
Cartesian Length (deg) Geodesic Length (m) Great Circle Length (m)<br>
---------------------- ------------------- -----------------------<br>
2.51199124202295 174832.047110646 174280.616818531
<br>
1.33416640641264 146069.046237422 145943.828398285
<br>
1.10453610171873 78260.0862914619 78019.3444824877 <br>
<br>
That might get you started,<br>
Micha<br>
<br>
<br>
On 05/21/2012 12:59 PM, M.E.Dodd wrote:
<blockquote
cite="mid:9932A16F9CE9264FAC3F2E0E008E1221013DD0D56E@KIELDERCMS1.open.ac.uk"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1255">
<meta name="Generator" content="Microsoft Word 14 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";
color:#660000;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";
color:#660000;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:#660000;}
p.emailquote, li.emailquote, div.emailquote
{mso-style-name:emailquote;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:1.0pt;
border:none;
padding:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";
color:#660000;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:"Consolas","serif";
color:#660000;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Arial","sans-serif";
color:blue;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:blue">Each
row in table has two sets of coordinates and I want to find
distance between these two points. <o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:blue"> <o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:blue">I
had assumed it might be an issue to have two sets of
coordinates per line so have created a second table
identical to the first one so one table could have one pair
of columns as the point coordinate and the second table
could have the second pair of columns as the coordinate.
This works with the ftools distance matrix but that gives
distance between all points not just the one pair per line
and takes forever as there are over 500 points in this
analysis and will be many thousands in next one I am
planning.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:blue"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:blue">Reprojecting
to flat CRS is tricky or impossible as the points are
worldwide.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:blue"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:blue">Had
hoped this would be possible in qgis since the various tools
can calculate distances in more complex situations.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:blue"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext"
lang="EN-US">From:</span></b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext"
lang="EN-US"> Micha Silver [<a class="moz-txt-link-freetext" href="mailto:micha@arava.co.il">mailto:micha@arava.co.il</a>] <br>
<b>Sent:</b> 21 May 2012 10:35<br>
<b>To:</b> M.E.Dodd<br>
<b>Cc:</b> <a class="moz-txt-link-abbreviated" href="mailto:qgis-user@lists.osgeo.org">qgis-user@lists.osgeo.org</a><br>
<b>Subject:</b> Re: [Qgis-user] distance between points
in a table<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Can you be more specific as to what you
mean by "two sets of points". How do you determine, for each
point in the table, which other point you need the distance
to?<br>
(I'm smelling a spatialite query here :-) )<br>
<br>
Also, distances in WGS84 will be in degrees. Perhaps a
projection to some flat CRS is in order? <br>
<br>
On 21/05/2012 11:25, M.E.Dodd wrote: <o:p></o:p></p>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Very
simple question, how to add a column to a table showing
the distance between two sets of points in the table (they
are both in lat long wgs84 coordinates). There are quite
a number of calculations that can be done on a column but
distance does not seem to be one of them. There are
plugins that calculate distance but they tend to do it
between other types of object or distance between all
points not just the pairs as arranged in the table.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Calibri","sans-serif""> </span><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Dr
Mike Dodd<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Department
of Environment, Earth and Ecosystems<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">The
Open University<br>
Walton Hall<br>
Milton Keynes<br>
MK7 6AA<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""> <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Calibri","sans-serif""><a
moz-do-not-send="true" href="http://www.ispot.org.uk/"><span
style="font-family:"Arial","sans-serif"">http://www.ispot.org.uk/</span></a></span><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Calibri","sans-serif""><a
moz-do-not-send="true"
href="http://www.floodplainmeadows.org.uk/"><span
style="font-family:"Arial","sans-serif"">http://www.floodplainmeadows.org.uk/</span></a></span><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><a
moz-do-not-send="true"
href="http://www.amanita-photolibrary.co.uk/HTML_Trees/">http://www.amanita-photolibrary.co.uk/HTML_Trees/</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""> <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Calibri","sans-serif""> </span><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Calibri","sans-serif""> </span><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Calibri","sans-serif""> </span><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Calibri","sans-serif""> </span><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Calibri","sans-serif""> </span><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<p class="MsoNormal"><br>
-- <br>
The Open University is incorporated by Royal Charter (RC
000391), an exempt charity in England & Wales and a
charity registered in Scotland (SC 038302).<o:p></o:p></p>
<p class="MsoNormal"><br>
<br>
This mail was received via Mail-SeCure System.<br>
<br>
<br>
<br>
<o:p></o:p></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>Qgis-user mailing list<o:p></o:p></pre>
<pre><a moz-do-not-send="true" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a><o:p></o:p></pre>
<pre><a moz-do-not-send="true" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>This mail was received via Mail-SeCure System.<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre><o:p> </o:p></pre>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><br>
<br>
<o:p></o:p></p>
<pre>-- <o:p></o:p></pre>
<pre>Micha Silver<o:p></o:p></pre>
<pre>052-3665918<o:p></o:p></pre>
</div>
<br>
<br>
This mail was received via Mail-SeCure System.<br>
</blockquote>
<p><br>
</p>
<br>
<pre class="moz-signature" cols="72">--
Micha Silver
GIS Consultant, Arava Development Co.
<a class="moz-txt-link-freetext" href="http://www.surfaces.co.il">http://www.surfaces.co.il</a></pre>
</body>
</html>