0

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?

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.

  • Sorry, thats not well put. What I mean by "a house next to it" is a terraced house. Multiple houses attached to each other. As opposed to for instance a villa which stands alone with no other houses/appartments attached to it. – user30058 May 18 '16 at 14:16
  • So ST_Intersects should work... Did you try ? (Better to test with a small subset of data) – WKT May 18 '16 at 14:19
  • Thanks, and I am trying as we speak. I put the limit on 10 and even after 10 minutes it is still running. Do you think that is normal? – user30058 May 18 '16 at 14:21
  • Did you perform an VACUUM ANALYZE ? Nevertheless it could be normal (as it need to find 10 intersecting polygons) and you may consider to extract some polygons just to test your query. – WKT May 18 '16 at 14:32
  • 1
    Also "CREATE INDEX house_gix ON houses USING GIST (geometry)" – Paul Ramsey May 18 '16 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.