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