Extract address data / Query point in administrative boundary

Question

I try to create a DB in postgreSQL with all the postal adress data for a specific country.

Actually I have this table full of data:

id integer NOT NULL DEFAULT nextval('plz_id_seq'::regclass),
country_code character varying(20),
postal_code character varying(20),
city_name character varying(180),
state_name character varying(180),
state_code character varying(20),
community_name character varying(180),
community_code character varying(20),
latitude double precision,
longitude double precision,
CONSTRAINT plz_pkey PRIMARY KEY (id)

The next step should be, to create a new table with the streets and housenumbers and link it to the right postal code area. For this I imported the specific OSM data into postgis with osm2pgsql and the hstore option.

Where Im now stuck, is the query to find the OSM postal_code area/polygon that fits to my plz data.

I tried this query, but get nothing back:

SELECT * FROM planet_osm_polygon WHERE boundary='postal_code' AND ST_CONTAINS(way, ST_PointFromText('POINT(48 7.85)', 900913));

Where ist my error?

Answer 1

I think the problem is here:

...ST_PointFromText('POINT(48 7.85)', 900913))...

I'm assuming that you meant this place (48°N 7.85°E), so the coordinates are geographical (EPSG:4326), not Web Mercator (EPSG:900913). In the place you gave to function (48 7.85 in EPSG:900913 = 0.0004°N 7.0517°E in EPSG:4326) could not be any of postal_code boundaries, cause it's middle of Gulf of Guinea.

Try to change this piece of query to:

...ST_Transform(ST_PointFromText('POINT(48 7.85)', 4326),900913)...

Of course if your data in planet_osm_polygon table is in EPSG:900913 projection