<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR>
<STYLE>@font-face {
font-family: Wingdings;
}
@font-face {
font-family: Tahoma;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
UNKNOWN {
FONT-FAMILY: Tahoma; panose-1: 2 11 6 4 3 5 4 4 2 4
}
UNKNOWN {
MARGIN: 1in 1.25in
}
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.emailstyle17 {
COLOR: navy; FONT-FAMILY: Arial
}
SPAN.EmailStyle18 {
COLOR: navy; FONT-FAMILY: Arial
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=blue link=blue>
<DIV dir=ltr align=left><SPAN class=054415319-19072007><FONT face=Arial
color=#0000ff size=2>You can also use distance_sphere if you don't want to
transform. Its not as accurate as transforming to a meter based system
though</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=054415319-19072007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=054415319-19072007><FONT face=Arial
color=#0000ff size=2>select distance_sphere(a.the_geom,
b.the_geom)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=054415319-19072007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=054415319-19072007><FONT face=Arial
color=#0000ff size=2>For a little bit more accuracy but trade-off of less speed,
you can use distance_spheroid</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=054415319-19072007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=054415319-19072007><FONT face=Arial
color=#0000ff size=2><A
href="http://postgis.refractions.net/pipermail/postgis-users/2007-January/014302.html">http://postgis.refractions.net/pipermail/postgis-users/2007-January/014302.html</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=054415319-19072007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=054415319-19072007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Burgholzer,Robert<BR><B>Sent:</B> Thursday, July 19, 2007 3:20
PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> RE: [postgis-users]
Distance query not working after transfrom?<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">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 face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Select
distance(transform(a.the_geom, 27700), transform(b.the_geom, 27700))
</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">…</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">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
face=Wingdings color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Wingdings">J</SPAN></FONT><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"> )</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">HTH,</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">r.b.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; 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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV>
<DIV>
<P class=MsoNormal
style="MARGIN-BOTTOM: 12pt; MARGIN-LEFT: 0.5in; MARGIN-RIGHT: 0in"><FONT
face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">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-BOTTOM: 12pt; MARGIN-LEFT: 0.5in; MARGIN-RIGHT: 0in"><FONT
face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">----- 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: 0.5in"><FONT face=Arial color=navy
size=2><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">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: 0.5in"><FONT face=Arial color=navy
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=navy
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">r.b.</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face=Arial color=navy
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; 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: 1in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV>
<P class=MsoNormal
style="MARGIN-BOTTOM: 12pt; MARGIN-LEFT: 1in; MARGIN-RIGHT: 0in"><FONT
face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">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: 1in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV style="MARGIN-LEFT: 0.5in">
<DIV class=MsoNormal style="MARGIN-LEFT: 0.5in; TEXT-ALIGN: center"
align=center><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR align=center width="100%" SIZE=1>
</SPAN></FONT></DIV></DIV>
<P class=MsoNormal style="MARGIN-LEFT: 1in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN
style="FONT-SIZE: 12pt">_______________________________________________<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: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV></DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV class=MsoNormal style="MARGIN-LEFT: 0.5in; TEXT-ALIGN: center"
align=center><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR align=center width="100%" SIZE=1>
</SPAN></FONT></DIV>
<P class=MsoNormal style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">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>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>