<html>
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 10 (filtered)">
<style>
<!--
_filtered {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}
_filtered {margin:1.0in 1.25in 1.0in 1.25in;}
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:blue;
text-decoration:underline;}
span.emailstyle17
{font-family:Arial;
color:navy;}
span.EmailStyle18
{font-family:Arial;
color:navy;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=blue>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>What I typically do, is use the transform
function in my distance query to bring it to something that uses meters, so it
goes like:</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Select distance(transform(a.the_geom, 27700),
transform(b.the_geom, 27700)) </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>…</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>I may be totally off with this approach,
there may be some better way of telling PostGIS what units you want, but this
works for me, and is verified by comparison to the original shapefiles (if ArcFoo
can be considered as verification</span></font><font size=2 color=navy
face=Wingdings><span style='font-size:10.0pt;font-family:Wingdings;color:navy'>J</span></font><font
size=2 color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'> )</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>HTH,</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>r.b.</span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b> postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Alan Cunnane<br>
<b><span style='font-weight:bold'>Sent:</span></b> Thursday, July 19, 2007 2:59
PM<br>
<b><span style='font-weight:bold'>To:</span></b> PostGIS Users Discussion<br>
<b><span style='font-weight:bold'>Subject:</span></b> Re: [postgis-users]
Distance query not working after transfrom?</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<div>
<div>
<p class=MsoNormal style='margin-right:0in;margin-bottom:12.0pt;margin-left:
.5in'><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>Could
you suggest to me how I can the get the result in meters and not degrees?
Thanks very much for your help</span></font></p>
<div>
<p class=MsoNormal style='margin-right:0in;margin-bottom:12.0pt;margin-left:
.5in'><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>-----
Original Message ----<br>
From: "Burgholzer,Robert" <rwburgholzer@deq.virginia.gov><br>
To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>
Sent: Thursday, 19 July, 2007 7:55:07 PM<br>
Subject: RE: [postgis-users] Distance query not working after transfrom?</span></font></p>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>I think that this is an
entirely correct result, as you are now getting it in the new units, which are
degrees, rather than meters.</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'>r.b.</span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 color=navy face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:navy'> </span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Alan Cunnane<br>
<b><span style='font-weight:bold'>Sent:</span></b> Thursday, July 19, 2007 2:51
PM<br>
<b><span style='font-weight:bold'>To:</span></b>
postgis-users@postgis.refractions.net<br>
<b><span style='font-weight:bold'>Subject:</span></b> [postgis-users] Distance
query not working after transfrom?</span></font></p>
<p class=MsoNormal style='margin-left:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<div>
<p class=MsoNormal style='margin-right:0in;margin-bottom:12.0pt;margin-left:
1.0in'><font size=3 face="Times New Roman"><span style='font-size:12.0pt'>Im
having a problem with a distance query after I transformed a set of points to
another SRID. I transformed from easting northing (SRID 27700) to longitude
lattitude (SRID 4326). The problem that I am having is that when I do the
same distance queries using both geom columns I get a completely different
result? I know that the distance query using the first easting northing column
is correct. Here is an example of the different returns I am getting:<br>
<br>
SRID (27700):<br>
<br>
SELECT a.stop_reference, b.stop_reference, distance(a.east_north, b.east_north)
FROM bus_stops1 a, bus_stops1 b WHERE a.stop_reference = 6200206290 AND
b.stop_reference = 6200244450;<br>
<br>
stop_reference | stop_reference | distance<br>
----------------+----------------+------------------<br>
6200206290 | 6200244450 |
4320.88972319359<br>
<br>
SRID (4326):<br>
<br>
SELECT a.stop_reference, b.stop_reference, distance(a.googlemap, b.googlemap)
FROM bus_stops1 a, bus_stops1 b WHERE a.stop_reference = 6200206290 AND
b.stop_reference = 6200244450;<br>
<br>
stop_reference | stop_reference | distance<br>
----------------+----------------+-------------------<br>
6200206290 | 6200244450 |
0.041147965100606<br>
<br>
Here is the table I am selecting from:<br>
<br>
<br>
Table
"s0679212.bus_stops1"<br>
Column
|
Type | Modifiers<br>
----------------+-----------------------+-----------<br>
stop_reference | character varying(12) | not null<br>
easting |
integer
| not null<br>
northing |
integer
| not null<br>
full_location | character varying(50) |<br>
gazetteer_code | character varying(1) |<br>
point_type | character varying(1) |<br>
nat_gazetteer | character varying(7) |<br>
district_name | character varying(24) |<br>
town_name | character varying(24) |<br>
east_north |
geometry
| not null<br>
lat |
double precision |<br>
lon |
double precision |<br>
googlemap |
geometry
|<br>
Indexes:<br>
"bus_stops1_pkey" PRIMARY KEY, btree
(stop_reference)<br>
"stops_distance1" gist (east_north)<br>
Check constraints:<br>
"enforce_dims_east_north" CHECK (ndims(east_north)
= 2)<br>
"enforce_dims_googlemap" CHECK (ndims(googlemap) =
2)<br>
"enforce_geotype_east_north" CHECK
(geometrytype(east_north) = 'POINT'::text OR east_north IS NULL)<br>
"enforce_geotype_googlemap" CHECK
(geometrytype(googlemap) = 'POINT'::text OR googlemap IS NULL)<br>
"enforce_srid_east_north" CHECK (srid(east_north)
= 27700)<br>
"enforce_srid_googlemap" CHECK (srid(googlemap) =
4326)<br>
<br>
I created the googlemap column using these commands:<br>
<br>
SELECT AddGeometryColumn( 'bus_stops1', 'googlemap', 4326, 'POINT', 2 );<br>
UPDATE bus_stops1 SET googlemap = transform(setsrid(makepoint(easting,
northing),27700), 4326);<br>
<br>
<br>
Can anyone suggest what on earth could be wrong?<br>
<br>
<br>
<br>
</span></font></p>
</div>
<p class=MsoNormal style='margin-left:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<div style='margin-left:.5in'>
<div class=MsoNormal align=center style='margin-left:.5in;text-align:center'><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=1 width="100%" align=center>
</span></font></div>
</div>
<p class=MsoNormal style='margin-left:1.0in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>Yahoo! Mail is the world's favourite email. Don't
settle for less, <a
href="http://uk.rd.yahoo.com/evt=44106/*http:/uk.docs.yahoo.com/mail/winter07.html"
target="_blank" rel=nofollow>sign up for your free account today</a>.</span></font></p>
</div>
<div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>_______________________________________________<br>
postgis-users mailing list<br>
postgis-users@postgis.refractions.net<br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a></span></font></p>
</div>
</div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
</div>
</div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'> </span></font></p>
<div class=MsoNormal align=center style='margin-left:.5in;text-align:center'><font
size=3 face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=1 width="100%" align=center>
</span></font></div>
<p class=MsoNormal style='margin-left:.5in'><font size=3 face="Times New Roman"><span
style='font-size:12.0pt'>Yahoo! Mail is the world's favourite email. Don't
settle for less, <a
href="http://uk.rd.yahoo.com/evt=44106/*http:/uk.docs.yahoo.com/mail/winter07.html">sign
up for your free account today</a>.</span></font></p>
</div>
</body>
</html>