Distance between Polygon and Point

Question

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 08: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

score 8 · Answer 1 · edited Apr 13 '17 at 12:34

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

I've always heard than "ST_Dwithin" is faster than "ST_Distance" in this case... Am I wrong? — Taber, Feb 11 '14 at 09: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 09:19
You probably should address the most important issue that is indexing, he's using ST_TRANSFORMafter all. — Jakub Kania, Feb 11 '14 at 09:24
but this returns all polygons within 50m of each point. He asks for just the closest. — Theo F, Jun 29 '20 at 15:55

score 3 · Answer 2 · answered Feb 11 '14 at 09:15

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;

answered Feb 11 '14 at 09:15

Taber

619
4
11

Distance between Polygon and Point

2 Answers2