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

[Paul Ramsey]

Before I can even begin to address what's going on with the
index/query side, I have to ask about the coordinate system:

You are using a geocentric system for postal code points?

https://epsg.io/4896

This seems very odd indeed. If you were flying a satellite or drilling
a deep well, maybe you'd use geocentric coordinates, but simple postal
code queries? So, perhaps there's a core error to deal with first: why
are you in EPSG:4896?

P


On Mon, Apr 11, 2016 at 11:21 AM, Michael Moore <michaeljmoore at gmail.com> wrote:
> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users