0

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?

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.