<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.3020" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY
style="WORD-WRAP: break-word; khtml-nbsp-mode: space; khtml-line-break: after-white-space"
bgColor=#ffffff>
<DIV><FONT face=Arial size=2>I've changed this to give me output by
miles:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SELECT </FONT></DIV>
<DIV><FONT face=Arial size=2> location_id, </FONT></DIV>
<DIV><FONT face=Arial size=2> ( </FONT></DIV>
<DIV><FONT face=Arial size=2>
distance_spheroid(point_geom, </FONT></DIV>
<DIV><FONT face=Arial size=2>
SetSRID(MakePoint(-117.13538, 32.6857),4326), </FONT></DIV>
<DIV><FONT face=Arial size=2>
'SPHEROID["WGS_1984",6378137,298.257223563]') / 1609.344 </FONT></DIV>
<DIV><FONT face=Arial size=2> ) </FONT></DIV>
<DIV><FONT face=Arial size=2>FROM </FONT></DIV>
<DIV><FONT face=Arial size=2> locations;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>How would I change this to give me the first
returned row as the closest point and ascend to the farthest point? I know
I'd usually use an "ORDERY BY" clause, but what do I "ORDER BY"? I tried
"ORDER BY point_geom" but that isn't exactly in order.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=brad@bradediger.com href="mailto:brad@bradediger.com">Brad Ediger</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=postgis-users@postgis.refractions.net
href="mailto:postgis-users@postgis.refractions.net">PostGIS Users
Discussion</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Saturday, December 30, 2006 12:58
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [postgis-users] Finding
Points with distance to a location.</DIV>
<DIV><BR></DIV>
<DIV>My bad.</DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>SELECT location_id, Distance(point_geom, SetSRID(MakePoint(-117.13538,
32.6857),4326)) FROM locations;<SPAN class=Apple-style-span
style="WORD-SPACING: 0px; FONT: 12px Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; border-spacing: 0px 0px; khtml-text-decorations-in-effect: none; apple-text-size-adjust: auto; orphans: 2; widows: 2"><SPAN
class=Apple-style-span
style="WORD-SPACING: 0px; FONT: 12px Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; border-spacing: 0px 0px; khtml-text-decorations-in-effect: none; apple-text-size-adjust: auto; orphans: 2; widows: 2"><SPAN
class=Apple-style-span
style="WORD-SPACING: 0px; FONT: 12px Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; border-spacing: 0px 0px; khtml-text-decorations-in-effect: none; apple-text-size-adjust: auto; orphans: 2; widows: 2"><BR
class=Apple-interchange-newline></SPAN></SPAN></SPAN></DIV>
<DIV>PostGIS does some sanity checks to ensure you're not operating on
different SRIDs (which would be meaningless). You have to tell PostGIS that
the point you're creating has SRID 4326 (WGS84). Note that SetSRID won't
convert a geometry from one SRID to another; you need Transform() for that.
You're simply tagging that point with the 4326 SRID.</DIV><BR>
<DIV>
<DIV>On Dec 30, 2006, at 12:54 AM, Adam wrote:</DIV><BR
class=Apple-interchange-newline>
<BLOCKQUOTE type="cite">
<DIV><FONT face=Arial size=2>WHen I run: <FONT face="Times New Roman"
size=3>SELECT location_id, Distance(point_geom, MakePoint(-117.13538,
32.6857)) FROM locations;</FONT></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I get error: Operation on two GEOMETRIES
with different SRIDs</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>What does that mean?</FONT></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=brad@bradediger.com href="mailto:brad@bradediger.com">Brad
Ediger</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=postgis-users@postgis.refractions.net
href="mailto:postgis-users@postgis.refractions.net">PostGIS Users
Discussion</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Saturday, December 30, 2006
12:49 AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [postgis-users] Finding
Points with distance to a location.</DIV>
<DIV><BR></DIV>
<DIV><SPAN class=Apple-style-span
style="WORD-SPACING: 0px; FONT: 12px Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; border-spacing: 0px 0px; khtml-text-decorations-in-effect: none; apple-text-size-adjust: auto; orphans: 2; widows: 2"><SPAN
class=Apple-style-span
style="WORD-SPACING: 0px; FONT: 12px Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; border-spacing: 0px 0px; khtml-text-decorations-in-effect: none; apple-text-size-adjust: auto; orphans: 2; widows: 2"><SPAN
class=Apple-style-span
style="WORD-SPACING: 0px; FONT: 12px Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; border-spacing: 0px 0px; khtml-text-decorations-in-effect: none; apple-text-size-adjust: auto; orphans: 2; widows: 2">You
want to use the Distance function in the result set:<BR
class=Apple-interchange-newline></SPAN></SPAN></SPAN></DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>SELECT location_id, Distance(point_geom, MakePoint(-117.13538,
32.6857)) FROM table;</DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>You don't need a WHERE clause unless you want to restrict the result
rows (for example, if you wanted to limit the results to locations within
50 miles of the target point).</DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>I would suggest reviewing the PostgreSQL documentation for some basic
SQL instruction (at <A
href="http://www.postgresql.org/docs/">http://www.postgresql.org/docs/</A>;
the manual is actually very good). Then you can refer to the PostGIS
reference (<A
href="http://postgis.refractions.net/docs/ch06.html">http://postgis.refractions.net/docs/ch06.html</A>) for
a list of functions.</DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>You really don't need a lot of knowledge on top of PostgreSQL to use
PostGIS effectively. Most everything that seems confusing or vague about
the PostGIS documentation can be clarified by reading the OpenGIS specs,
at <A
href="http://www.opengeospatial.org/standards/sfa">http://www.opengeospatial.org/standards/sfa</A>
and <A
href="http://www.opengeospatial.org/standards/sfb">http://www.opengeospatial.org/standards/sfb</A>.</DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>And you can always ask on postgis-users if you need
clarification.</DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>Hope this helps,</DIV>
<DIV>Brad</DIV><BR>
<DIV>
<DIV>On Dec 30, 2006, at 12:10 AM, Adam wrote:</DIV><BR
class=Apple-interchange-newline>
<BLOCKQUOTE type="cite">
<DIV><FONT face=Arial size=2>I've tried:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SELECT point_geom, location_id<BR>FROM
locations<BR>WHERE Distance(point_geom, 'POINT(-117.13538
32.6857)')<BR></FONT></DIV>
<DIV><FONT face=Arial size=2>But get error: argument of WHERE must
be type boolean, not type double precision</FONT></DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV><FONT face=Arial size=2></FONT><BR
class=khtml-block-placeholder></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=adam@spatialsystems.org
href="mailto:adam@spatialsystems.org">Adam</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=postgis-users@postgis.refractions.net
href="mailto:postgis-users@postgis.refractions.net">PostGIS Users
Discussion</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Saturday, December 30, 2006
12:07 AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> [postgis-users] Finding
Points with distance to a location.</DIV>
<DIV><BR></DIV>
<DIV><FONT face=Arial size=2>I figured this out last year, but can't
figure out how I figured it out. I've read "Mapping Hacks" and
"Web Mapping Illustrated" along with what I could find in the WIKI and
documentation but can't start to figure it out.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I have my geometry column named
"point_geom" and a unique column named "location_id".</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I want to query the distance of all the
points in my "point_geom" column to coords: Long = -117.13538 ::
Lat = 32.6857</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2><STRONG>What would the query for this
look like? Is there a comprehensive article or book out that
goes in depth into how spatial queries work?</STRONG></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>The output would look like
this:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>location_id | distance</FONT></DIV>
<DIV><FONT face=Arial
size=2>-------------------------------</FONT></DIV>
<DIV><FONT face=Arial size=2>5 | 45.38823</FONT></DIV>
<DIV><FONT face=Arial size=2>9 | 50.39952</FONT></DIV>
<DIV><FONT face=Arial size=2>1 | 82.39923</FONT></DIV>
<DIV><FONT face=Arial size=2>.</FONT></DIV>
<DIV><FONT face=Arial size=2>.</FONT></DIV>
<DIV><FONT face=Arial size=2>.</FONT></DIV>
<DIV><FONT face=Arial
size=2>-------------------------------</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I'm guessing that you'd use
distance_spheroid for the
calculation. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<HR>
<DIV><BR
class=khtml-block-placeholder></DIV>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE>
<DIV
style="MARGIN: 0px">_______________________________________________</DIV>
<DIV style="MARGIN: 0px">postgis-users mailing list</DIV>
<DIV style="MARGIN: 0px"><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></DIV>
<DIV style="MARGIN: 0px"><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></DIV></BLOCKQUOTE></DIV><BR>
<DIV><BR class=khtml-block-placeholder></DIV>
<HR>
<DIV><BR
class=khtml-block-placeholder></DIV>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR></BLOCKQUOTE>
<DIV
style="MARGIN: 0px">_______________________________________________</DIV>
<DIV style="MARGIN: 0px">postgis-users mailing list</DIV>
<DIV style="MARGIN: 0px"><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></DIV>
<DIV style="MARGIN: 0px"><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></DIV></BLOCKQUOTE></DIV><BR>
<P>
<HR>
<P></P>_______________________________________________<BR>postgis-users
mailing
list<BR>postgis-users@postgis.refractions.net<BR>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR></BLOCKQUOTE></BODY></HTML>