select neighbour polygons in sql

Question

I have a database of few milion records (all houses in my country). Now I want to select all records who have a neighbouring record (a house next to it) and give this a status 'T'. Each record has a geometry column, looking like this:

POLYGON(( 254059.737 593504.637 0.0, 254059.227 593500.0 0.0, 254059.216 593499.899 0.0, 254058.242 593500.0 0.0, 254057.914 593500.034 0.0, 254057.893 593500.0 0.0, 254057.807 593499.863 0.0, 254052.074 593490.692 0.0, 254052.182 593490.639 0.0, 254058.138 593490.002 0.0, 254057.932 593488.165 0.0, 254074.487 593486.413 0.0, 254075.981 593500.0 0.0, 254076.265 593502.583 0.0, 254076.561 593502.55 0.0, 254076.594 593502.847 0.0, 254059.737 593504.637 0.0))

Is there a possibility to do this with an sql statement? I have never used SQL so I am a complete rookie. A detailled explanation is preferred.

I started with:

SELECT id, geometry, 'T'::text as type 
FROM houses 
WHERE

I think I have to add some intersect function at the WHERE clause?

Answer 1

Not sure what do you mean with "a house next to it" but here an example of what you can try:

SELECT a.id, a.geometry, 'T'::text as type 
FROM houses a, houses b 
WHERE ST_Intersects(a.geometry,b.geometry) AND a.id != b.id

Could be done with other spatial operator (ST_DWithin could be a better candidate).
Better with Gist index on geometry field.