[postgis-users] nearest polygon

Michael Smedberg Michael.Smedberg at redfin.com
Tue Jun 9 08:51:20 PDT 2009


Here's another option for calculating nearby regions:

 

1. Buffer out the polygon you're interested in to make a bigger polygon

2. Find all the polygons that overlap with the buffered polygon
(ignoring the original polygon- everything is near itself!)

3. Order the results by the distance between the centroids of the
polygons

 

I do this in Java/Hibernate with the following SQL:

 

First, calculate the buffered polygon and the centroid of that polygon:

 

SELECT

 
ST_AsText(ST_Buffer(ST_Transform(ST_Buffer(ST_Transform(polygon_data,
2163), 100), 4326), 0)) AS buffered_poly,

            ST_Centroid(polygon_data) AS centroid

FROM ...

 

where 2136 is an SRID in meters, 4326 is the SRID that our polygons are
stored in, and 100 is the number of meters to buffer by.

 

Second, find the polygons that overlap with that buffered polygon:

 

SELECT 

            [stuff],

            st_distance_sphere(st_centroid(poly_table.polygon),
ST_GeomFromText(:centroid, 4326)) AS dist

FROM

            [tables]

WHERE ST_Intersects(

                        ST_GeomFromText(:bufferedPoly, 4326),

                        poly_table.polygon

            )

            AND [other constraints]

ORDER BY

            dist ASC

 

where poly_table.polygon are the polygons we're looking for, :centroid
is the centroid we got from the previous query, :bufferedPoly is the
buffered polygon we got from the previous query, and 4326 is again the
SRID that our polygons are stored in.  Note that [other constraints]
should include a clause to ignore the source polygon.

 

These two SQL statements can be combined into a single SQL statement,
but in our case it was more performant to calculate the buffered polygon
one time and use it multiple times.

 

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Paragon Corporation
Sent: Monday, June 08, 2009 9:31 PM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] nearest polygon

 

Charles,

 

If you just need the 1 nearest neighbor for each result.  Use distinct
ON as described here
http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_neare
st_neighbor

 

SELECT DISTINCT ON(g1.gid)  g1.gid As gref_gid, g1.description As
gref_description, g2.gid As gnn_gid, 
        g2.description As gnn_description  
    FROM sometable As g1, sometable As g2   
    WHERE g1.gid <> g2.gid AND ST_DWithin(g1.the_geom, g2.the_geom, 300)

    ORDER BY g1.gid, ST_Distance(g1.the_geom,g2.the_geom) 

For the 300 you want to change that to the max distance you expect the
nearest neighbor for any record will be.  If you are using census data,
you wil also want to transform that from long lat 4269 to some other
projection like 2163 (National Atlas US).

 

You can also use the array approach we describe in the above article to
have nearest neighbors in columns instead of as separate rows (a sort of
distance cross tab if you will).

 

The generic solution we described below is more for returning multiple
near neighbors per record, and there is a bit of a bug in it that it
doesn't handle non-point geomtries quite accurately.  We are working on
fixing this and still maintaining performance and also updating it to
use the new PostGIS functions.

 

 

Leo

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
charles.blankenship at gmail.com
Sent: Monday, June 08, 2009 9:02 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] nearest polygon

Hi,

I'm  very new to GIS and SQL so this is a naive question.

I have some boundary files from the US census containing school
districts and urban areas.

I would like to find the nearest, or if possible several nearest, urban
areas for each district.

I know this type of problem can get really complicated, but I need a
fairly quick and easy solution if one exists.  I could limit this to
centroid to centroid distances if that makes a big difference.


I found the following link which seems like it might do what I need.
http://www.bostongis.com/?content_name=postgis_nearest_neighbor_generic#
130


Can anyone tell me if the solution above is the best way to go about
this?  Or, if there's a simpler "quick and easy" way, that would be
helpful as well.


Thanks,
Charles

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090609/eb5fda35/attachment.html>


More information about the postgis-users mailing list