[postgis-tickets] [PostGIS] #2363: st_distance with an added Integer value is not working in 2.0

[PostGIS]

#2363: st_distance with an added Integer value is not working in 2.0
-------------------------+--------------------------------------------------
  Reporter:  vbalent     |       Owner:  pramsey      
      Type:  defect      |      Status:  closed       
  Priority:  blocker     |   Milestone:  PostGIS 2.0.4
 Component:  postgis     |     Version:  2.0.x        
Resolution:  worksforme  |    Keywords:               
-------------------------+--------------------------------------------------

Comment(by robe):

 That's a little convoluted.  The ST_AsText is just for displaying geometry
 in user friendly way not to actually use.  That would be super slow with
 large geometries.

 Now that said I see you are doing this:

 {{{
 ST_Transform(ST_POINTFROMTEXT(ST_AsText(GEOM),4326),2260)
 }}}

 Which suggests your table data is NOT in 2260 projection or if it is it is
 misstated but really in wgs_84 long lat (assuming your above query works
 as you are expecting it to).

 What does:

 {{{
 SELECT ST_SRID(geom) from part_1_crimes limit 1;
 }}}

 return?

 To save yourself having to write convoluted (and super slow code that
 doesn't use an index) as the  above, convert your table to 2260 with a
 command something like:


 {{{
 ALTER TABLE part_1_crimes ALTER TYPE geom geometry(POINT,2260) USING
 ST_Transform(ST_SetSRID(geom, 4326),5260);
 }}}


 Then you should be able to rewrite your query as follows:


 {{{
  SELECT * FROM PART_1_CRIMES WHERE
 ST_DWithin(ST_TRANSFORM(ST_GeomFromText('POINT(-73.76545859999999
 42.658618)', 4326), 2260),

     GEOM, 5280);

 }}}

 and if you have a gist index on your geom column, the above query should
 run MUCH faster and yield the same results.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/2363#comment:10>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.