1

I am working to match between the closest points of two tables. I have two tables: Building, SchoolBuilding.

Building Table: Contains Polygones,
SchoolBuilding Table: contains Points (From Lat/Long).

I'd like to match between the both tables, and find the closest building that are "Schools" Following this article: http://blog.jordogskog.no/tag/st_closestpoint/

I wrote this query, but I couldn't get any results:

Select ST_ClosestPoint(a.geom, b.wkb_geometry) as the_geom from  Building a,SchoolBuilding b  LIMIT 1;

I tried to figure out where the problem is, I found that I am comparing between Point and MULTIPOLYGON

select  ST_GeomFromText(st_astext(wkb_geometry),4326) from Building limit 1;
                                                                                                   st_geomfromtext                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 0106000020E610000001000000010300000001000000050000007FB28F50DF1B2E4109B480B8E0FF0641025A2A2D821B2E41F6C3ACF40AFD064104B150BD531B2E41FC0B28E369FD06417F088BE0B01B2E410FFCFBA63F0007417FB28F50DF1B2E4109B480B8E0FF0641
(1 row)

--

select st_astext(wkb_geometry) from Building limit 1;                                                                                                                                                          st_astext                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 MULTIPOLYGON(((986607.657346323 188412.090089232,986561.088213742 188321.369470149,986537.869756252 188333.235916227,986584.438560739 188423.95653531,986607.657346323 188412.090089232)))


select st_astext(geom) from SchoolBuilding limit 1;
                 st_astext                 
-------------------------------------------
 POINT(-73.7785887529997 42.6453684010005)

The question is: Can we find the nearest point comparing MULTIPOLYGON ?

  • ST_ClosestPoint() is designed to accept just two geometries, which can be either multipart or singlepart. For the pair of geometries, it will find the closest pair of vertices and return the vertex of the second geometry. As you want to find the closest Point from among a number of Points, if you want to use ST_ClosestPoint, you'll need to make a single MultiPoint geometry from your Points to use as the comparison feature. – Arthur Feb 4 '14 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.