[postgis-users] nearest polygon

[charles.blankenship at gmail.com]

Thank you kindly.  That works if I remove the ".description" part
since I do not have such a column.

Two really dumb questions.

1) Can I transform the tables to another projection (like national
atlas) within the query itself, or do I have to create a new
reprojected table (or reproject the current table itself)?

2) I have the data in two distinct tables/layers.  If I alter the
sample query like such, "FROM tableA As g1, tableB As g2", the
 query seems to keep running indefinitely.  Do I need to create a new
table that has the geometries of both tables?

Again I apologize for my lack of knowledge.

Charles

On Mon, Jun 8, 2009 at 9:31 PM, Paragon Corporation <lr at pcorp.us> wrote:
>
> 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_nearest_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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>