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

[Michael Moore]

Sorry about the bottom posting. On a different group I was told to always
bottom post, but you guys seem to be top posting so I suppose I should do
likewise.
Lucas, interesting point you bring up about creating the index as
GEOGRAPHY. In theory, everything I am currently doing should work but If I
can't find a GEOMETRY solution, I'll try creating a GEOGRAPHY index and see
what happens.

On Mon, Apr 11, 2016 at 12:43 PM, Lucas Fairchild-Madar <
lucas.madar at gmail.com> wrote:

> Nevermind, that won't work with your ST_DWithin query. You can create an
> index using a cast if that's something you're looking for.
>
> On Mon, Apr 11, 2016 at 12:42 PM, Lucas Fairchild-Madar <
> lucas.madar at gmail.com> wrote:
>
>> You can cast to geography only within your ST_Distance call to get meters:
>>
>> ST_Distance(t1.geo_position::geography,t2.geo_position::geography)
>>
>> That way the rest of your query will still use the index.
>>
>> 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
>>>
>>
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160411/c8d3cd4e/attachment.html>