<html><body><div style="color:#000; background-color:#fff; font-family:verdana, helvetica, sans-serif;font-size:13px"><div dir="ltr" id="yui_3_16_0_1_1426885348292_278354"><span>Hi Aaron,</span></div><div id="yui_3_16_0_1_1426885348292_278414" dir="ltr"><br></div><div dir="ltr">Hopefully this (simplistic) description helps.<br></div><div id="yui_3_16_0_1_1426885348292_278606" dir="ltr"><br><span></span></div><div id="yui_3_16_0_1_1426885348292_278415" dir="ltr"><span id="yui_3_16_0_1_1426885348292_278999">Setting a SRID value for a feature tells Postgis what coordinate reference system (CRS) the coordinates are in. This includes the unit, which can be any linear unit of measurement, such as degrees (measured at the surface of the earth), meters, feet, etc. Obviously getting this right is important to calculate distances correctly.<br></span></div><div id="yui_3_16_0_1_1426885348292_279005" dir="ltr"><span><br></span></div><div id="yui_3_16_0_1_1426885348292_278608" dir="ltr"><span id="yui_3_16_0_1_1426885348292_278607">Different CRS's generally apply to different parts of the globe, and include the projection parameters. Note that every projection applies some distortion - trying to map (project) a part of a spherical surface to a flat plane has this problem. There are three main types of distortion - angular (changes shapes), area and distance. Normally to measure distance using a projected CRS, you'd use an equidistant projection (which minimises distance distortions) centered near the location you are trying to measure.</span></div><div id="yui_3_16_0_1_1426885348292_278648" dir="ltr"><span id="yui_3_16_0_1_1426885348292_278607"><br></span></div><div id="yui_3_16_0_1_1426885348292_278753" dir="ltr"><span id="yui_3_16_0_1_1426885348292_278607">An alternative approach is to measure it on a spheroid, in 3D space, instead of on a projected 2D space. This is basically what a Postgis geography allows you to do. But the coordinate units in a geography are degree coordinates, so you need to specify that your coordinates are unprojected lon/lat values when you use them in a geography. The SRID (Spatial Reference ID) for such a CRS is 4326. <br></span></div><div id="yui_3_16_0_1_1426885348292_278769" dir="ltr"><span id="yui_3_16_0_1_1426885348292_278607"><br></span></div><div id="yui_3_16_0_1_1426885348292_278783" dir="ltr"><span id="yui_3_16_0_1_1426885348292_278607">In your case, try:</span></div><div id="yui_3_16_0_1_1426885348292_278784" dir="ltr">SELECT * FROM users<br style="" class="">WHERE ST_DWithin (users.location::geography, st_setsrid(st_makepoint (146.0,<br style="" class="">138.19), 4326)::geography, 100);</div><div id="yui_3_16_0_1_1426885348292_278868"><br></div><div id="yui_3_16_0_1_1426885348292_278869" dir="ltr">This assumes your location is a geometry with a SRID of 4326 (ie: the coordinate values are unprojected lon/lat degrees). It then converts this geometry to a geography datatype, which it tests against a point geometry in SRID 4326, which is also converted to a geography for the test, to see if it is within 100m. So this SQL tests geography against geography datatype.</div><div id="yui_3_16_0_1_1426885348292_279132" dir="ltr"><br></div><div id="yui_3_16_0_1_1426885348292_279159" dir="ltr">If your location feature is not SRID 4326, you'll need to reproject (transform) it to 4326 to for this to work:</div><div id="yui_3_16_0_1_1426885348292_279160" dir="ltr"><br></div><div id="yui_3_16_0_1_1426885348292_279161" dir="ltr">SELECT * FROM users<br style="" class="">WHERE ST_DWithin (ST_Transform(users.location,4326)::geography, st_setsrid(st_makepoint (146.0,<br style="" class="">138.19), 4326)::geography, 100);</div><div id="yui_3_16_0_1_1426885348292_278950" dir="ltr"><br></div><div id="yui_3_16_0_1_1426885348292_279056" dir="ltr">(I haven't tested it, but I think this should work)</div><div id="yui_3_16_0_1_1426885348292_279057" dir="ltr"><br></div><div id="yui_3_16_0_1_1426885348292_279214" dir="ltr">Cheers</div><div dir="ltr"><br></div><div id="yui_3_16_0_1_1426885348292_279215" dir="ltr">Bent Wood<br></div><div id="yui_3_16_0_1_1426885348292_278870"><br> </div><div id="yui_3_16_0_1_1426885348292_278357" style="font-family: verdana, helvetica, sans-serif; font-size: 13px;"> <div id="yui_3_16_0_1_1426885348292_278356" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1426885348292_278355" dir="ltr"> <hr id="yui_3_16_0_1_1426885348292_278754" size="1"> <font id="yui_3_16_0_1_1426885348292_278358" face="Arial" size="2"> <b><span style="font-weight:bold;">From:</span></b> Aaron Lewis <the.warl0ck.1989@gmail.com><br> <b><span style="font-weight: bold;">To:</span></b> postgis-users@lists.osgeo.org <br> <b><span style="font-weight: bold;">Sent:</span></b> Monday, March 23, 2015 12:09 AM<br> <b><span style="font-weight: bold;">Subject:</span></b> [postgis-users] Need help on basic concepts, I just need really simple calculations<br> </font> </div> <div id="yui_3_16_0_1_1426885348292_278443" class="y_msg_container"><br>Hi,<br><br>I've been searching online for days. Trying to understand why SRID is<br>required. So I picked some random value.<br><br>Now I'm need to retrieve POINTs within a circle range, e.g a circle at<br>(146.0, 138.19) with radius of 100 meters:<br><br>SELECT * FROM users<br>WHERE ST_DWithin (users.location, st_setsrid(st_makepoint (146.0,<br>138.19), 2600), 100);<br><br>It's very simple, but the result seems wrong. I have a record contains<br>a POINT(55 43) that matches this query.<br><br>Anyone know what's wrong with it?<br><br>-- <br>Best Regards,<br>Aaron Lewis - PGP: 0x13714D33 - <a href="http://pgp.mit.edu/" target="_blank">http://pgp.mit.edu/</a><br>Finger Print: 9F67 391B B770 8FF6 99DC D92D 87F6 2602 1371 4D33<br>_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br><br><br></div> </div> </div> </div></body></html>