| Author |
Message |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 16/11/2009 00:09:23
|
artigas
Joined: 15/11/2009 17:09:42
Messages: 88
Offline
|
Greetings -
I have worked out a bit of PostgreSQL 8.3 SQL to allow individuals that want to research the linking of geocode_id to postal code a bit of a start. You can try this out, make changes and experiment until you can get pretty close and a judgment call is needed.
Record type '1' starts at the places, works through the names, and matches to a postal code. Useful for things like Russia since the places are in a European type code page and the postal is in Cyrillic type code page.
Record type '2' starts at the postal, matches to the places and adds in names. It matches the country, admin1, and admin2.
Record type '3' is the places match to names with the iso_language = 'post' for postal codes. I though it useful to show these to see how many additional items got matched with 1) and 2)
Look at the query. It takes all the UNIONed records and finds the difference between the places lat/lon and postal lat/lon giving you a measure of confidence. If there is a big difference you will have to look at a map to check out if it is correct. The postal lat/lon might be a centroid that does not match the exact location of the place with the same name.
Use this as a view. Select on that view by country and if it looks pretty good create the alternative names records with the 'post'. You might be able to add in the admin1 and admin2 codes.
Please note that I select only on the 'P' this allows the smaller countries (AD for instance) to match properly.
Code:
----------
SELECT
(CASE WHEN (place_latitude - postal_latitude) = 0.0000
THEN null::numeric
ELSE place_latitude - postal_latitude END
) AS diff_lat,
(CASE WHEN (place_longitude - postal_longitude) = 0.0000
THEN null::numeric
ELSE place_longitude - postal_longitude END
) AS diff_lon,
x_all.*
FROM (
SELECT * FROM (
SELECT DISTINCT ON (gpostal.country_code, gpostal.postal_code, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code)
'1'::text AS record_code,
-- gplaces.*
gplaces.geoname_id AS geoname_id,
trim(gplaces.full_name) AS full_name,
trim(gplaces.feature_class) AS feature_class,
trim(gplaces.feature_code) AS feature_code,
trim(gplaces.country_code) AS country_check,
trim(gplaces.admin1_code) AS admin1_check,
trim(gplaces.admin2_code) AS admin2_check,
round(gplaces.latitude, 4) AS place_latitude,
round(gplaces.longitude, 4) AS place_longitude,
-- gnames.*,
'm1->'::text AS m1,
gnames.geoname_name_id AS geoname_name_id,
trim(gnames.alternate_name) AS altername_name,
trim(gnames.iso_language) AS language_code,
-- gpostal.*,
'm2->'::text AS m2,
trim(gpostal.country_code) AS country_code,
trim(gpostal.postal_code) AS postal_code,
trim(gpostal.place_name) AS place_name,
trim(gpostal.admin1_code) AS admin1_code,
trim(gpostal.admin2_code) AS admin2_code,
trim(gpostal.admin1_name) AS admin1_name,
trim(gpostal.admin2_name) AS admin2_name,
trim(gpostal.admin3_name) AS admin3_name,
round(gpostal.latitude::numeric(13,8), 4) AS postal_latitude,
round(gpostal.longitude::numeric(13,8), 4) AS postal_longitude,
gpostal.accuracy AS accuracy
----------
FROM xwor.geoname_places AS gplaces
LEFT JOIN xwor.geoname_places_names AS gnames ON (gnames.geoname_id = gplaces.geoname_id)
LEFT JOIN xwor.geoname_places_postal AS gpostal ON (lower(gnames.alternate_name) = lower(gpostal.place_name) AND
gpostal.country_code = gplaces.country_code)
WHERE gpostal.place_name IS NOT NULL
AND gplaces.feature_class = 'P' --AND gplaces.feature_code = 'PPL'
ORDER BY gpostal.country_code, gpostal.postal_code, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code
) AS x_1
UNION
SELECT * FROM (
SELECT DISTINCT ON (gpostal.country_code, gpostal.postal_code, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code)
'2'::text AS record_code,
-- gplaces.*
gplaces.geoname_id AS geoname_id,
trim(gplaces.full_name) AS full_name,
trim(gplaces.feature_class) AS feature_class,
trim(gplaces.feature_code) AS feature_code,
trim(gplaces.country_code) AS country_check,
trim(gplaces.admin1_code) AS admin1_check,
trim(gplaces.admin2_code) AS admin2_check,
round(gplaces.latitude, 4) AS place_latitude,
round(gplaces.longitude, 4) AS place_longitude,
-- gnames.*,
'm1->'::text AS m1,
gnames.geoname_name_id AS geoname_name_id,
trim(gnames.alternate_name) AS altername_name,
trim(gnames.iso_language) AS language_code,
-- gpostal.*,
'm2->'::text AS m2,
trim(gpostal.country_code) AS country_code,
trim(gpostal.postal_code) AS postal_code,
trim(gpostal.place_name) AS place_name,
trim(gpostal.admin1_code) AS admin1_code,
trim(gpostal.admin2_code) AS admin2_code,
trim(gpostal.admin1_name) AS admin1_name,
trim(gpostal.admin2_name) AS admin2_name,
trim(gpostal.admin3_name) AS admin3_name,
round(gpostal.latitude::numeric(13,8), 4) AS postal_latitude,
round(gpostal.longitude::numeric(13,8), 4) AS postal_longitude,
gpostal.accuracy AS accuracy
----------
FROM xwor.geoname_places_postal AS gpostal
LEFT JOIN xwor.geoname_places AS gplaces ON (lower(gpostal.place_name) = lower(gplaces.full_name) AND
gpostal.country_code = gplaces.country_code AND
gpostal.admin1_code = gplaces.admin1_code AND
gpostal.admin2_code = gplaces.admin2_code)
LEFT JOIN xwor.geoname_places_names AS gnames ON (lower(gpostal.place_name) = lower(gnames.alternate_name) AND
gnames.geoname_id = gplaces.geoname_id)
WHERE gpostal.place_name IS NOT NULL
AND gplaces.feature_class = 'P' --AND gplaces.feature_code = 'PPL'
ORDER BY gpostal.country_code, gpostal.postal_code, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code
) AS x_2
UNION
SELECT * FROM (
SELECT
'3'::text AS record_code,
-- gplaces.*
gplaces.geoname_id AS geoname_id,
trim(gplaces.full_name) AS full_name,
trim(gplaces.feature_class) AS feature_class,
trim(gplaces.feature_code) AS feature_code,
trim(gplaces.country_code) AS country_check,
trim(gplaces.admin1_code) AS admin1_check,
trim(gplaces.admin2_code) AS admin2_check,
round(gplaces.latitude, 4) AS place_latitude,
round(gplaces.longitude, 4) AS place_longitude,
-- gnames.*,
'm1->'::text AS m1,
gnames.geoname_name_id AS geoname_name_id,
trim(gnames.alternate_name) AS altername_name,
trim(gnames.iso_language) AS language_code,
-- gpostal.*,
'm2->'::text AS m2,
trim(gplaces.country_code) AS country_code,
trim(gnames.alternate_name) AS postal_code,
trim(gplaces.full_name) AS place_name,
trim(gplaces.admin1_code) AS admin1_code,
trim(gplaces.admin2_code) AS admin2_code,
null::text AS admin1_name,
null::text AS admin2_name,
null::text AS admin3_name,
round(gplaces.latitude, 4) AS postal_latitude,
round(gplaces.longitude, 4) AS postal_longitude,
null::character(1) AS accuracy
----------
FROM xwor.geoname_places_names AS gnames
LEFT JOIN xwor.geoname_places AS gplaces ON (gnames.geoname_id = gplaces.geoname_id)
WHERE gplaces.feature_class = 'P' --AND gplaces.feature_code = 'PPL'
AND gnames.iso_language = 'post'
ORDER BY gplaces.country_code, gnames.alternate_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code
) AS x_3
) AS x_all
ORDER BY country_code, postal_code, geoname_id, record_code
;
----------
If you use postgreSQL you will just have to convert the scripts using your schema, table, and field names and you should be good to go. Any other type of database you will have to understand how to conver the script.
Since this is the first time for me with the geoname database, this script took about 8 hours to create and test. Each individual SQL queries first, and the combined SQL queries second. Experimentation takes time.
This will get it started, and since I have seen several questions about this topic, I am sure this will be of some assistance to some of the individuals that use this data set.
---PLEASE--- if you modify this for your particular SQL backend, discover a useful variation that matches more records, or make SQL changes to make this more efficient, please post back your SQL. The more individuals working on this problem, the quicker it will be solved.
Thanks In Advance.
|
Robert Artigas
|
|
|
 |
