ST_Distance and 'as' in Postgis

I've written this query and it works, although it's a bit slow:

SELECT name,
(ST_Distance( ST_Transform( way,900913 ),ST_Transform( ST_GeomFromText('POINT (-6.2222 53.307)',4326),900913 )))
FROM ga_osm_latlong_polygon 
WHERE 
( (ST_Distance( ST_Transform( way,900913 ),ST_Transform( ST_GeomFromText('POINT (-6.2222 53.307)',4326),900913 )))
<= 1000 ) 
ORDER BY
(ST_Distance( ST_Transform( way,900913 ),ST_Transform( ST_GeomFromText('POINT (-6.2222 53.307)',4326),900913 ))),
name

I'm trying to rewrite it in a more elegant way, using 'as':

SELECT name,
(ST_Dis开发者_开发问答tance( ST_Transform( way,900913 ),ST_Transform( ST_GeomFromText('POINT (-6.2222 53.307)',4326),900913 ))) AS d
FROM ga_osm_latlong_polygon 
WHERE ( d <= 1000 ) 
ORDER BY d, name

Unfortunately, I get: ERROR: column "d" does not exist

Any idea about what I'm getting wrong here?

Thanks!

Hallo Mulone

About your alias problem, Luther is right.

About the query being slow has two reasons.

First, you are transforming every point, that takes time

Second and probably more importand, youshould use ST_Dwithin together with a spatial index instead of using distance in the where clause.

ST_Dwithin uses the spatial indexes to sort away a lot of the calculations.

But projecting your data to do those calculations will give you very inaccurate answers. Why not use the geography functions instead.

try:

SELECT d, name
(
  SELECT ST_Distance(a.way,b.geom) as d, a.name from 
      (SELECT way::geography, name from ga_osm_latlong_polygon) a, 
      (SELECT 'POINT(-6.2222 53.307)'::geography as geom) b 
  where ST_DWithin(a.way, b.geom, 1000)
) c
order by d, name;

But I think I would write it more simply like:

  SELECT ST_Distance(a.way,b.geom) as d, a.name from 
      (SELECT way::geography, name from ga_osm_latlong_polygon) a, 
      (SELECT 'POINT(-6.2222 53.307)'::geography as geom) b 
  where ST_DWithin(a.way, b.geom, 1000)
order by ST_Distance(a.way,b.geom), name;

But the first version might be faster because avoiding ST_Distance to run twice.

But to get this working well of course you will need the spatial index Now when I write it I realize that the casting to geography might be a showstopper for the index. If so, I would suggest you make a geography column instead and build a proper index on that. A working index makes a difference like night and day here.

Update: Or maybe you can create the index with the geography type directly. I have not tried, but might be worth trying: like this:

Create index idx_polygon_geog
on ga_osm_latlong_polygon
using gist(way::geography);

HTH

Nicklas

Column aliases are not visible in the WHERE clause. You can rewrite the query like this:

SELECT * FROM
 (
  SELECT name,
          (ST_Distance( 
            ST_Transform( way,900913 ),
            ST_Transform( ST_GeomFromText('POINT (-6.2222  53.307)',4326),900913 ))) 
      AS d
  FROM  ga_osm_latlong_polygon
 ) 

AS tmp    
WHERE ( d <= 1000 ) 
ORDER BY d, name

Also, I am not sure if PostGIS can take advantage of a spatial index in your ST_Distance() predicate. According to the current docs:

http://postgis.refractions.net/documentation/manual-1.5/ch04.html#id2638955

ST_Distance() includes a implicit bounding box check (which can be done against the index) from version 1.3 on, BUT you are checking way AFTER a ST_Transform, which means that the query has to scan all rows. Could try to rewrite the query leaving way in its original SRID? Like ST_Distance(way,ST_Transform(...,<srid of way>))?