[postgis-users] Nearest neighbor spatial join (more questions)

[Sheara Cohen]

Hey all and Mark -

 

Mark, thanks again for your suggestions. I am having trouble making it
work, and I'm wondering if you or anyone else has any suggested fixes to
any of the following scripts. These were my various failed attempts:

 

Take One:

[WARNING  ] CREATE TABLE
public.census_bg_near_ids_for_du_equal_zero_test_062111

            as 

                SELECT DISTINCT ON(o.ogc_id)  

                o.ogc_id As orig_ogc_id,

                o.sf3_totalhu_h030001 As sf3_totalhu_h030001,

                o.st_pointfromtext As st_pointfromtext,

                n.ogc_id As near_ogc_id,

                n.sf3_totalhu_h030001 As near_sf3_totalhu_h030001,

                FROM census_bg_du_equal_zero_test_062111 As o,
census_bg_du_more_than_zero_test_062111 As n   

                WHERE o.ogc_id <> n.ogc_id AND ST_DWithin(o.the_geom,
n.the_geom, 25000);

            

Take Two:           

[WARNING  ] CREATE TABLE
public.census_bg_near_ids_for_du_equal_zero_test_062111

            as 

                SELECT 

                o.ogc_id As orig_ogc_id,

                o.sf3_totalhu_h030001 As sf3_totalhu_h030001,

                o.st_pointfromtext As st_pointfromtext,

                n.ogc_id As near_ogc_id,

                n.sf3_totalhu_h030001 As near_sf3_totalhu_h030001,  

                FROM census_bg_du_equal_zero_test_062111 As o,
census_bg_du_more_than_zero_test_062111 As n   

                WHERE o.ogc_id <> n.ogc_id AND ST_DWithin(o.the_geom,
n.the_geom, 25000)   

                LIMIT 1;

 

Maybe the "nn" in the field names of the following model script
something important?

 

Model One: 

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)

 

Model Two:
SELECT 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 = 1 and g1.gid <> g2.gid AND ST_DWithin(g1.the_geom,
g2.the_geom, 300)   
    ORDER BY ST_Distance(g1.the_geom,g2.the_geom) 
    LIMIT 5

 

Thank you again!


~ Sheara

 

Sheara Cohen
Planner

C A L T H O R P E  A S S O C I A T E S
2095 ROSE STREET, SUITE 201, BERKELEY, CALIFORNIA, 94709 USA
510 809-1165 (direct) | 510-548-6800 x35 (main) | 510 548-6848 (fax)
sheara at calthorpe.com <mailto:sheara at calthorpe.com>  | www.calthorpe.com

 

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