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.

closed as unclear what you're asking by BradHards, vinayan, PolyGeo, Devdatta Tengshe, neogeomat Feb 11 '14 at 12:28

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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.