<!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.16788" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=733395913-05022009><FONT face=Arial
color=#0000ff size=2>Mark,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=733395913-05022009><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=733395913-05022009><FONT face=Arial
color=#0000ff size=2> Lets say your table is called sometable and is in
4326 projection, then it would look something like</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=733395913-05022009><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=733395913-05022009>
<DIV>SELECT <SPAN class=733395913-05022009>ebas.</SPAN>ebaname,
distance(ebas.the_geom<SPAN class=733395913-05022009>, </SPAN><SPAN
class=733395913-05022009>pt.the_geom</SPAN>) as
Distance_Metres<BR>FROM <SPAN class=733395913-05022009>ebas</SPAN></DIV>
<DIV><SPAN class=733395913-05022009></SPAN><SPAN
class=733395913-05022009> INNER JOIN </SPAN></DIV>
<DIV><SPAN class=733395913-05022009>(SELECT gid, ST_Transform(the_geom,
32662) As the_geom </SPAN></DIV>
<DIV><SPAN class=733395913-05022009>FROM sometable WHERE <STRONG>gid =
1</STRONG>) As pt</SPAN></DIV>
<DIV><SPAN class=733395913-05022009></SPAN><SPAN class=733395913-05022009>ON
</SPAN> ST_DWithin(<SPAN
class=733395913-05022009>ST_Transform(ebas.the_geom, 32662)</SPAN>,<SPAN
class=733395913-05022009>pt.the_geom</SPAN>, 100000)<BR>ORDER BY
ST_Distance(ST_transform(<SPAN
class=733395913-05022009>ebas.</SPAN>the_geom,32662), <SPAN
class=733395913-05022009>pt.the_geom)</SPAN></DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff size=2>The
gid = 1 is if you just want to pull one record -- you can change the where to
limit to subset of point table.</FONT></SPAN></DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff size=2>If you
are going to do this often, you should really put a functional index on your
table with this transform.</FONT></SPAN></DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff
size=2>something like</FONT></SPAN></DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff size=2>CREATE
INDEX idx_the_geom_32662_ebas ON ebas USING gist<BR>
(st_transform(the_geom, 32662))<BR> WHERE the_geom IS NOT
NULL;<BR></FONT></SPAN></DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff size=2>and
same on your point table.</FONT></SPAN></DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff size=2>Also
be forewarned. I've never worked with Plate Carree, but as a general rule
of thumb, the larger the area a projection supports</FONT></SPAN></DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff size=2>the
more inaccurate the measurement.</FONT></SPAN></DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff size=2>Hope
that helps,</FONT></SPAN></DIV>
<DIV><SPAN class=733395913-05022009><FONT face=Arial color=#0000ff
size=2>Regina</FONT></SPAN></DIV></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>mark
balman<BR><B>Sent:</B> Thursday, February 05, 2009 5:59 AM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users]
transform geometry and calculate distance query<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV>Hi all,</DIV>
<DIV> </DIV>
<DIV>I have a query I need to create that transforms the geometry from lat/long
to plate carree so I can return distances of a point to nearby polygons at a
specified distance (not ideal to use Plate Carree but I cannot find any other
global projection that I can use within the EPSG codes). What I have so far is
as follows:</DIV>
<DIV> </DIV>
<DIV>SELECT ebaname, distance(ST_Transform(ST_geomfromtext('POINT(-77.144
-0.629)',4326),32662), the_geom) as Distance_Metres<BR>FROM ebas<BR>WHERE
ST_DWithin(ST_Transform(the_geom, 32662)
,ST_Transform(ST_geomfromtext('POINT(-77.144 -0.629)',4326),32662),
100000)<BR>AND ST_Transform(the_geom, 32662) >
ST_Transform(ST_geomfromtext('POINT(-77.144 -0.629)',4326),32662)<BR>ORDER BY
ST_Distance(ST_transform(the_geom,32662),
ST_Transform(ST_geomfromtext('POINT(-77.144 -0.629)',4326),32662))</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>This seems to work ok, but what I am trying to do is to use an existing
point layer in postgres (same coordinates as specifying the geometry as
geomfromtext) . I cannot seem to figure this out, any help would be
gratefully appreciated.</DIV>
<DIV> </DIV>
<DIV>TIA</DIV>
<DIV> </DIV>
<DIV>Mark</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>
<P><hr size=1></P>
<P><STRONG><font size="2" color="339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </p> <p> </font></STRONG></P>