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).