Zip Code Column Empty

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

rmudie96
Posts: 1
Joined: Fri May 21, 2021 2:02 pm

Zip Code Column Empty

Post by rmudie96 » Fri May 21, 2021 2:10 pm

Hi,

I'm trying to extract the zip codes for a number of patents from PATSTAT and it seems everything is empty. My code is this:

SELECT tls209_appln_ipc.appln_id, ipc_class_symbol,
tls201_appln.appln_auth, tls201_appln.appln_id, appln_filing_year,
tls206_person.person_ctry_code, tls206_person.psn_sector, tls206_person.psn_name, tls206_person.psn_level,
tls226_person_orig.zip_code, city, state
FROM tls209_appln_ipc
LEFT JOIN tls201_appln ON tls209_appln_ipc.appln_id = tls201_appln.appln_id
LEFT JOIN tls207_pers_appln ON tls201_appln.appln_id = tls207_pers_appln.appln_id
LEFT JOIN tls206_person ON tls207_pers_appln.person_id = tls206_person.person_id
LEFT JOIN tls226_person_orig ON tls206_person.person_id = tls226_person_orig.person_id
WHERE tls206_person.person_ctry_code = 'GB'
AND (ipc_class_symbol LIKE 'H04M 3%'
OR ipc_class_symbol LIKE 'H04M 4%'
OR ipc_class_symbol LIKE 'H04M 5%')

When I view the results, the zip codefield is completely empty despite there being thousands of patents listed. I'm wondering a) if potentially this data is stored somewhere else in the system, so I just need to change my code to reflect that, if b) this is a unique GB issue as we call them post codes, or c) if zip codes just genuinely arent stored in the system despite the variable being there

The zip code is completely fundamental to the analysis I'm doing (they will be my nodes for a patent network analysis), so I'm needing to find out where they are in the system and if they're not anywhere, what to try next

Thanks!


EPO / PATSTAT Support
Posts: 256
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Zip Code Column Empty

Post by EPO / PATSTAT Support » Fri May 21, 2021 4:09 pm

Hello Rmudie96,

the reason why your query does not return results is the missing spaces in the IPC codes.
There should always be 8 characters before the position of the slash (which is position 9)
The query below will give you data.
However, keep in mind that a) addresses for physical persons have been removed from PATSTAT online b) there in principle only addresses available for EP applications.
We do however have a reasonable good coverage on the NUTS codes. I thinks that might be sufficient (and easier) for you to work with instead of the postal codes.
Check other forum posts on NUTS.

Code: Select all

SELECT tls209_appln_ipc.appln_id, ipc_class_symbol,
tls201_appln.appln_auth, tls201_appln.appln_id, appln_filing_year,
tls206_person.person_ctry_code, tls206_person.psn_sector, tls206_person.psn_name, tls206_person.psn_level,
tls226_person_orig.zip_code, city, state, nuts
FROM tls201_appln 
JOIN tls207_pers_appln ON tls201_appln.appln_id = tls207_pers_appln.appln_id
JOIN tls206_person ON tls207_pers_appln.person_id = tls206_person.person_id
JOIN tls226_person_orig ON tls206_person.person_id = tls226_person_orig.person_id
JOIN tls209_appln_ipc on tls201_appln.appln_id = tls209_appln_ipc.appln_id
WHERE tls206_person.person_ctry_code = 'GB'
AND (ipc_class_symbol LIKE 'H04M   3%'
OR ipc_class_symbol LIKE 'H04M   4%'
OR ipc_class_symbol LIKE 'H04M   5%')
Geert BOEDT
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply