All geek questions in one place

ST_Distance and 'as' in Postgis

I wrote this query and it works, although it is 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 am trying to rewrite it in a more elegant way using 'as':

 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 WHERE ( d <= 1000 ) ORDER BY d, name

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

Any idea what I'm doing wrong here?

Thanks!

+4
source share
2 answers

Hallo mulone

About your problem with pseudonyms, Luther is right.

There are two reasons for slow query.

First you convert every point that takes time

Second and probably more importand, you should use ST_Dwithin along with the spatial index instead of using distance in the where clause.

ST_Dwithin uses spatial indexes to sort many calculations.

But projecting your data onto these calculations will give you very inaccurate answers. Why not use geography features.

to 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 this more simply:

  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 may be faster, because avoid ST_Distance to run twice.

But for this to work well, of course, you will need a spatial index. Now that I am writing this, I understand that casting in geography can be an indicator of the index. If so, I would suggest that you instead create a geography column and create an appropriate index. The work index changes what is here day and night.

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

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

NTN

Niklas

+2
source

Column aliases are not visible in the WHERE clause. You can rewrite the query as follows:

 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'm not sure if PostGIS can use a spatial index in your ST_Distance () predicate. According to current documents:

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

ST_Distance () includes an implicit bounding block check (which can be done against the index) from version 1.3, but you check way AFTER ST_Transform, which means that the request must check all rows. Is it possible to rewrite the request, leaving way in the original SRID? How is ST_Distance(way,ST_Transform(...,<srid of way>)) ?

0
source

Source: https://habr.com/ru/post/1338137/

More articles:


All Articles