Na importação das moradas do INE, estava a perder o “nome do edifício”, que é importante. Nalguns casos, os endereços não têm número, mas têm “nome do edifício”, como é ilustrado na imagem seguinte (em Beja).

Assim sendo, a query para passar das múltiplas tabelas criadas a partir do GML para uma única tabela passa a ser:

create table moradas as 
with designator as ( 
	select al.parent_ogr_pkid, json_object_agg(regexp_replace(ld.locatordesignator_type_href, 'http://inspire.ec.europa.eu/codelist/LocatorDesignatorTypeValue/(.*)Identifier', '\1'), 
	ld.locatordesignator_designator) as designator 
	from address_locator al, address_locator_addresslocator_designator ld 
	where al.ogr_pkid = ld.parent_ogr_pkid 
	group by al.parent_ogr_pkid, ld.parent_ogr_pkid), 
buidname as (
	select al.parent_ogr_pkid, gns.spellingofname_text
	from address_locator al, address_locator_addresslocator_name alan, address_locator_addresslocator_name_locatorname_name alanln, geographicalname gn, geographicalname_spelling gns 
	where al.ogr_pkid = alan.parent_ogr_pkid and
	alan.ogr_pkid =alanln.parent_ogr_pkid and
	alanln.geographicalname_pkid = gn.ogr_pkid and 
	gn.ogr_pkid = gns.parent_ogr_pkid),
position as (
	select ap.parent_ogr_pkid, ap.geographicposition_geometry 
	from address_position ap), 
postcode as (
	select parent_ogr_pkid, pd.postcode 
	from address_component ac left join postaldescriptor pd on ac.href_postaldescriptor_pkid = pd.ogr_pkid 
	where href_postaldescriptor_pkid is not null), 
toponimo as (
	with tfnp as (
		select parent_ogr_pkid, json_object_agg( regexp_replace(partofname_type_href , 'http://inspire.ec.europa.eu/codelist/PartTypeValue/(.*)', '\1'), partofname_part) as partofname 
		from thoroughfarename_ad_name_thoroughfarenamevalue_nameparts 
		group by parent_ogr_pkid) 
	select ac.parent_ogr_pkid, tfnp.partofname 
		from address_component ac left join thoroughfarename tf on ac.href_thoroughfarename_pkid = tf.ogr_pkid 
		left join thoroughfarename_ad_name tfn on tf.ogr_pkid = tfn.parent_ogr_pkid 
		left join tfnp on tfnp.parent_ogr_pkid = tfn.ogr_pkid where href_thoroughfarename_pkid is not null) 
select a.ogr_pkid, p.geographicposition_geometry, d.designator->>'building' as numero, d.designator->>'floor' as piso, d.designator->>'unit' as fracao, 
	t.partofname->>'name' as toponimo, t.partofname->>'type' as tipo, buidname.spellingofname_text as edificio, pc.postcode 
	from address a, position p, designator d, toponimo t, postcode pc, buidname
where a.ogr_pkid = p.parent_ogr_pkid and a.ogr_pkid = d.parent_ogr_pkid and a.ogr_pkid = pc.parent_ogr_pkid and a.ogr_pkid = t.parent_ogr_pkid and a.ogr_pkid = buidname.parent_ogr_pkid;