16/11/2009 05:21:40
|
artigas
Joined: 15/11/2009 17:09:42
Messages: 88
Offline
|
Greetings -
I have made a few corrections.
Code:
SELECT
(CASE WHEN (place_latitude - postal_latitude) = 0.0000
THEN null::numeric
ELSE place_latitude - postal_latitude END
) AS diff_lat,
(CASE WHEN (place_longitude - postal_longitude) = 0.0000
THEN null::numeric
ELSE place_longitude - postal_longitude END
) AS diff_lon,
abs(place_latitude - postal_latitude) AS abs_lat,
abs(place_longitude - postal_longitude) AS abs_lon,
x_all.*
FROM (
SELECT * FROM (
SELECT DISTINCT ON (gpostal.country_code, gpostal.postal_code, gpostal.place_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code)
'1'::text AS record_code,
-- gplaces.*
gplaces.geoname_id AS geoname_id,
trim(gplaces.full_name) AS full_name,
trim(gplaces.feature_class) AS feature_class,
trim(gplaces.feature_code) AS feature_code,
trim(gplaces.country_code) AS country_check,
trim(gplaces.admin1_code) AS admin1_check,
trim(gplaces.admin2_code) AS admin2_check,
round(gplaces.latitude, 4) AS place_latitude,
round(gplaces.longitude, 4) AS place_longitude,
-- gnames.*,
'm1->'::text AS m1,
gnames.geoname_name_id AS geoname_name_id,
trim(gnames.alternate_name) AS altername_name,
trim(gnames.iso_language) AS language_code,
-- gpostal.*,
'm2->'::text AS m2,
trim(gpostal.country_code) AS country_code,
trim(gpostal.postal_code) AS postal_code,
trim(gpostal.place_name) AS place_name,
trim(gpostal.admin1_code) AS admin1_code,
trim(gpostal.admin2_code) AS admin2_code,
trim(gpostal.admin1_name) AS admin1_name,
trim(gpostal.admin2_name) AS admin2_name,
trim(gpostal.admin3_name) AS admin3_name,
round(gpostal.latitude::numeric(13,8), 4) AS postal_latitude,
round(gpostal.longitude::numeric(13,8), 4) AS postal_longitude,
gpostal.accuracy AS accuracy
----------
FROM xwor.geoname_places AS gplaces
LEFT JOIN xwor.geoname_places_names AS gnames ON (gnames.geoname_id = gplaces.geoname_id)
LEFT JOIN xwor.geoname_places_postal AS gpostal ON (lower(gnames.alternate_name) = lower(gpostal.place_name) AND
gpostal.country_code = gplaces.country_code)
WHERE gpostal.place_name IS NOT NULL
AND gplaces.feature_class = 'P' --AND gplaces.feature_code = 'PPL'
ORDER BY gpostal.country_code, gpostal.postal_code, gpostal.place_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code
) AS x_1
UNION
SELECT * FROM (
SELECT DISTINCT ON (gpostal.country_code, gpostal.postal_code, gpostal.place_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code)
'2'::text AS record_code,
-- gplaces.*
gplaces.geoname_id AS geoname_id,
trim(gplaces.full_name) AS full_name,
trim(gplaces.feature_class) AS feature_class,
trim(gplaces.feature_code) AS feature_code,
trim(gplaces.country_code) AS country_check,
trim(gplaces.admin1_code) AS admin1_check,
trim(gplaces.admin2_code) AS admin2_check,
round(gplaces.latitude, 4) AS place_latitude,
round(gplaces.longitude, 4) AS place_longitude,
-- gnames.*,
'm1->'::text AS m1,
gnames.geoname_name_id AS geoname_name_id,
trim(gnames.alternate_name) AS altername_name,
trim(gnames.iso_language) AS language_code,
-- gpostal.*,
'm2->'::text AS m2,
trim(gpostal.country_code) AS country_code,
trim(gpostal.postal_code) AS postal_code,
trim(gpostal.place_name) AS place_name,
trim(gpostal.admin1_code) AS admin1_code,
trim(gpostal.admin2_code) AS admin2_code,
trim(gpostal.admin1_name) AS admin1_name,
trim(gpostal.admin2_name) AS admin2_name,
trim(gpostal.admin3_name) AS admin3_name,
round(gpostal.latitude::numeric(13,8), 4) AS postal_latitude,
round(gpostal.longitude::numeric(13,8), 4) AS postal_longitude,
gpostal.accuracy AS accuracy
----------
FROM xwor.geoname_places_postal AS gpostal
LEFT JOIN xwor.geoname_places AS gplaces ON (lower(gpostal.place_name) = lower(gplaces.full_name) AND
gpostal.country_code = gplaces.country_code AND
gpostal.admin1_code = gplaces.admin1_code AND
gpostal.admin2_code = gplaces.admin2_code)
LEFT JOIN xwor.geoname_places_names AS gnames ON (lower(gpostal.place_name) = lower(gnames.alternate_name) AND
gnames.geoname_id = gplaces.geoname_id)
WHERE gpostal.place_name IS NOT NULL
AND gplaces.feature_class = 'P' --AND gplaces.feature_code = 'PPL'
ORDER BY gpostal.country_code, gpostal.postal_code, gpostal.place_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code
) AS x_2
UNION
SELECT * FROM (
SELECT
'3'::text AS record_code,
-- gplaces.*
gplaces.geoname_id AS geoname_id,
trim(gplaces.full_name) AS full_name,
trim(gplaces.feature_class) AS feature_class,
trim(gplaces.feature_code) AS feature_code,
trim(gplaces.country_code) AS country_check,
trim(gplaces.admin1_code) AS admin1_check,
trim(gplaces.admin2_code) AS admin2_check,
round(gplaces.latitude, 4) AS place_latitude,
round(gplaces.longitude, 4) AS place_longitude,
-- gnames.*,
'm1->'::text AS m1,
gnames.geoname_name_id AS geoname_name_id,
trim(gnames.alternate_name) AS altername_name,
trim(gnames.iso_language) AS language_code,
-- gpostal.*,
'm2->'::text AS m2,
trim(gplaces.country_code) AS country_code,
trim(gnames.alternate_name) AS postal_code,
trim(gplaces.full_name) AS place_name,
trim(gplaces.admin1_code) AS admin1_code,
trim(gplaces.admin2_code) AS admin2_code,
null::text AS admin1_name,
null::text AS admin2_name,
null::text AS admin3_name,
round(gplaces.latitude, 4) AS postal_latitude,
round(gplaces.longitude, 4) AS postal_longitude,
null::character(1) AS accuracy
----------
FROM xwor.geoname_places_names AS gnames
LEFT JOIN xwor.geoname_places AS gplaces ON (gnames.geoname_id = gplaces.geoname_id)
WHERE gplaces.feature_class = 'P' --AND gplaces.feature_code = 'PPL'
AND gnames.iso_language = 'post'
ORDER BY gplaces.country_code, gnames.alternate_name, gplaces.full_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code
) AS x_3
) AS x_all
ORDER BY country_code, postal_code, place_name, geoname_id, record_code
;
|
Robert Artigas
|
|
|
|
16/11/2009 22:08:22
|
artigas
Joined: 15/11/2009 17:09:42
Messages: 88
Offline
|
Greetings -
Last post for me on this subject, until someone replies.
The following query gives the alternate names 'post' items that might be added, that do not seem to exist in the alternate name table.
There are a couple of constraints that make sure that postal lat/lon is close to the place lat/lon. It does not account for the postal codes that might not be close to the place (large geographic area or centroid is a weird shape). Again this is part of the course when doing research.
On the geoname database I have loaded, this returns 134,725 records. A probable use for this is to pick one country at time, see what matches, research what does not, and just do it. This SQL query will get you started.
There are comments on the SQL query so read and enjoy.
Code:
SELECT
base.diff_lat,
base.diff_lon,
base.abs_lat,
base.abs_lon,
base.record_code,
base.country_code,
--
-- Comment out the fields above to produce the 'alternate_names' table.
-- The fields bellow are one's that are inserted into the 'altername_names' table.
--
base.geoname_id AS geoname_id,
'post'::text AS iso_language,
base.postal_code AS alternate_name,
null::text AS is_preferred_name,
null::text AS is_short_name
--
FROM (
SELECT
(CASE WHEN (place_latitude - postal_latitude) = 0.0000
THEN null::numeric
ELSE place_latitude - postal_latitude END
) AS diff_lat,
(CASE WHEN (place_longitude - postal_longitude) = 0.0000
THEN null::numeric
ELSE place_longitude - postal_longitude END
) AS diff_lon,
abs(place_latitude - postal_latitude) AS abs_lat,
abs(place_longitude - postal_longitude) AS abs_lon,
x_all.*
FROM (
SELECT * FROM (
SELECT DISTINCT ON (gpostal.country_code, gpostal.postal_code, gpostal.place_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code)
'1'::text AS record_code,
-- gplaces.*
gplaces.geoname_id AS geoname_id,
trim(gplaces.full_name) AS full_name,
trim(gplaces.feature_class) AS feature_class,
trim(gplaces.feature_code) AS feature_code,
trim(gplaces.country_code) AS country_check,
trim(gplaces.admin1_code) AS admin1_check,
trim(gplaces.admin2_code) AS admin2_check,
round(gplaces.latitude, 4) AS place_latitude,
round(gplaces.longitude, 4) AS place_longitude,
-- gnames.*,
'm1->'::text AS m1,
gnames.geoname_name_id AS geoname_name_id,
trim(gnames.alternate_name) AS altername_name,
trim(gnames.iso_language) AS language_code,
-- gpostal.*,
'm2->'::text AS m2,
trim(gpostal.country_code) AS country_code,
trim(gpostal.postal_code) AS postal_code,
trim(gpostal.place_name) AS place_name,
trim(gpostal.admin1_code) AS admin1_code,
trim(gpostal.admin2_code) AS admin2_code,
trim(gpostal.admin1_name) AS admin1_name,
trim(gpostal.admin2_name) AS admin2_name,
trim(gpostal.admin3_name) AS admin3_name,
round(gpostal.latitude::numeric(13,8), 4) AS postal_latitude,
round(gpostal.longitude::numeric(13,8), 4) AS postal_longitude,
gpostal.accuracy AS accuracy
----------
FROM xwor.geoname_places AS gplaces
LEFT JOIN xwor.geoname_places_names AS gnames ON (gnames.geoname_id = gplaces.geoname_id)
LEFT JOIN xwor.geoname_places_postal AS gpostal ON (lower(gnames.alternate_name) = lower(gpostal.place_name) AND
gpostal.country_code = gplaces.country_code)
WHERE gpostal.place_name IS NOT NULL
AND gplaces.feature_class = 'P' --AND gplaces.feature_code = 'PPL'
ORDER BY gpostal.country_code, gpostal.postal_code, gpostal.place_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code
) AS x_1
UNION
SELECT * FROM (
SELECT DISTINCT ON (gpostal.country_code, gpostal.postal_code, gpostal.place_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code)
'2'::text AS record_code,
-- gplaces.*
gplaces.geoname_id AS geoname_id,
trim(gplaces.full_name) AS full_name,
trim(gplaces.feature_class) AS feature_class,
trim(gplaces.feature_code) AS feature_code,
trim(gplaces.country_code) AS country_check,
trim(gplaces.admin1_code) AS admin1_check,
trim(gplaces.admin2_code) AS admin2_check,
round(gplaces.latitude, 4) AS place_latitude,
round(gplaces.longitude, 4) AS place_longitude,
-- gnames.*,
'm1->'::text AS m1,
gnames.geoname_name_id AS geoname_name_id,
trim(gnames.alternate_name) AS altername_name,
trim(gnames.iso_language) AS language_code,
-- gpostal.*,
'm2->'::text AS m2,
trim(gpostal.country_code) AS country_code,
trim(gpostal.postal_code) AS postal_code,
trim(gpostal.place_name) AS place_name,
trim(gpostal.admin1_code) AS admin1_code,
trim(gpostal.admin2_code) AS admin2_code,
trim(gpostal.admin1_name) AS admin1_name,
trim(gpostal.admin2_name) AS admin2_name,
trim(gpostal.admin3_name) AS admin3_name,
round(gpostal.latitude::numeric(13,8), 4) AS postal_latitude,
round(gpostal.longitude::numeric(13,8), 4) AS postal_longitude,
gpostal.accuracy AS accuracy
----------
FROM xwor.geoname_places_postal AS gpostal
LEFT JOIN xwor.geoname_places AS gplaces ON (lower(gpostal.place_name) = lower(gplaces.full_name) AND
gpostal.country_code = gplaces.country_code AND
gpostal.admin1_code = gplaces.admin1_code AND
gpostal.admin2_code = gplaces.admin2_code)
LEFT JOIN xwor.geoname_places_names AS gnames ON (lower(gpostal.place_name) = lower(gnames.alternate_name) AND
gnames.geoname_id = gplaces.geoname_id)
WHERE gpostal.place_name IS NOT NULL
AND gplaces.feature_class = 'P' --AND gplaces.feature_code = 'PPL'
ORDER BY gpostal.country_code, gpostal.postal_code, gpostal.place_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code
) AS x_2
UNION
SELECT * FROM (
SELECT
'3'::text AS record_code,
-- gplaces.*
gplaces.geoname_id AS geoname_id,
trim(gplaces.full_name) AS full_name,
trim(gplaces.feature_class) AS feature_class,
trim(gplaces.feature_code) AS feature_code,
trim(gplaces.country_code) AS country_check,
trim(gplaces.admin1_code) AS admin1_check,
trim(gplaces.admin2_code) AS admin2_check,
round(gplaces.latitude, 4) AS place_latitude,
round(gplaces.longitude, 4) AS place_longitude,
-- gnames.*,
'm1->'::text AS m1,
gnames.geoname_name_id AS geoname_name_id,
trim(gnames.alternate_name) AS altername_name,
trim(gnames.iso_language) AS language_code,
-- gpostal.*,
'm2->'::text AS m2,
trim(gplaces.country_code) AS country_code,
trim(gnames.alternate_name) AS postal_code,
trim(gplaces.full_name) AS place_name,
trim(gplaces.admin1_code) AS admin1_code,
trim(gplaces.admin2_code) AS admin2_code,
null::text AS admin1_name,
null::text AS admin2_name,
null::text AS admin3_name,
round(gplaces.latitude, 4) AS postal_latitude,
round(gplaces.longitude, 4) AS postal_longitude,
null::character(1) AS accuracy
----------
FROM xwor.geoname_places_names AS gnames
LEFT JOIN xwor.geoname_places AS gplaces ON (gnames.geoname_id = gplaces.geoname_id)
WHERE gplaces.feature_class = 'P' --AND gplaces.feature_code = 'PPL'
AND gnames.iso_language = 'post'
ORDER BY gplaces.country_code, gnames.alternate_name, gplaces.full_name, gplaces.feature_code, gplaces.admin1_code, gplaces.admin2_code
) AS x_3
) AS x_all
--ORDER BY country_code, postal_code, place_name, geoname_id, record_code
) AS base
WHERE base.record_code = '1' -- Link through the names records.
AND base.abs_lat < 1.0000 -- Make sure postal code is close
AND base.abs_lon < 1.0000 -- to the place's actual location.
--
-- These are the countries that have 'post' records already. We will assume that these are as complete as they can get.
-- This will give us the 'post' alternate names that have not been added to the primary geoname table.
--
-- Please note that the countries 'AD' and 'PF' are not in the feature_class = 'P' category. The countries that had 'post'
-- records where found with a different query that did not use the feature_class or feature_code to constraint the query.
--
-- SELECT DISTINCT ON (gp.country_code)
-- trim(gp.country_code) AS country_code
-- FROM xwor.geoname_places AS gp
-- JOIN xwor.geoname_places_names AS gpn ON (gp.geoname_id = gpn.geoname_id)
-- WHERE gpn.iso_language = 'post'
-- ORDER BY gp.country_code
--
AND base.country_code NOT IN ('AD','AU','BE','BG','CH','DE','ES','FR','GF','GP','IN','MQ','NO','PF','PM','RE','YT')
--
ORDER BY country_code, postal_code, place_name, geoname_id
;
Hope this helps.
Regards,
|
Robert Artigas
|
|
|
|
|
|
|
|
![[Search]](/gforum/templates/default/images/icon_mini_search.gif){kind=icon}
![[Recent Topics]](/gforum/templates/default/images/icon_mini_recentTopics.gif){kind=icon}
![[Members]](/gforum/templates/default/images/icon_mini_members.gif){kind=icon}
![[Groups]](/gforum/templates/default/images/icon_mini_groups.gif){kind=icon}
![[Register]](/gforum/templates/default/images/icon_mini_register.gif){kind=icon}
Register![[Login]](/gforum/templates/default/images/icon_mini_login.gif){kind=icon}
Login
![[Up]](/gforum/templates/default/images/icon_up.gif){kind=icon}