[postgis-users] Using SRID with meters, but still not getting UNITS in meters with ST_DWithin

[Michael Moore]

I am trying to find all zip codes withing a given range of current zip 
code. For example, User is at zip code 95076 and want to know all zip codes 
within a 30 mile range. This will always be a short distance, nothing over 
50 miles. The source zip code is any place in the USA.  My input table has 
a latitude field, a longitude field and a POINT field for each zip code. 
I want to supply in input in meters, not degrees. I do NOT want to cast to 
*geography* because this prevents the index from being used. 
Here is a *working query* that uses the index, but ST_DWithin  is not 
understanding ".05" as meters:


lcd1_dev=>   select t2.city, t2.postalcode, 
ST_Distance(t1.geo_position,t2.geo_position)
lcd1_dev->   from      tpostalcoordinate t1
lcd1_dev->   left join tpostalcoordinate t2 on ST_DWithin(t1.*geo_position*
,t2.*geo_position* , .05)
lcd1_dev->   where t1.postalcode = '94404'and t1.countrycode2tcountry = 
'US' and t2.countrycode2tcountry= 'US';
     city     | postalcode |     st_distance
--------------+------------+---------------------
 Redwood City | 94065      |  0.0273766323714193
 San Mateo    | 94408      | 0.00504738546179631
 Belmont      | 94002      |  0.0440065904155286
 San Mateo    | 94404      |                   0
 San Mateo    | 94403      |  0.0370314731005901
 San Mateo    | 94407      |  0.0416118372581607

*This shows that I am using SRID 4896*
lcd1_dev=> select * from geometry_columns where f_table_name = 
'tpostalcoordinate';
 f_table_catalog | f_table_schema |   f_table_name    | f_geometry_column | 
coord_dimension | srid | type
-----------------+----------------+-------------------+-------------------+-----------------+------+-------
 lcd1_dev        | qsn_app        | tpostalcoordinate | geo_position      | 
              2 | 4896 | POINT 

*4896 is UNIT "metre" as show here:*
 4896 | EPSG      |      4896 | 
GEOCCS["ITRF2005",DATUM["International_Terrestrial_Reference_Frame_2005",SPHEROID["GRS 
1980",6378137,298.257222101,AUTHORIT
Y["EPSG","7019"]],AUTHORITY["EPSG","6896"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Geocentric X",OTH
ER],AXIS["Geocentric Y",OTHER],AXIS["Geocentric 
Z",NORTH],AUTHORITY["EPSG","4896"]] | +proj=geocent +ellps=GRS80 +units=m 
+no_de


*The following (from EXPLAIN) proves that the index is being used in the 
working query shown above:*   ->  Index Scan using tpostalcoordinate_pk on 
tpostalcoordinate t1  (cost=0.42..8.45 rows=1 width=32)

What do I need to do to get this in meters without losing my index access?

TIA,
Mike
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160411/1204a36b/attachment.html>