[postgis-users] nested query to make adjacency list?

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Jeff D. Hamann
> Sent: 20 December 2005 08:05
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] nested query to make adjacency list?
> 
> I've been in the process of moving an application that uses Arc/Info files
> to one that uses PostGIS completely! I need to generate an adjacency
> list/matrix and have been able perform the query for a single polygon,
> 
> -- build a temp table of the first polygon, only.
> select * into table first_one from test where gid = 1;
> 
> -- now create a adjacency list for the first polygon
> select
>  first_one.gid as gid,
>  first_one.Standid as stid,
>  test.gid as adj_gid,
>  test.Standid
> from
>  test,
>  first_one
> where
>  touches( first_one.the_geom, test.the_geom );
> 
> -- boo-yah!
> 
>  gid |  stid  | adj_gid | standid
> -----+--------+---------+---------
>    1 | 010101 |       2 | 010115
>    1 | 010101 |       6 | 010103
>    1 | 010101 |       7 | 010105
>    1 | 010101 |       8 | 010113
> (4 rows)
> 
> This yields the correct results.
> 
> Is it possible to nest this query so that I can create this for every
> polygon all at once?
> 
> Jeff.


Hi Jeff,

You may be able to get the results you need by joining your test table back
onto itself. I'm more of an SQL person rather than a GIS person, but I
imagine the query should look something like this:


 select
  t1.gid as gid,
  t1.standid as stid,
  t2.gid as adj_gid,
  t2.standid
 from
  test t1,
  test t2
 where
  touches( t1.the_geom, t2.the_geom );


Also if you have a large number of geometries then you should get much
better performance by adding a spatial index to the_geom column, doing
VACUUM ANALYZE, and using the && operator to further check only geometries
whose bounding box overlaps on each iteration, e.g.

select
  t1.gid as gid,
  t1.standid as stid,
  t2.gid as adj_gid,
  t2.standid
 from
  test t1,
  test t2
 where
  touches( t1.the_geom, t2.the_geom ) AND t1.the_geom && t2.the_geom;



Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.