How to retrieve Car Dealer POI listing from RDF using SQL query

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

Symptoms
--------

Customers may report one or more of the following:

Need to generate a complete list of car dealer POIs from RDF data.
Unable to identify all dealer locations belonging to a specific automotive brand.
Need dealer POI details including address, coordinates, phone number, website, and email.
Need to filter car dealer POIs by country or specific POI ID.
Difficulty retrieving POI contact information from RDF tables.
Need a consolidated export of dealer locations for analysis or validation.

Answer
------

To retrieve a comprehensive list of car dealer POIs from RDF, use the SQL query provided below and replace the placeholder chain IDs with the chain ID(s) corresponding to the desired vehicle manufacturer or dealer brand. The query extracts available POI attributes, including location, address, contact details, brand information, and category metadata from RDF tables.

This method is intended for RDF environments where direct database access is available and can be narrowed to a specific country using ISO_COUNTRY_CODE filters or to an individual POI using POI_ID filters

Root Cause
----------

Car dealer information is distributed across multiple RDF tables, including:

RDF_POI
RDF_POI_CHAINS
* RDF_CHAIN
* RDF_CHAIN_NAME
* RDF_POI_ADDRESS
* RDF_LOCATION
* RDF_POI_CONTACT_INFORMATION
* RDF_FEATURE_NAME tables

Querying a single table does not provide complete dealer information because attributes such as chain affiliation, address, contact information, and location coordinates are stored separately. A multi-table join is required to produce a comprehensive dealer listing.

Example 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

;

Search Keywords / Tags
----------------------

RDF, POI, Car Dealer, Automotive Dealer, POI Listing, Chain ID, RDF_POI, RDF Chain, SQL Query, Dealer Export, POI Contact Information, RDF Database, Vehicle Dealer Search, Dealer Location Data, POI Extraction