ST_DWithin not using meters even when SRID unit = "metre"

From: Michael Moore <michaeljmoore(at)gmail(dot)com>
To: postgres list <pgsql-sql(at)postgresql(dot)org>
Subject: ST_DWithin not using meters even when SRID unit = "metre"
Date: 2016-04-08 23:11:59
Message-ID: CACpWLjNZTfbZq=XsLdsgCSMV1YnadNsCaX2Ph4svrtwRZFHEyw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Please forgive the cross post if you are on the postgis users forum. I'm
having some trouble knowing if my stuff is getting through to that list.
-------------------------------------------------------------------------------
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
miles, but from any location in the USA.
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
*This proves that I am using the index:*
-> 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

Browse pgsql-sql by date

  From Date Subject
Next Message Chris 2016-04-11 22:31:26 Connection to database failed: FATAL: no pg_hba.conf entry for host
Previous Message 李嘉 2016-04-07 06:43:30 答复: [SQL] postgresql cpu question