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 <[email protected]> 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: [email protected] > [mailto:[email protected]] On Behalf Of > [email protected] > Sent: Monday, June 08, 2009 9:02 PM > To: [email protected] > 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
