<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Apr 11, 2016 at 12:31 PM, Michael Moore <span dir="ltr"><<a href="mailto:michaeljmoore@gmail.com" target="_blank">michaeljmoore@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div class="h5"><br><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Apr 11, 2016 at 11:50 AM, Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">Before I can even begin to address what's going on with the<br>
index/query side, I have to ask about the coordinate system:<br>
<br>
You are using a geocentric system for postal code points?<br>
<br>
<a href="https://epsg.io/4896" rel="noreferrer" target="_blank">https://epsg.io/4896</a><br>
<br>
This seems very odd indeed. If you were flying a satellite or drilling<br>
a deep well, maybe you'd use geocentric coordinates, but simple postal<br>
code queries? So, perhaps there's a core error to deal with first: why<br>
are you in EPSG:4896?<br>
<br>
P<br>
<div><div><br>
<br>
On Mon, Apr 11, 2016 at 11:21 AM, Michael Moore <<a href="mailto:michaeljmoore@gmail.com" target="_blank">michaeljmoore@gmail.com</a>> wrote:<br>
> I am trying to find all zip codes withing a given range of current zip code.<br>
> For example, User is at zip code 95076 and want to know all zip codes within<br>
> a 30 mile range. This will always be a short distance, nothing over 50<br>
> miles. The source zip code is any place in the USA. My input table has a<br>
> latitude field, a longitude field and a POINT field for each zip code.<br>
> I want to supply in input in meters, not degrees. I do NOT want to cast to<br>
> geography because this prevents the index from being used.<br>
> Here is a working query that uses the index, but ST_DWithin is not<br>
> understanding ".05" as meters:<br>
><br>
><br>
> lcd1_dev=> select t2.city, t2.postalcode,<br>
> ST_Distance(t1.geo_position,t2.geo_position)<br>
> lcd1_dev-> from tpostalcoordinate t1<br>
> lcd1_dev-> left join tpostalcoordinate t2 on<br>
> ST_DWithin(t1.geo_position,t2.geo_position , .05)<br>
> lcd1_dev-> where t1.postalcode = '94404'and t1.countrycode2tcountry = 'US'<br>
> and t2.countrycode2tcountry= 'US';<br>
> city | postalcode | st_distance<br>
> --------------+------------+---------------------<br>
> Redwood City | 94065 | 0.0273766323714193<br>
> San Mateo | 94408 | 0.00504738546179631<br>
> Belmont | 94002 | 0.0440065904155286<br>
> San Mateo | 94404 | 0<br>
> San Mateo | 94403 | 0.0370314731005901<br>
> San Mateo | 94407 | 0.0416118372581607<br>
><br>
> This shows that I am using SRID 4896<br>
> lcd1_dev=> select * from geometry_columns where f_table_name =<br>
> 'tpostalcoordinate';<br>
> f_table_catalog | f_table_schema | f_table_name | f_geometry_column |<br>
> coord_dimension | srid | type<br>
> -----------------+----------------+-------------------+-------------------+-----------------+------+-------<br>
> lcd1_dev | qsn_app | tpostalcoordinate | geo_position |<br>
> 2 | 4896 | POINT<br>
><br>
> 4896 is UNIT "metre" as show here:<br>
> 4896 | EPSG | 4896 |<br>
> GEOCCS["ITRF2005",DATUM["International_Terrestrial_Reference_Frame_2005",SPHEROID["GRS<br>
> 1980",6378137,298.257222101,AUTHORIT<br>
> Y["EPSG","7019"]],AUTHORITY["EPSG","6896"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Geocentric<br>
> X",OTH<br>
> ER],AXIS["Geocentric Y",OTHER],AXIS["Geocentric<br>
> Z",NORTH],AUTHORITY["EPSG","4896"]] | +proj=geocent +ellps=GRS80 +units=m<br>
> +no_de<br>
><br>
> The following (from EXPLAIN) proves that the index is being used in the<br>
> working query shown above:<br>
> -> Index Scan using tpostalcoordinate_pk on tpostalcoordinate t1<br>
> (cost=0.42..8.45 rows=1 width=32)<br>
><br>
> What do I need to do to get this in meters without losing my index access?<br>
><br>
> TIA,<br>
> Mike<br>
><br>
</div></div>> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div><br></div></div></div><div class="gmail_extra">Here is what we do to create the column on the table:</div><div class="gmail_extra"><p style="margin:0in 0in 0.0001pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><span style="font-size:x-small;color:rgb(31,73,125)"> </span><br></p><p style="margin:0in 0in 0.0001pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><span style="color:rgb(31,73,125)"><font size="1">1. SELECT AddGeometryColumn('qsn_app','tpostalcoordinate', 'geo_position',4326,'POINT',2);</font></span></p><p style="margin:0in 0in 0.0001pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><span style="color:rgb(31,73,125)"><font size="1"> </font></span></p><p style="margin:0in 0in 0.0001pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><span style="color:rgb(31,73,125)"><font size="1">2. UPDATE tpostalcoordinate pc set pc.geo_position = ST_SetSRID(ST_Point(pc.longitude, pc.latitude), 4326);</font></span></p><p style="margin:0in 0in 0.0001pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><span style="color:rgb(31,73,125)"><font size="1"> </font></span></p><p style="margin:0in 0in 0.0001pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><span style="color:rgb(31,73,125)"><font size="1">3. CREATE INDEX tpostal_geo_position_idx ON tpostalcoordinate USING gist(geo_position);</font></span></p><p style="margin:0in 0in 0.0001pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><span style="color:rgb(31,73,125)"><font size="1"><br></font></span></p><p style="margin:0in 0in 0.0001pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><span style="color:rgb(31,73,125)"><font size="1">I am on </font></span><span style="font-size:12.8px;font-family:arial,sans-serif;color:rgb(34,34,34)">EPSG:4896 because I tried about 10 others that were also meters and that's just the one I ended up on . There is one thing you said that leads me to believe there is something I don't understand: "...</span><span style="font-family:arial,sans-serif;color:rgb(34,34,34)">geocentric system...</span><span style="font-family:arial,sans-serif;color:rgb(34,34,34)">". My understanding is that there are two datatypes to choose from ; geometry and geography. I have chosen geometry because 1. it should be faster due to simpler calculations and 2. It should be faster because it can use the index. Beyond that I thought that I would then need to choose a proper SRID. The two criteria for choosing an SRID for me are 1. the units are meters and 2. it is based on a datum that is appropriate for the USA. Now the fact that you said: "geocentric system" makes me think that maybe there is something else I need to be looking at when choosing a proper SRID. A little more on the application: The user wants to know all of the schools that are within a specific distance of his current location, his zip code. Precision is not important. We get the user's zip code from the screen and we have a list of schools and their zip codes. </span></p><p style="margin:0in 0in 0.0001pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><span style="font-family:arial,sans-serif;color:rgb(34,34,34)"><br></span></p><p style="margin:0in 0in 0.0001pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><span style="font-family:arial,sans-serif;color:rgb(34,34,34)">Thanks,</span></p><p style="margin:0in 0in 0.0001pt;font-family:Calibri,sans-serif;color:rgb(0,0,0)"><span style="font-family:arial,sans-serif;color:rgb(34,34,34)">Mike</span></p></div></div></blockquote><div> </div></div>John,</div><div class="gmail_extra">The latitude and longitude are correct and I know that because if I cast my geometry points to geography, every thing works perfectly: it understand meters and expected target zip codes are in the result set. The only problem with with casting to geography is that geography does not use the index. </div><div class="gmail_extra">Regards,</div><div class="gmail_extra">Mike</div><div class="gmail_extra"><br></div></div>