1

I have two tables from Open Baltimore Dataset - Vital Signs (Housing and Community Development) and Housing Typology 2008. I am trying to find correlation between two geometries present in those datasets. Data information:

Vital Signs: 55 rows

Housing Typology: 710 rows

I want to find which row of Housing Typology belongs to Vital Signs. And both use different SRIDs as well:

SELECT Find_SRID('baltimore', 'vs14_housing', 'geom');
> 102685
SELECT Find_SRID('baltimore', 'housing_typology_2008', 'geom');
> 2248

And when I executed the following query:

select 
a.gid as Vital_Sign_Index, b.gid as Housing_Typology_Index
from
    baltimore."housing_typology_2008" b, baltimore."vs14_housing" a 
    where
    ST_Intersects(ST_Transform(a.geom, 2248), b.geom)
    order by b.gid

It returns something like this:

vital_sign_index  housing_typology_index    
51  1
55  1
51  2
55  2
55  3
51  3
55  4
51  4
55  5
51  5
51  6

Put I want to do one on one mapping rather than many to one based on maximum intersection with that geometry. So in simple words, I want to group by based on maximum overlapping distance.

Currently it returns 1255 records due to overlapping regions while I want it to return 710 (Numbers of Housing Typology rows).

2
  • You seem to be conflating mulipolygon and one-to-many relationships -- the two are not related. Please edit your title to focus on the question. Have tried using a GROUP BY in your query?
    – Vince
    Nov 26, 2016 at 12:43
  • 1
    I've read your question 3 times and still am not clear what exactly you're trying to calculate. Maybe draw up a picture. Nov 26, 2016 at 23:03

1 Answer 1

1

The below query will give you at most one vital_sign_index for each housing topology. It will give you the vital index such that the intersection is biggest for that topology. The NOT ST_Touches is just to weed out those where the boundaries touch but no area intersects since intersection is a much costlier process than relation checks.

   SELECT DISTINCT ON(b.gid)
        a.gid as Vital_Sign_Index, b.gid as Housing_Typology_Index
     FROM
       baltimore.housing_typology_2008 b
              INNER JOIN    baltimore.vs14_housing a 
        ON (
           ST_Intersects(ST_Transform(a.geom, 2248), b.geom) 
     AND NOT ST_Touches(ST_Transform(a.geom, 2248), b.geom) )
        ORDER BY b.gid,    
        ST_Area( ST_Intersection(ST_Transform(a.geom, 2248), b.geom) ) DESC;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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