4

I am working on a query to find the closest point between polygon and point

Can I use this operator to calculate the distance:

SELECT a.point, b.poly FROM tableA a, tableB b 
WHERE ST_Distance_Sphere( ST_Transform(a.point,4269), ST_Transform(b.poly,4269)) < 50 
ORDER BY a.point<-> b.poly
LIMIT 1;

But it run and take a very long time without results or error message.

  • There doesn't appear to be a question here (no question mark, and not obvious what you need). Can you click edit below the question and update it with details of what you'd like to know? – BradHards Feb 11 '14 at 8:50
  • Can you please define what you mean by 'closest point between polygone and point'? An Image might help us understand what you want to achieve. – Devdatta Tengshe Feb 11 '14 at 12:02
7

ST_Distance_Sphere is implemented only for points.

I suggest you to use ST_Distance instead, which provides the minimum distance between two bi-dimensional geometries of any type.

I've performed a similar test with two tables (point vs polygon), using this code:

SELECT a.gid point_gid, b.gid polygon_gid, (SELECT ST_Distance(a.geom, b.geom)) distance
    FROM harta_gal_etichete a, harta_gal_parcele b 
    WHERE (SELECT ST_Distance(a.geom, b.geom) < 50)
    ORDER BY a.gid;

If you have really big tables (spatial indexed), you'll have to wait a few minutes to obtain a result like this:

enter image description here


EDIT:

Thanks to the comments I was able to reproject my code and to achieve a significant reducing of the (same data set) processing time (from 269 seconds to 10):

-- Reproject the point table geometry
CREATE TABLE public.harta_gal_etichete_4296 AS 
    SELECT ST_Transform(geom, 4269) AS geom 
    FROM harta_gal_etichete;

-- Reproject the polygon table geometry
CREATE TABLE public.harta_gal_parcele_4296 AS 
    SELECT ST_Transform(geom, 4269) AS geom 
    FROM harta_gal_parcele;

-- Compute the distances between the two geometries
SELECT a.gid point_gid, b.gid polygon_gid, (SELECT ST_Distance(a.geom, b.geom)) distance
    FROM public.harta_gal_etichete_4296 a, public.harta_gal_parcele_4296 b 
    WHERE ST_DWithin(a.geom, b.geom, 50)
    ORDER BY a, b

enter image description here

I choosed this solution because using ST_Transform for 205496 rows last forever.

Full credit goes to "How to reproject all geometries in a PostGIS table", Taber and Jakub Kania

  • 2
    I've always heard than "ST_Dwithin" is faster than "ST_Distance" in this case... Am I wrong? – Taber Feb 11 '14 at 9:12
  • I guess you are right! You have a vote from me! Let's hope the user don't want to see the distances! – Sorin Călinică Feb 11 '14 at 9:19
  • 1
    You probably should address the most important issue that is indexing, he's using ST_TRANSFORMafter all. – Jakub Kania Feb 11 '14 at 9:24
3

Try ST_Within, it's return a boolean, and it's faster.

Something like:

SELECT a.point, b.poly FROM tableA a, tableB b 
WHERE ST_Within( ST_Transform(a.point,4269), ST_Transform(b.poly,4269), 50) 
ORDER BY a.point<-> b.poly
LIMIT 1;

Not the answer you're looking for? Browse other questions tagged or ask your own question.