How to retrieve car dealer POI information / listing from RDF

Car Dealer POI information: How to quickly create a comprehensive listing from RDF data using a SQL-Query.

The following SQL-Query can be used to retrieve a comprehensive list.

All POI information available is accessible using this SQL query.

In this query replace the Chain ID(s) XXX with the Chain ID of the car brand.

POI CAT_IDs are (5511, 5000, 7538, 9719).

The query can be limited by defining per ISO_COUNTRY_CODE, or by searching only per POI_ID.

--step1:

create table tmp_t100
as
SELECT
distinct
rpns.POI_ID,
rp.cat_id,
rcn.NAME brand_name,
rpa.ISO_COUNTRY_CODE AS ISO,
rl.lat, rl.lon,
CASE
WHEN rpnts.NAME IS NOT NULL
THEN rpnts.NAME
ELSE rpn.NAME
END AS poi_name,
CASE
WHEN rpat.STREET_NAME IS NOT NULL
THEN rpat.STREET_NAME
ELSE rpa.STREET_NAME
END AS poi_streetname,
rpa.HOUSE_NUMBER poi_housenr,
rpa.POSTAL_CODE postal_code,
CASE
WHEN rfnst.NAME IS NOT NULL
THEN rfnst.NAME
ELSE rfn.NAME
END AS City,
rpa.ACTUAL_ADDRESS VANITY_ADDRESS
FROM RDF_POI rp
inner join RDF_POI_CHAINS rpcs on rp.POI_ID = rpcs.POI_ID
inner join RDF_CHAIN rc on rc.CHAIN_ID = rpcs.CHAIN_ID
inner join RDF_CHAIN_NAME rcn on rcn.CHAIN_ID = rc.CHAIN_ID
inner join RDF_POI_ADDRESS rpa on rpa.POI_ID = rp.POI_ID
left outer join RDF_POI_ADDRESS_TRANS rpat on rpat.POI_ID = rpa.POI_ID
inner join RDF_LOCATION rl on rl.LOCATION_ID = rpa.LOCATION_ID
inner join RDF_POI_NAMES rpns on rp.POI_ID = rpns.POI_ID
inner join RDF_POI_NAME rpn on rpns.NAME_ID = rpn.NAME_ID
left outer join RDF_POI_NAME_Trans rpnts on rpn.NAME_ID = rpnts.NAME_ID
inner join RDF_FEATURE_NAMES rfns on rfns.feature_id = rpa.order8_id
inner join RDF_FEATURE_NAME rfn on rfn.name_id = rfns.name_id
left outer join RDF_FEATURE_NAME_TRANS rfnst on rfn.NAME_ID = rfnst.NAME_ID
WHERE
rp.CAT_ID IN (5511, 5000, 7538, 9719)
AND rcn.LANGUAGE_CODE = 'ENG'
AND rc.CHAIN_ID IN (XXX, XXX, XXX)
AND rfns.IS_EXONYM = 'N'
AND rfns.NAME_TYPE = 'B'
;

--step 2:

select distinct t1.*,

rm.ATTR_DESCRIPTION feat_name, rm.*,

rpci_ct1.CONTACT as telephone,

rpci_ct1.CONTACT_TYPE as telcontype,

rpci_ct1.PREFERRED as telprefered,

rpci_ct1.PHONE_LOCAL_NUMBER as tel_phone_local_number,

rpci_ct1.PHONE_AREA_CODE as tel_phone_area_code,

rpci_ct2.contact as toll_free_phone_number,

rpci_ct5.contact as mobile_phone_number,

rpci_ct3.contact as url,

rpci_ct4.contact as email

from tmp_t100 t1

inner join RDF_META rm on rm.ATTRIBUTE = t1.CAT_ID::varchar(255)

left outer join RDF_POI_CONTACT_INFORMATION rpci_ct1 on rpci_ct1.POI_ID = t1.POI_ID and rpci_ct1.CONTACT_TYPE = '1'

left outer join RDF_POI_CONTACT_INFORMATION rpci_ct2 on rpci_ct2.POI_ID = t1.POI_ID and rpci_ct2.CONTACT_TYPE = '2'

left outer join RDF_POI_CONTACT_INFORMATION rpci_ct3 on rpci_ct3.POI_ID = t1.POI_ID and rpci_ct3.CONTACT_TYPE = '3'

left outer join RDF_POI_CONTACT_INFORMATION rpci_ct4 on rpci_ct4.POI_ID = t1.POI_ID and rpci_ct4.contact_type = '4'

left outer join RDF_POI_Contact_information rpci_ct5 on rpci_ct5.POI_ID = t1.POI_ID and rpci_ct5.CONTACT_TYPE = '5'

where

rm.TABLE_NAME = 'RDF_POI'

AND rm.COLUMN_NAME = 'CAT_ID'

AND rm.LANGUAGE_CODE = 'ENG'

order by t1.poi_id

;