<HTML><BODY style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; "><DIV>You don't want to order by the point_geom because Postgres doesn't understand the geometry type natively. You should ORDER BY distance_spheroid(...) with the distance_spheroid clause being the same as in the SELECT clause.</DIV><BR><DIV><DIV>On Dec 30, 2006, at 1:12 AM, Adam wrote:</DIV><BR class="Apple-interchange-newline"><BLOCKQUOTE type="cite"> <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><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-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">_______________________________________________</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">postgis-users mailing list</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></DIV> </BLOCKQUOTE></DIV><BR></BODY></HTML>